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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> peter.thompson
> ------------------------------------------------------------------------
> peter.thompson's Profile:
> http://www.excelforum.com/member.php...o&userid=29686
> View this thread: http://www.excelforum.com/showthread...hreadid=494302
>

[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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> peter.thompson
> ------------------------------------------------------------------------
> peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
> View this thread: http://www.excelforum.com/showthread...hreadid=494302
>