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

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"
wrote in
message news:peter.thompson.204ivb_1134721202.472@excelfor um-nospam.com...

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"
wrote in
message news:peter.thompson.204ivb_1134721202.472@excelfor um-nospam.com...

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


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

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"
<peter.thompson.205ozy_1134775801.3239@excelforu m-nospam.com wrote in
message news:peter.thompson.205ozy_1134775801.3239@excelfo rum-nospam.com...

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