CommandButton color change on event

I want a CommandButton to change its backcolor when the content of a worksheet cell is empty, and vice versa. Is this possible? Am new to this stuff & would appreciate any help, ideas etc:


Cheers

Peter

Hi Peter,

Try something like:

'===============>>
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 is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman



"peter.thompson"
<[email protected]> wrote in
message news:[email protected]...
>
> I want a CommandButton to change its backcolor when the content of a
> worksheet cell is empty, and vice versa. Is this possible? Am new to
> this stuff & would appreciate any help, ideas etc:
>
>
> 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=494066
>

AN example

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "" Then
Me.CommandButton1.BackColor = &HFFFFFF
Else
Me.CommandButton1.BackColor = &HFF
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

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


"peter.thompson"
<[email protected]> wrote in
message news:[email protected]...
>
> I want a CommandButton to change its backcolor when the content of a
> worksheet cell is empty, and vice versa. Is this possible? Am new to
> this stuff & would appreciate any help, ideas etc:
>
>
> 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=494066
>

Thanks guys - this works if I manual enter data into the cell, however commandbutton color doesn't change if the cell content is changed by formula-I'm still missing something basic (new to VBA!)

Cheers

Peter

Hi Peter,

Try

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

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

This is again worksheet event code and should be pasted into the sheet's
code module.

See also the response to your later variation of this question.

---
Regards,
Norman



"peter.thompson"
<[email protected]> wrote in
message news:[email protected]...
>
> Thanks guys - this works if I manual enter data into the cell, however
> commandbutton color doesn't change if the cell content is changed by
> formula-I'm still missing something basic (new to VBA!)
>
> 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=494066
>