Cannot Open A Userform

[charllie]

Hi Folks,

Does anyone have any ideas what could be causing this error.

I have a commandbutton on worksheet which opens a userform, both contained within the same workbook.

When i use the commandbutton to open the userform i get Runtime error 9, subscript out of range. It then highlights the code which is

"TeamLeaderScreen.Show"

I am not sure what is causing it or how to solve it. I would really appreciate anyones help with this.

Thanks

 

[Norie]

Are those double quotes actually in the code?

Have you checked the userform name?

Is there any code in the initialize event of the userform?

 

[charllie]

Hi Norie,

How are you?

Are those double quotes actually in the code?

No they are not, i just used them in this post.

Have you checked the userform name?

Yes i did that and i even created a new commandbutton and typed a new code out but still happened.

Is there any code in the initialize event of the userform?

This is the code in initialize event but i cant see anyting irregular in that but i am no expert.

Funny thing, it is also happening to another userform and commadbutton on the same worksheet.

Private Sub UserForm_Initialize()
        
        With Workbooks("Team Leader.xls").Worksheets("TL Screen Layout")
            Me.ShrinkwrapComboBox.AddItem .Range("G9").Text 'YES
            Me.ShrinkwrapComboBox.AddItem .Range("G10").Text 'NO
            Me.PassSampleComboBox.AddItem .Range("G9").Text 'YES
            Me.PassSampleComboBox.AddItem .Range("G10").Text 'NO
            Me.MacAreaComboBox.AddItem .Range("G9").Text 'YES
            Me.MacAreaComboBox.AddItem .Range("G10").Text 'NO
            Me.SplitsComboBox.AddItem .Range("G9").Text 'YES
            Me.SplitsComboBox.AddItem .Range("G10").Text 'NO
            Me.HandOverComboBox.AddItem .Range("G6").Text  'Handed Over
            Me.HandOverComboBox.AddItem .Range("G7").Text  'Closed
            'Configuration ComboxBox DropDown List***********************
            With Workbooks("Team Leader.xls").Worksheets("TL Screen Layout") 'Drop Down list for Me.ConfigurationComboBox
                Set MyRange = .Range("B6:B35")
                Me.ConfigurationComboBox.RowSource = "'" & .Name & "'!" & MyRange.Address
            End With
            'Format ComboxBox DropDown List********************************
            With Workbooks("Team Leader.xls").Worksheets("TL Screen Layout") 'Drop Down list for Me.FormatComboBox
                Set MyRange = .Range("C6:C9")
                Me.FormatComboBox.RowSource = "'" & .Name & "'!" & MyRange.Address
            End With
            'HandPacking Reason ComboxBox DropDown List*******************
            With Workbooks("Team Leader.xls").Worksheets("TL Screen Layout") 'Drop Down list for Me.ReasonComboBox
                Set MyRange = .Range("D6:D25")
                Me.ReasonComboBox.RowSource = "'" & .Name & "'!" & MyRange.Address
            End With
            'Sticker Position Grid Ref DropDown List*************************
                Set MyRange = .Range("E7:E41")
                Me.StickerReferenceComboBox1.RowSource = "'" & .Name & "'!" & MyRange.Address
            
                    Me.StickerReferenceComboBox2.RowSource = "'" & .Name & "'!" & MyRange.Address
                
                Me.StickerReferenceComboBox3.RowSource = "'" & .Name & "'!" & MyRange.Address
            
                    Me.StickerReferenceComboBox4.RowSource = "'" & .Name & "'!" & MyRange.Address
                
                Me.StickerReferenceComboBox5.RowSource = "'" & .Name & "'!" & MyRange.Address
            
                    Me.StickerReferenceComboBox6.RowSource = "'" & .Name & "'!" & MyRange.Address
                
                Me.StickerReferenceComboBox7.RowSource = "'" & .Name & "'!" & MyRange.Address
            
                    Me.StickerReferenceComboBox8.RowSource = "'" & .Name & "'!" & MyRange.Address
            'Sticker Position Location DropDown List*************************
            Me.StickerLocationComboBox1.AddItem .Range("F7")  'Sticker 1
            Me.StickerLocationComboBox1.AddItem .Range("F8")
            Me.StickerLocationComboBox1.AddItem .Range("F9")
            Me.StickerLocationComboBox2.AddItem .Range("F7")  'Sticker 2
            Me.StickerLocationComboBox2.AddItem .Range("F8")
            Me.StickerLocationComboBox2.AddItem .Range("F9")
            Me.StickerLocationComboBox3.AddItem .Range("F7")  'Sticker 3
            Me.StickerLocationComboBox3.AddItem .Range("F8")
            Me.StickerLocationComboBox3.AddItem .Range("F9")
            Me.StickerLocationComboBox4.AddItem .Range("F7")  'Sticker 4
            Me.StickerLocationComboBox4.AddItem .Range("F8")
            Me.StickerLocationComboBox4.AddItem .Range("F9")
            Me.StickerLocationComboBox5.AddItem .Range("F7")  'Sticker 5
            Me.StickerLocationComboBox5.AddItem .Range("F8")
            Me.StickerLocationComboBox5.AddItem .Range("F9")
            Me.StickerLocationComboBox6.AddItem .Range("F7")  'Sticker 6
            Me.StickerLocationComboBox6.AddItem .Range("F8")
            Me.StickerLocationComboBox6.AddItem .Range("F9")
            Me.StickerLocationComboBox7.AddItem .Range("F7")  'Sticker 7
            Me.StickerLocationComboBox7.AddItem .Range("F8")
            Me.StickerLocationComboBox7.AddItem .Range("F9")
            Me.StickerLocationComboBox8.AddItem .Range("F7")  'Sticker 8
            Me.StickerLocationComboBox8.AddItem .Range("F8")
            Me.StickerLocationComboBox8.AddItem .Range("F9")
        End With
