Commandbutton backcolor change on event

[peter.thompson]

Further to my post yesterday, I need to have a commandbutton change its
color when the content of a cell changes in another worksheet from
blank("") to a value

Had the following suggestion from a board member(thanks!)

Private Sub Worksheet_Change(ByVal Target As Range)
Const sAdd As String = "A1" '<<==== CHANGE

If Not Intersect(Range(sAdd), Target) Is Nothing Then
With Me.CommandButton1
If IsEmpty(Target) Then
..BackColor = &HFFFF&
Else
..BackColor = &HFF&
End If
End With
End If

End Sub


This works if I manually enter a value or "" into say"A1" on the sheet,
but not if the value changes in "A1" as a result of a formula - any
ideas welcome!

Also, what do I need to add to the code to say refer to "A1" in another
worksheet??

Cheers

Peter

 

[Norman Jones]

Hi Peter,

Try:

'===============>>
Private Sub Worksheet_Calculate()
Const sAdd As String = "A1" '<<==== CHANGE

With ThisWorkbook.Sheets("Button").CommandButton1
If Range(sAdd).Value <> "" Then
.BackColor = &HFFFF&
Else
.BackColor = &HFF&
End If
End With
End Sub
'<<===============

This is worksheet event code and should be pasted into the code module of
the sheet which houses the formula cell.

Change"Button" to the name of the sheet which houses the command button.


---
Regards,
Norman


"peter.thompson"

 

[peter.thompson]

Thanks Norman - works fine

Sorry a couple more question

1) If I want to do this for 10 buttons on the same worksheet, what di
In need to do to the code?

2) If the cell is inanother worksheet, again, what needs to change to
code?

Thanks a bunch for hour help

Cheers

Peter

 

[Bob Phillips]

Private Sub Worksheet_Calculate()
Const sAdd As String = "A1" '<<==== CHANGE

With ThisWorkbook.Sheets("Button")
If Worksheets("Sheet22).Range(sAdd).Value <> "" Then
.CommandButton1.BackColor = &HFFFF&
.CommandButton2.BackColor = &HFFFF&
.CommandButton3.BackColor = &HFFFF&
.CommandButton4.BackColor = &HFFFF&
.CommandButton5.BackColor = &HFFFF&
.CommandButton6.BackColor = &HFFFF&
.CommandButton7.BackColor = &HFFFF&
.CommandButton8.BackColor = &HFFFF&
.CommandButton9.BackColor = &HFFFF&
.CommandButton10.BackColor = &HFFFF&
Else
.CommandButton1.BackColor = &HFF&
.CommandButton2.BackColor = &HFF&
.CommandButton3.BackColor = &HFF&
.CommandButton4.BackColor = &HFF&
.CommandButton5.BackColor = &HFF&
.CommandButton6.BackColor = &HFF&
.CommandButton7.BackColor = &HFF&
.CommandButton8.BackColor = &HFF&
.CommandButton9.BackColor = &HFF&
.CommandButton10.BackColor = &HFF&
End If
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"peter.thompson"