Macro Button

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
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

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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

Board Regular
Joined
Mar 23, 2009
Messages
122
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

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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:

Code:
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
 

Forum statistics

Threads
1,085,717
Messages
5,385,440
Members
401,955
Latest member
salimgildeh

Some videos you may like

This Week's Hot Topics

Top