Subscript out of range and no value in array?

[neiljabba]

Hello all

I have been working on a combobox for a toolbar that seems to have an erratic performance. It works quite happily for a while and then throws up an out of range error.

It uses a Public array to populate the choices with sheet names and then use those values to select a sheet based on the choice from the combo box.

Its slightly clumsy so maybe could do with a tidy up but why does it only fail occasionally, usually when I think its working fine. Is there something I cant see?

Anyway here it is any suggestions or improvements would be nice, especially the combobox reset which I cant seem to get working.

Public ws As Worksheet
Public SheetArray(1 To 10) As String

Public i As Integer ' used as array index value



Sub ToAddBars() 'creates browse toolbar and sets properties etc




For Each Toolbar In Toolbars
Select Case Toolbar.Name
Case "test"
Toolbar.Delete
End Select
Next



Set mybar = CommandBars.Add(Name:="test", Position:=msoBarTop, Temporary:=True)


mybar.Visible = True

Set newcombo = mybar.Controls.Add(Type:=msoControlComboBox)
With newcombo
.AddItem "Select worksheet to view"
For Each ws In Worksheets
i = i + 1


.AddItem ws.Name

Let SheetArray(i) = ws.Name


Next ws

.DropDownWidth = 120
.ListIndex = 1
.Caption = "Class Group Filter"
.Width = 140
.OnAction = "processselection"
End With


End Sub


Sub processselection() 'processes options chosen from drop down box for form.

Dim choice As Long

choice = CommandBars("test").Controls(1).ListIndex

Select Case choice

Case 1
choice = 1


Case 2
choice = 2
choice = choice - 1'had to do this to ensure correct array value not sure why bit clumsy.
Sheets(SheetArray(choice)).Select

Case 3
choice = 3
choice = choice - 1
Sheets(SheetArray(choice)).Select

Case 4
choice = 4
choice = choice - 1
Sheets(SheetArray(choice)).Select

Case 5
choice = 5
choice = choice - 1
Sheets(SheetArray(choice)).Select

Case 6
choice = 6
choice = choice - 1
Sheets(SheetArray(choice)).Select

Case 7
choice = 7
choice = choice - 1
Sheets(SheetArray(choice)).Select

Case 8
userchoice = 8
choice = choice - 1
Sheets(SheetArray(choice)).Select

Case 9
userchoice = 9
choice = choice - 1
Sheets(SheetArray(choice)).Select

Case 10
userchoice = 10
choice = choice - 1
Sheets(SheetArray(choice)).Select


End Select
End Sub


Many thanks in advance

Neil

 

[neiljabba]

Let me add.

More specifically it works fine in its test environment but put into its real environment does not. There is nothing added in that envrionment apart from more buttons and another combobox which works fine.

I reference the right control and there are no other instances of the variables used.

Cheers
Neil

 

[PHV]

As the variable i is a Public one, I suggest to reinitialize it to 0 in the ToAddBars procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[neiljabba]

cheers PH

Ill give it a try

Neil