Code cleanup
 

I am using this code 30 times to "reset" 30 commandbuttons on a
worksheet.
There must be a more elegant way? Any assistance much appreciated

Cheers,

Peter (new to VBA)

With ThisWorkbook.Sheets("Benefits").CommandButton1
If Range("a100").Value < "" Then
..BackColor = &HFFC0C0
Else
..BackColor = &HE0E0E0
End If
End With


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=502302

Code cleanup
 

With ThisWorkbook.Sheets("Benefits")

SetButton .CommandButton1, .range("A100")
SetButton .CommandButton2, .range("A101")
'...etc
end with

Sub SetButton(cb as object, rng as range)
if rng.value<"" then
cb.BackColor = &HFFC0C0
else
cb.BackColor = &HE0E0E0
end if
end sub


Tim




--
Tim Williams
Palo Alto, CA


"peter.thompson"
<peter.thompson.21t3fa_1137547201.8604@excelforu m-nospam.com wrote in
message news:peter.thompson.21t3fa_1137547201.8604@excelfo rum-nospam.com...

I am using this code 30 times to "reset" 30 commandbuttons on a
worksheet.
There must be a more elegant way? Any assistance much appreciated

Cheers,

Peter (new to VBA)

With ThisWorkbook.Sheets("Benefits").CommandButton1
If Range("a100").Value < "" Then
BackColor = &HFFC0C0
Else
BackColor = &HE0E0E0
End If
End With


--
peter.thompson
------------------------------------------------------------------------
peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
View this thread: http://www.excelforum.com/showthread...hreadid=502302

Code cleanup
 

Thanks Tim

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