CODEWITHSUNDEEP
excelvbauserform
Donald Duck
Donald Duck

Reputation: 29

The userform won`t open

The Error Says

Run-time error 13 Type Mismatch

Every time I add this to user form. Any ideas why?

I've set it up as a module then called it in to the UserForm_Initialize then it shows an error and won't open the Userform I am trying to add headers & controls to the Listbox Quote Details List

Sub Fill_Quote_Detail()

Dim QDetails As ListBox
Set QDetails = Body_And_Vehicle_Type_Form.Quote_Details

With QDetails

    Dim ws       As Worksheet
    Dim RngData  As Range
    
    Set ws = Sheets("Quote Detail")
    Set RngData = ws.Range("A1:K" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    
    QDetails.ColumnHeads = True
    QDetails.ColumnCount = RngData.Columns.Count
    
    Set RngData = RngData.Resize(RngData.Rows.Count - 1).Offset(1)
    QDetails.RowSource = RngData / Parent.Name & "!" & RngData.Address
    
    QDetails.ColumnWidths = "90;60;100;150;90;80;100;95;60"
End With

End Sub

Upvotes: 0

Views: 483

Answers (2)

norie
norie

Reputation: 9857

The code should probably be in the Initialize event of the userform.

Private Sub UserForm_Initialize()
Dim ws       As Worksheet
Dim RngData  As Range
  
    
    Set ws = Sheets("Quote Detail")
    Set RngData = ws.Range("A1:K" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    Set RngData = RngData.Resize(RngData.Rows.Count - 1).Offset(1)
    
    With Me.Quote_Details

        .ColumnHeads = True
        .ColumnCount = RngData.Columns.Count
 
        .RowSource = RngData.Address(External:=True)
  
        .ColumnWidths = "90;60;100;150;90;80;100;95;60"
  
  End With

End Sub

Now all you need in Fill_Quote_Detail is one line.

Sub Fill_Quote_Detail()
    Body_And_Vehicle_Type_Form.Show
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33662

Set your .RowSource using the following syntax:

.RowSource = RngData.Address(0, 0, xlA1, xlExternal)

After cleaning and sorting your code, try the modified code below:

Modified Code

Sub Fill_Quote_Detail()


Dim QDetails As MSForms.ListBox
Dim ws       As Worksheet
Dim RngData  As Range

Set QDetails = Body_And_Vehicle_Type_Form.Quote_Details

Set ws = ThisWorkbook.Sheets("Quote Detail")
With ws
    Set RngData = .Range("A1:K" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

With QDetails
    .ColumnHeads = True
    .ColumnCount = RngData.Columns.Count
    
    Set RngData = RngData.Resize(RngData.Rows.Count - 1).Offset(1) ' remove Header row
    .RowSource = RngData.Address(0, 0, xlA1, xlExternal) ' set your List-Box Source
    
    .ColumnWidths = "90;60;100;150;90;80;100;95;60"
End With

End Sub

Upvotes: 1

Related Questions