Type Mismatch

[Rockee052]

Hi

I am trying to figure out why I am getting a type mismatch run time
error 13 when running my code... I have searched the help file in vba
and I am still stumped. I have also done a google search and well, here
I am...

What I am trying to do is view each worksheet when clicked on in the
listbox. It was working then I added some more code for an options
command button and now there is some sort of conflict.
The listbox is on a userform.

I also have one additional question, When my option command button has
been clicked 4 times and up it changes the list box property to
multiselectmulti property when it should be multiselectsingle. Where
did I go wrong?

Thanks for any help or advice

Here is my code:

Option Explicit
Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub cmdOption_Click()
If cmdOption.Caption = "Options >>" Then
Me.Height = 160.5
ListBox1.Height = 120.8
ListBox1.ListStyle = fmListStylePlain
cmdOption.Caption = "<< Options"
Else
Me.Height = 197.25
ListBox1.Height = 120.8
cmdOption.Caption = "Options >>"
ListBox1.ListStyle = fmListStyleOption
ListBox1.MultiSelect = fmMultiSelectMulti
End If
End Sub

Private Sub cmdPrint_Click()
Dim i As Integer
Application.ScreenUpdating = False
If cmdOption.Caption = "Options >>" Then
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
With Sheets(ListBox1.List(i))
PageSetup.BlackAndWhite = True
PrintOut
End With
End If
Next i
End If
Application.ScreenUpdating = True
Unload Me
End Sub

Private Sub ListBox1_Click()
Worksheets(ListBox1).Text.Activate ' type mismatch ?
Range("A1").Select
End Sub

Private Sub UserForm_Initialize()
Dim wks As Worksheet
For Each wks In Worksheets
Select Case wks.Name
Case "Parts List", "Sheet List", "All Parts", _
"All Part Numbers", "Enter Data", "Summary", _
"Logo", "HelpSheet"
Case Else: Me.ListBox1.AddItem wks.Name
End Select
Next
Me.Height = 160.5
End Sub


Rockee Freeman

 

[Chip Pearson]

Rockee,

Change
Worksheets(ListBox1).Text.Activate
To
Worksheets(ListBox1.Text).Activate



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rockee052]

Chip,

Thanks for your prompt reply, it works unless I click on my optio
command button then I get "script out of range" run time error 9. Wha
did I get myselft into?

Thanks

Rocke

 

[Chip Pearson]

Rockee,

Most likely, you don't have a worksheet with same name as appears
in the list box. Are you sure that the list box names an
existing sheet?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rockee052]

Chip,

All the sheets are in the listbox are existing sheets. It does wor
correctly until I hit the options command button, thats when things ge
a little crazy. Once the option command button has been clicked, I ge
the run time error "Script out of range" in the line of code
Worksheets(ListBox1.Text).Activate. But, it does work correctly until
click on the options button...

Again thanks for help

Rocke