can't select worksheet

[donbowyer]

The code below is in a userform.
MyText appears in the correct format.
However, when run, I get the <<error9, out of range error message>>
If I substiute ...Worksheets(MyText)... with ...Worksheets(6)... it runs.
What have I done wrong??

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, _
ByVal X As Single, ByVal Y As Single)
MyText = ListBox1.Text
Application.ActiveWorkbook.Worksheets(MyText).Select
Unload UserForm1
End Sub

 

[Joel]

Worksheets() can either take a string or a number. when its a nuber it is
the count of which worksheet.

Whey ou use Worksheets(6) it is a number indicating the 6th worksheet

Listboxes contain strings that must be converted to numbers.

change from
MyText = ListBox1.Text
to
MyNumber = val(trim(ListBox1.Text))

The change
Application.ActiveWorkbook.Worksheets(MyText).Select
to
Application.ActiveWorkbook.Worksheets(MyNumber).Select

 

[donbowyer]

Hi Joel
Thanks for the reply.
However, from my ListBox, MyText is an alpha string with no numbers, for
example <<UnitedAirlines.>>
So it is the Worksheet entitled <<UnitedAirlines>> that I want to Select,
but as I say, Application.ActiveWorkbook.Worksheets(MyText).Select doesn't
work, even though as you suggest, Worksheets() can take a string as well as a
number.

 

[Dave Peterson]

Are you sure you're populating that listbox with the correct sheetnames?

Is that sheet visible?

Does the sheet exist in the activeworkbook -- you don't change workbooks after
the userform is shown, right?

Do you have the .multiselect property set to fmMultiSelectSingle?

This worked ok for me:

Option Explicit
Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
Dim myText As String

myText = ListBox1.Text
Application.ActiveWorkbook.Worksheets(myText).Select
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
Me.ListBox1.MultiSelect = fmMultiSelectSingle
For iCtr = 1 To Worksheets.Count
If Sheets(iCtr).Visible = True Then
Me.ListBox1.AddItem Sheets(iCtr).Name
End If
Next iCtr
End Sub

 

[Joel]

Your posting showed worksheets(6), what your are really getting is
worksheets("6"). You need to convert the string to a number like
worksheets(val(trim("6")))

 

[donbowyer]

Hi Dave
The ListBox is populated with the WorkBook's SheetNames, except with spaces
between words which the SheetNames don't have.
However, to get MyText, I use:-
MyText = Replace(ListBox1.Text, " ", ""), the product of which is exactly
the same as the relevant SheetName.
The sheets are visible only in the sense that the Tabs are visible, but any
sheet could be open (visible) in the window.
The purpose of the TextBox in the associated UserForm is to select (for
display) the desired WorkBook sheet.
I don't have the .multiselect property set to fmMultiSelectSingle, so I put:-
Me.ListBox1.MultiSelect = fmMultiSelectSingle into the Initialise routine,
but not the rest of your code which adds sheets, as the box is already
populated.
On Run with these changes, the same error message appears.
There is no change of WorkBook at any time.

 

[Dave Peterson]

I bet that the names and strings are not the same.

Maybe adding:
debug.print "|" & myText & "|"
will help you find the difference.

Hi Dave
The ListBox is populated with the WorkBook's SheetNames, except with spaces
between words which the SheetNames don't have.
However, to get MyText, I use:-
MyText = Replace(ListBox1.Text, " ", ""), the product of which is exactly
the same as the relevant SheetName.
The sheets are visible only in the sense that the Tabs are visible, but any
sheet could be open (visible) in the window.
The purpose of the TextBox in the associated UserForm is to select (for
display) the desired WorkBook sheet.
I don't have the .multiselect property set to fmMultiSelectSingle, so I put:-
Me.ListBox1.MultiSelect = fmMultiSelectSingle into the Initialise routine,
but not the rest of your code which adds sheets, as the box is already
populated.
On Run with these changes, the same error message appears.
There is no change of WorkBook at any time.

 

[donbowyer]

Hi Dave
They are the same, but what I didn't realise was that the routine below,
which I am using to select one of the ListBox SheetNames, when finished, does
not seem return to the routine which called the UserForm containing the
ListBox, as I had expected. I have changed things and all is now working.
Many Thanks for your help.
Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, _
ByVal X As Single, ByVal Y As Single)

 

[Dave Peterson]

I'm not sure what that means, but it sounds like you got things working.

Hi Dave
They are the same, but what I didn't realise was that the routine below,
which I am using to select one of the ListBox SheetNames, when finished, does
not seem return to the routine which called the UserForm containing the
ListBox, as I had expected. I have changed things and all is now working.
Many Thanks for your help.
Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, _
ByVal X As Single, ByVal Y As Single)

<<snipped>>