Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using combo box to change color of tabs (xl 2003)
Hi, I want to use a combo box to iniate a procedure to change the color of
the sheet tabs depending on which selection is made: the options a 1. grey tab if no data has been added to a particular worksheet 2. yellow if data has been added, but not validated (never mind the color code is wrong) 3. Green if data has been added to a worksheet and validated. My code: Private Sub ComboBox1_Change() worksheets(ComboBox1.Value).Select Set CBox = ComboBox1.ListIndex If ComboBox1.ListIndex < -1 Then Select Case LCase(CBox) Case 0 ' The corresponding text for this option is "data missing" Sheets("FirstPg").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 15 Case 1 ' The corresponding text for this option is "data updated" Sheets("FirstPg").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 97 Case 2 ' The corresponding text for this option is "data validated" Sheets("FirstPg").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 43 Case Else End Select End If End Sub Why does this fail? Thks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using combo box to change color of tabs (xl 2003)
Which line fails?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using combo box to change color of tabs (xl 2003)
Hi Dan,
I get a "subscript out of range" message. I changed the code, but get the same reply. I linked cell 4,2 to the combo box element, so cell 4,2 displays the associated text. This is the revised code: Private Sub ComboBox1_Change() Dim CBox As Variant worksheets(ComboBox1.Value).Select CBox = Cells(4, 2).Value Select Case CBox Case "Input data" Sheets("Firstpg").Select ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 15 Case "Data available" Sheets("Firstpg").Select ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 97 Case "Data checket" Sheets("Firstpg").Select ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 43 Case Else End Select End Sub Any ideas? "dan dungan" wrote: Which line fails? Private Sub ComboBox1_Change() Dim CBox As Variant worksheets(ComboBox1.Value).Select CBox = Cells(4, 2).Value Select Case CBox Case "Inddata" Sheets("Forside").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 15 Case "Data indlagt" Sheets("Forside").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 97 Case "Data checket" Sheets("Forside").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 43 Case Else End Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using combo box to change color of tabs (xl 2003)
If you get that "subscript out of range" error on a line that uses the name of
the worksheet, then I bet you have a typing error in your code. But my bet is that you're using up the incorrect cell in this line: CBox = Cells(4, 2).Value Since your code is behind the worksheet, this unqualified cells() range refers to the worksheet with the code--not the sheet you just activated. I'd try: Option Explicit Private Sub ComboBox1_Change() Dim CBox As Variant Dim wks as worksheet set wks = nothing on error resume next set wks = worksheets(ComboBox1.Value) on error goto 0 if wks is nothing then msgbox "Please select a worksheet!" exit sub end if 'CBox = wks.Cells(4, 2).Value 'I find this easier to read for a range that doesn't change. 'but either is fine CBox = wks.range("B4").value Select Case lcase(CBox) Case lcase("Input data") ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 15 Case lcase("Data available") ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 97 Case lcase("Data checket") ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 43 End Select End Sub Untested. Uncompiled. Watch for typos. There's no reason to select anything for your code to work. Kragelund wrote: Hi Dan, I get a "subscript out of range" message. I changed the code, but get the same reply. I linked cell 4,2 to the combo box element, so cell 4,2 displays the associated text. This is the revised code: Private Sub ComboBox1_Change() Dim CBox As Variant worksheets(ComboBox1.Value).Select CBox = Cells(4, 2).Value Select Case CBox Case "Input data" Sheets("Firstpg").Select ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 15 Case "Data available" Sheets("Firstpg").Select ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 97 Case "Data checket" Sheets("Firstpg").Select ActiveWorkbook.Sheets("Firstpg").Tab.ColorIndex = 43 Case Else End Select End Sub Any ideas? "dan dungan" wrote: Which line fails? Private Sub ComboBox1_Change() Dim CBox As Variant worksheets(ComboBox1.Value).Select CBox = Cells(4, 2).Value Select Case CBox Case "Inddata" Sheets("Forside").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 15 Case "Data indlagt" Sheets("Forside").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 97 Case "Data checket" Sheets("Forside").Select ActiveWorkbook.Sheets("Forside").Tab.ColorIndex = 43 Case Else End Select End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default Color in MS 2003 Excel Tabs | Excel Discussion (Misc queries) | |||
How do I keep color and font btwn tabs in Excel 2003? | Excel Programming | |||
My excel 2003 wont let me fill cells with color or color the tabs. | New Users to Excel | |||
MS Excel should allow you to change the color of the tabs or the . | Excel Worksheet Functions | |||
Change color of sheet tabs | Excel Programming |