Enable/Disable Form Control Button

[lesley burlingham]

Hi

I have 1 form control buttons on my spreadsheet assigned to a macro. I need this box to be disabled until 1 is input into cell B1.

Thanks

 

[Ramesh]

Here you go..

Please find the attached updated file where on the Worksheet_Change Event and Workbook_Open event I have written the code for enabling or disabling the button.

The button is assigned to execute the macro written in Module1.. when the button is enabled, only then the procedure gets executed and the msgbox is shown.

 

[lesley burlingham]

Thanks for the reply. My spreadsheet is not recognising ws. How would I write the code if I had multiple sheets.

 

[Ramesh]

If you would like to perform this check on all the worksheets for Cell B1, then you need to modify the code as below in Workbook_open event

[INDENT] Dim ws As Worksheet
 Dim MainWorksheet As Worksheet
 'This is the worksheet where we have the button placed
 Set MainWorksheet = ThisWorkbook.Worksheets("Sheet1")
 'Looping thru each worksheet and checking whether cell B1 is empty
 For Each ws In ThisWorkbook.Worksheets
        If Trim(Len(ws.Range("B1").Value)) <> 0 Then
            MainWorksheet.Buttons("Button 1").Enabled = True
            MainWorksheet.Buttons("Button 1").Font.ColorIndex = 1
        Else
            MainWorksheet.Buttons("Button 1").Enabled = False
            MainWorksheet.Buttons("Button 1").Font.ColorIndex = 2
        End If
 Next[/INDENT]

And in the change event for all the worksheets we have to copy/paste the same code available in Sheet1's worksheet_change event..