Deactivate button on criteria

  • Thread starter Thread starter Rick Ong
  • Start date Start date
R

Rick Ong

I had created a button to run a macro and insert some
value in 2 cells. How can I deactivate the button whenever
the operation is complete, and only re-activating it
provided that the contents in both the cells are cleared
or deleted?
 
There are two different buttons that you could have used. One is from the Forms
toolbar and one is from the Control toolbox toolbar.

When you click on the button, what name shows up in the name box (to the left of
the formula bar)?

If it looks like "Commandbutton1", it's probably from the control toolbox
toolbar. If it looks like "Button 1", it's probably from the Forms toolbar.
(But you could have gone out of your way to rename them to make this
invalid--but why would you???).

Anyway, rightclick on the worksheet tab (the one that holds the button) and
select view code. The paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCellsToCheck As Range
Set myCellsToCheck = Me.Range("a1,b2")

If Intersect(Target, myCellsToCheck) Is Nothing Then
Exit Sub
End If

'Top one is for the Forms toolbar
' bottom one used the control toolbox toolbar
'With Me.Buttons("button 2")
With Me.CommandButton1
Select Case Application.CountA(myCellsToCheck)
Case Is = 0:
.Enabled = True
.Caption = "click me to run macro"
Case Is = 1
.Enabled = False
.Caption = "Clear other cell" & vbLf & "before running"
Case Is = 2
.Enabled = False
.Caption = "Clear both cells " & myCellsToCheck.Address(0, 0) _
& vbLf & " before clicking"
End Select
End With

End Sub

And I used A1 and B2 as my cells to get checked.
 
It's sometimes difficult to separate the wheat from the chaff. But you'll want
to put it under the worksheet that holds the two cells that need to be checked.
 
Back
Top