can't select worksheet
 

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).Sele ct
Unload UserForm1
End Sub
--
donwb

can't select worksheet
 

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).Sele ct
to
Application.ActiveWorkbook.Worksheets(MyNumber).Se lect


"donbowyer" wrote:

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).Sele ct
Unload UserForm1
End Sub
--
donwb

can't select worksheet
 

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).Sele ct doesn't
work, even though as you suggest, Worksheets() can take a string as well as a
number.

--
donwb


"Joel" wrote:

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).Sele ct
to
Application.ActiveWorkbook.Worksheets(MyNumber).Se lect


"donbowyer" wrote:

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).Sele ct
Unload UserForm1
End Sub
--
donwb

can't select worksheet
 

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).Sele ct
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

donbowyer wrote:

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).Sele ct doesn't
work, even though as you suggest, Worksheets() can take a string as well as a
number.

--
donwb

"Joel" wrote:

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).Sele ct
to
Application.ActiveWorkbook.Worksheets(MyNumber).Se lect


"donbowyer" wrote:

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).Sele ct
Unload UserForm1
End Sub
--
donwb

--

Dave Peterson

can't select worksheet
 

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" wrote:

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).Sele ct doesn't
work, even though as you suggest, Worksheets() can take a string as well as a
number.

--
donwb


"Joel" wrote:

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).Sele ct
to
Application.ActiveWorkbook.Worksheets(MyNumber).Se lect


"donbowyer" wrote:

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).Sele ct
Unload UserForm1
End Sub
--
donwb

can't select worksheet
 

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.

--
donwb


"Dave Peterson" wrote:

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).Sele ct
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

donbowyer wrote:

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).Sele ct doesn't
work, even though as you suggest, Worksheets() can take a string as well as a
number.

--
donwb

"Joel" wrote:

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).Sele ct
to
Application.ActiveWorkbook.Worksheets(MyNumber).Se lect


"donbowyer" wrote:

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).Sele ct
Unload UserForm1
End Sub
--
donwb

--

Dave Peterson

can't select worksheet
 

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

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

donbowyer wrote:

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.

--
donwb

"Dave Peterson" wrote:

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).Sele ct
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

donbowyer wrote:

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).Sele ct doesn't
work, even though as you suggest, Worksheets() can take a string as well as a
number.

--
donwb

"Joel" wrote:

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).Sele ct
to
Application.ActiveWorkbook.Worksheets(MyNumber).Se lect


"donbowyer" wrote:

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).Sele ct
Unload UserForm1
End Sub
--
donwb

--

Dave Peterson


--

Dave Peterson

can't select worksheet
 

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)

--
donwb


"Dave Peterson" wrote:

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

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

donbowyer wrote:

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.

--
donwb

"Dave Peterson" wrote:

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).Sele ct
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

donbowyer wrote:

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).Sele ct doesn't
work, even though as you suggest, Worksheets() can take a string as well as a
number.

--
donwb

"Joel" wrote:

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).Sele ct
to
Application.ActiveWorkbook.Worksheets(MyNumber).Se lect


"donbowyer" wrote:

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).Sele ct
Unload UserForm1
End Sub
--
donwb

--

Dave Peterson


--

Dave Peterson

can't select worksheet
 

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

donbowyer wrote:

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)

--
donwb
<<snipped