Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code cleanup help please
Hi Peter,
Dim rCell As Range Represents an extraneous variable declaration and may be deleted --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data cleanup | Excel Worksheet Functions | |||
Can someone help Cleanup my recorded macro | Excel Discussion (Misc queries) | |||
Log File CleanUp | Excel Programming | |||
Leo Heuser format cleanup code, and a question | Excel Programming | |||
Source Data Code Cleanup Help | Excel Programming |