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!

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

Which line fails?

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

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