Macro Button

[ElvisSteel]

I have a button on a worksheet that performs a task. I want to be able to prevent the user from clicking the button, i.e. disable the button, until they have completed certain cells on the sheet.

Is this possible?

Thanks in advance

Steve

 

[Yard]

2 options:

1) perform a test when the button is pressed, i.e. within the button-click event code

2) perform a test every time the worksheet calculates/is changed and enable the button accordingly.

Which cells must they complete, and do the values themselves need to be tested?

 

[ElvisSteel]

Thanks for the prompt response.
I already coded for your first option, but I thought it would be nicer to actually disable the button until the required data was completed.

Without boring you too much with the details...
The sheet has a drop-down box allowing the user to select a specific record from a data sheet and enter some other data against this, resulting in a number of calculated values being added to the original record.

So what I want is to disable the button until an item is selected.

Please don't spend ages on this, as I can just leave it with an error message as per the current solution. I just wondered whether the disabling of a button is possible in this scenario.

Thanks

Steve

 

[Yard]

It is possible, it just depends on whether the validation of your requirements is based on a calculation or a user changing a cell.

Try something like this in the Worksheet code module:

Private Sub CommandButton1_Click()
MsgBox "OK"
End Sub
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1") = "True" Then
    Me.CommandButton1.Enabled = True
Else
    Me.CommandButton1.Enabled = False
End If
End Sub

 

[ElvisSteel]

Thanks very much for your help.