End Sub

Thanks

 

[Norie]

Have you checked all the workbook/worksheet names in that code?

Is this occurring on all machines?

Try removing the .xls extension from the workbook name.

Private Sub UserForm_Initialize()
Dim arrYNCombos
Dim I As Long

    arrYNCombos = Array("ShrinkwrapComboBox", "PassSampleComboBox", "MacAreaComboBox", _
                        "SplitsComboBox")
                        
    With Workbooks("Team Leader").Worksheets("TL Screen Layout")
        For I = LBound(arrYNCombos) To UBound(arrYNCombos)
            Me.Controls(arrYNCombos).AddItem .Range("G9").Text 'YES
            Me.Controls(arrYNCombos).AddItem .Range("G10").Text 'NO
        Next I
        
        Me.HandOverComboBox.AddItem .Range("G6").Text  'Handed Over
        Me.HandOverComboBox.AddItem .Range("G7").Text  'Closed
        
        'Configuration ComboxBox DropDown List***********************
        Me.ConfigurationComboBox.RowSource = "'" & .Name & "'!B6:B35"
        
        'Format ComboxBox DropDown List********************************
        Me.FormatComboBox.RowSource = "'" & .Name & "'!C6:C9"
        
        'HandPacking Reason ComboxBox DropDown List*******************
        Me.ReasonComboBox.RowSource = "'" & .Name & "'!D6:D25"
                
        For I = 1 To 8
            'Sticker Position Grid Ref DropDown List*************************
            Me.Controls("StickerReferenceComboBox1" & I).RowSource = "'" & .Name & "'!E7:E41"
            'Sticker Position Location DropDown List*************************
            Me.Controls("StickerLocationComboBox1" & I).AddItem .Range("F7")  'Sticker i
            Me.Controls("StickerLocationComboBox1" & I).AddItem .Range("F8")  'Sticker i
            Me.Controls("StickerLocationComboBox1" & I).AddItem .Range("F9")  'Sticker i
        Next I
    End With
End Sub

 

[charllie]

Hi Norie,

I think i have found it. It was this part of the above code.

'Sticker Position Grid Ref DropDown List***
                Set MyRange = .Range("E7:E41")

I changed it to:

Set MyRange = Worksheets("TL Screen Layout").Range("E7:E41")

It appears that i did not assign the range.

It all seems ok now, going to run a few more tests to see.

Thanks Norie for your help. Sorry to waste your time for my silly mistake. I appreciate your time.