can't select worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

donbowyer - 31 Dec 2007 01:21 GMT

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

Signature

donwb

Reply to this Message

Joel - 31 Dec 2007 10:18 GMT

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

> The code below is in a userform.
> MyText appears in the correct format.
[quoted text clipped - 9 lines]
> Unload UserForm1
> End Sub

Reply to this Message

donbowyer - 31 Dec 2007 12:53 GMT

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.

Signature

donwb

> Worksheets() can either take a string or a number. when its a nuber it is
> the count of which worksheet.
[quoted text clipped - 26 lines]
> > Unload UserForm1
> > End Sub

Reply to this Message

Dave Peterson - 31 Dec 2007 14:45 GMT

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

> Hi Joel
> Thanks for the reply.
[quoted text clipped - 40 lines]
> > > --
> > > donwb

Signature

Dave Peterson

Reply to this Message

donbowyer - 31 Dec 2007 15:28 GMT

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.

Signature

donwb

> Are you sure you're populating that listbox with the correct sheetnames?
>
[quoted text clipped - 70 lines]
> > > > --
> > > > donwb

Reply to this Message

Dave Peterson - 31 Dec 2007 17:25 GMT

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
[quoted text clipped - 94 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

donbowyer - 31 Dec 2007 19:36 GMT

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)

Signature

donwb

> I bet that the names and strings are not the same.
>
[quoted text clipped - 100 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 31 Dec 2007 20:05 GMT

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,
[quoted text clipped - 8 lines]
> --
> donwb

<<snipped>>

Reply to this Message

Joel - 31 Dec 2007 15:03 GMT

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")))

> Hi Joel
> Thanks for the reply.
[quoted text clipped - 35 lines]
> > > Unload UserForm1
> > > End Sub

Reply to this Message