The userform won`t open

Question

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

Check the Initialize (and Activate) events of your form. The error is most likely there. If you're working with forms/classes, you should set the error handling in the VBE to 'Break in class module'. — Rory
– Rory, Commented Mar 4, 2021 at 15:15
If Quote_Details is a listbox on the userform QDetails should be declared as MSForms.Listbox. — norie
– norie, Commented Mar 4, 2021 at 15:39
This QDetails.RowSource = RngData / Parent.Name & "!" & RngData.Address should result with an error, try QDetails.RowSource = RngData.Parent.Name & "!" & RngData.Address — Shai Rado
– Shai Rado, Commented Mar 4, 2021 at 16:07
This code is in a separate Module. I`ve tried to declare is as MSForms but the Set QDetails goes yellow. — Donald Duck
– Donald Duck, Commented Mar 4, 2021 at 16:13

Shai Rado · Accepted Answer · 2021-03-04 16:17:35Z

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

@user14435479 you're welcome. Please mark as answer if it helped you solve your error

norie · Accepted Answer · 2021-03-04 17:16:10Z

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

Collectives™ on Stack Overflow

The userform won`t open

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related