Using combo box to change color of tabs (xl 2003)

[Kragelund]

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

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!

 

[dan dungan]

Which line fails?

 

[Kragelund]

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?

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]

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.