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).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
> > >
> > > 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).Select 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).Select
> > > > > to
> > > > > Application.ActiveWorkbook.Worksheets(MyNumber).Select
> > > > >
> > > > >
> > > > > "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).Select
> > > > > > Unload UserForm1
> > > > > > End Sub
> > > > > > --
> > > > > > donwb
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>
|
Similar Threads
