Code cleanup help please
 

I have set up a sheet that has 10 command buttons which change colo
when worksheet cells contain something other than a ""

Here is the cosr I'm using too effect this

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

With ThisWorkbook.Sheets("Costs").CommandButton2
If Range("a101").Value < "" Then
.BackColor = &HFFFF00
Else
.BackColor = &HE0E0E0
End If
End With

etc etc

Is there a more efficient way to do this?

Cheers

Peter (new to VBA

--
peter.thompso
-----------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...fo&userid=2968
View this thread: http://www.excelforum.com/showthread.php?threadid=49542

Code cleanup help please
 

peter.thompson Wrote:
With ThisWorkbook.Sheets("Costs").CommandButton1
If Range("a100").Value < "" Then
.BackColor = &HFFFF00
Else
.BackColor = &HE0E0E0
End If
End With

With ThisWorkbook.Sheets("Costs").CommandButton2
If Range("a101").Value < "" Then
.BackColor = &HFFFF00
Else
.BackColor = &HE0E0E0
End If
End With

Peter, you can benefit from the built-in object classes in Excel. I'
not sure what you're trying to accomplish in the end, but instead o
using absolute references, you can browse through each sheet or eac
cell in a range automatically:


Code
-------------------
This code checks each sheet for cell A100 and changes the CommandButton1 control's background colour.

Dim sh as Worksheet
For each sh in ThisWorkbook.Sheets
If sh.Range("a100").Value < "" Then
sh.CommandButton1.BackColor = &HFFFF00
Else
sh.CommandButton1.BackColor = &HE0E0E0
End If
Next sh

-------------------



Code
-------------------
This code checks the current sheet for cell A100:A109 and changes the CommandButton(n) control's background colour.

Dim rng as Range
Dim os as Integer 'variable to hold the cell offset value

For os = 0 to 9 'loops through offset 0 to 9
With Range("A100").Offset(0, os) '
If .Value < "" Then
CommandButton1.BackColor = &HFFFF00
Else
CommandButton1.BackColor = &HE0E0E0
End If
Next sh

-------------------

--
Excelibu
-----------------------------------------------------------------------
Excelibur's Profile: http://www.excelforum.com/member.php...fo&userid=2978
View this thread: http://www.excelforum.com/showthread.php?threadid=49542

Code cleanup help please
 

Hi Peter,

Basedpartly on information in earlier posts, try:



--
---
Regards,
Norman



"peter.thompson"
<peter.thompson.20fg5m_1135230901.6389@excelforu m-nospam.com wrote in
message news:peter.thompson.20fg5m_1135230901.6389@excelfo rum-nospam.com...

I have set up a sheet that has 10 command buttons which change color
when worksheet cells contain something other than a ""

Here is the cosr I'm using too effect this

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

With ThisWorkbook.Sheets("Costs").CommandButton2
If Range("a101").Value < "" Then
BackColor = &HFFFF00
Else
BackColor = &HE0E0E0
End If
End With

etc etc

Is there a more efficient way to do this?

Cheers

Peter (new to VBA)


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

Code cleanup help please
 

Hi Peter,

Based partly on information from previous posts by you, try something like:


'===============
Private Sub Worksheet_Calculate()
Dim rng As Range
Dim i As Long
Dim rCell As Range
Const sAdd As String = "A100" '<<==== CHANGE
Const NumOfButtons As Long = 10 '<<==== CHANGE
Dim OleObj As OLEObject

Set rng = Me.Range(sAdd).Resize(NumOfButtons)

For i = 1 To NumOfButtons
With Me.OLEObjects("Commandbutton" & i).Object

If rng(i).Value < "" Then
.BackColor = &HFFFF&
Else
.BackColor = &HFF&
End If
End With
Next i

End Sub
'<<===============


---
Regards,
Norman



"peter.thompson"
<peter.thompson.20fg5m_1135230901.6389@excelforu m-nospam.com wrote in
message news:peter.thompson.20fg5m_1135230901.6389@excelfo rum-nospam.com...

I have set up a sheet that has 10 command buttons which change color
when worksheet cells contain something other than a ""

Here is the cosr I'm using too effect this

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

With ThisWorkbook.Sheets("Costs").CommandButton2
If Range("a101").Value < "" Then
BackColor = &HFFFF00
Else
BackColor = &HE0E0E0
End If
End With

etc etc

Is there a more efficient way to do this?

Cheers

Peter (new to VBA)


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

Code cleanup help please
 

Hi Peter,

Dim rCell As Range

Represents an extraneous variable declaration and may be deleted


---
Regards,
Norman

Code cleanup help please
 

Thanks Norman that fixed it up perfectly...am on a steep learning curv
here!

Cheers

Pete

--
peter.thompso
-----------------------------------------------------------------------
peter.thompson's Profile: http://www.excelforum.com/member.php...fo&userid=2968
View this thread: http://www.excelforum.com/showthread.php?threadid=49542