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