CODEWITHSUNDEEP
excelvbaactivex
Silva
Silva

Reputation: 153

Excel | Disable button at start

I am working on an excel file that will work as a calendar with specifications. I want to have a button at each day. Since I want this to be reusable for other years, I will have buttons on columns with no days (for example, if January starts on a tewsday, Monday will have a button, but nothing on the day, since it is from December).

enter image description here

I know it is possible to set a button enable = False, but I don't know where to put that code. I don't want it to be disabled when another button is clicked but at the opening of the file. I am new to vba, I'm sorry if this is something really simple.

Upvotes: 0

Views: 233

Answers (1)

Josias Maestre
Josias Maestre

Reputation: 127

My approach needs those cells with days from previous month to be empty or "", if theres any value inside it wont work (instead you change the logic to treat cells values like numbers instead of strings).

I noticed that days in your calendar are in string format or so (i.e: "01") that's why I use Len() to evaluate length of string.

This code will set buttons visibility based on TopLeftCell value. Visible = True to days with some value, and Visible = False to empty values.

There is a way to make a button "Enable" but that property is for buttons inside an UserForm.

Tell me if it works for your case, since Sheet.CurrentRegion may cause some issues if your cells are way to much separate from each other, plus it could also hide some other buttons you have. If any of those scenarios do happen let me know, I'll continue helping you anyways!

Sub Set_Buttons_Visibility()

  Dim Sheet As Worksheet
  Dim Calendar_DataBodyRange As Range
  Dim Shape As Shape

    'Set Calendar range
    Set Sheet = ActiveSheet                         'Set Sheet
    Set Calendar_DataBodyRange = Sheet.Cells(1, 1).CurrentRegion      'Set current region
    Calendar_DataBodyRange.Select   '<- comment this after you tested everything[']

    'Hide buttons from previous month
    For Each Shape In Sheet.Shapes
        'If Shape.Visible Then Shape.Select
        
    'Get variables
        'Get Button day, as string
        strTemp = CStr(Shape.TopLeftCell)
        
        'Get range occupied by button
        Set rngTemp = Sheet.Range(Shape.TopLeftCell, Shape.BottomRightCell)
        'rngTemp.Select
        
    'Test conditions
        'Test rngTemp is part of Calendar_DataBodyRange
        bInRange = Not Intersect(Calendar_DataBodyRange, rngTemp) Is Nothing
        
        'Test TopLeftCell has some string
        bString = (Len(strTemp) > 0)
        
        'Test bInRange and bShow (True and True)
        bCondition = (bString = False) And bInRange
        
    'Perform action
        'Set shape visibility
        Shape.Visible = Not (bCondition)
        
        'Delete shape (only if you have another procedure to rebuild all buttons)
        ''''Shape.delete
    Next
    
End Sub

Run code when workbooks opens

To start this function when workbook opens, go to VBA Project Explorer > ThisWorkbook then inside the module you can bind your code to Workbook_Open event. Later on (depending in where you've have stored your code) use the following Run function.

Important: According to your case you might need to store your code 1) inside the sheet you are working on, in other cases you store your code 2) in a single sheet usually called PERSONAL.XLSB that is always open when Excel itself Opens (Know more about this) so your functions can be accesible for all sheets that you work on.

Pros and Cons: On the first case is perfect for sharing your work with your boss or colleagues since your code is locally stored in the sheet (but is harder to update, and hard to back up) and the second case is optimal for your own use since all your functions are in the same workbook so you can call it like "[Workbook.Name]![FunctionName],[FunctionParameters]" (allows you to do better updating and an easier backup just by copy-pasting). In any case you can addapt to your necessities.

Private Sub Workbook_Open()
    'Run sintax needs Workbook [extension] and string [!]

    'Function is stored in current workbook (case 1)
    Run ThisWorkbook.Name & "!Set_Buttons_Visibility"
  
    'Function is stored in PERSONAL (case 2)
    Run "PERSONAL.XLSB!Set_Buttons_Visibility"


End Sub

Upvotes: 1

Related Questions