Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
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 " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
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 " wrote in message ... Chip, Thanks for your prompt reply, it works unless I click on my option command button then I get "script out of range" run time error 9. What did I get myselft into? Thanks Rockee --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type mismatch... | Excel Discussion (Misc queries) | |||
type mismatch--how to fix | Excel Discussion (Misc queries) | |||
Type mismatch | Excel Programming | |||
Type mismatch | Excel Programming | |||
Type Mismatch | Excel Programming |