Bug in VBA : Error 13

Adil Kh
Adil Kh used Ask the Experts™
on
Hello everyone,

I am blocked. When i unload the Userform3 "New product" and i select the company in option button it goes to error.

Can you help to solve my problem

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I guess you need to post a sample workbook that show the error so we can help you.
gowflow
Roy CoxGroup Finance Manager

Commented:
What error message are you getting? An example workbook would definitely help.

Where is the Option Button?
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Hi,

Error 13 is a type uncompatibility error.
Alas, without your source code, there is nothing we can do.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Good afternoon, I attached the file so you can download it

Thank you
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Still no attachment. Did you click the blue 'Upload' button?

Author

Commented:
Sorry,

This is the file thank you. Please let me know if it is attached or not
Draft.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I believe the problem is in this code in Userform3. At line 10, 'd' is empty.

Private Sub UserForm_Initialize()
Dim D1 As Object, D2 As Object, D3 As Object, D4 As Object
Stop
Set OI = Worksheets("Items")

TV = OI.Range("X1").CurrentRegion
Set D1 = CreateObject("Scripting.Dictionary")

NL = UBound(TV, 1)
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To NL
    D1(TV(i, 1)) = ""
    
Next i
Me.ComboBox4.List = D1.keys
Me.ComboBox4.SetFocus
End Sub

Open in new window

Most Valuable Expert 2011
Top Expert 2011
Commented:
In Userform3, you set TV using:

TV = OI.Range("X1").CurrentRegion

Open in new window


so the first column is now City names. But in the Optionbutton code in Userform1 you are looking for a Company name in column 1 of TV, which hasn't been reset. As a result, the error arises in BOS here:

Set d = CreateObject("Scripting.Dictionary")
For i = 2 To NL
    If TV(i, 1) = M Then d(TV(i, 2)) = ""
Next i
Me.ComboBox1.List = Application.Transpose(d.keys)

Open in new window


because d is always empty.

This is one of the dangers of public variables. I don't really see why you need to use TV in userform 3 at all. Just use a local variable.

Author

Commented:
Thank you all For your help and solutions.

Author

Commented:
I have another problem in the same file should i continue here or create another Question?

Thank you
Roy CoxGroup Finance Manager

Commented:
Start a separate question please

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial