PDA

View Full Version : [SOLVED] Userform ComboBox - Type Mismatch error in code



bdsii
02-04-2010, 09:45 AM
I have a Userform with a ComboBox with a name of Account01. The ComboBox is populated from the names of the worksheets. The code below should take the user to the selected worksheet but instead I get a Type Mismatch error on the line below. I cannot find anywhere if it is required to specify a type for the ComboBox. I have figured a clunky workaround but I want to learn correctly and not create a patchwork of code.

In the code for processing, the snippet below should take the user to the worksheet. The worksheet does exist and is spelled the same as in the ComboBox and I know they are the same because the ComboBox is populated by a loop grabbing the worksheet names.


Sheets(Account01).Select


I also tried the code below instead without any luck.


ActiveWorkbook.Sheets(Account01).Select


I have also tried using a Dim statement to assign Account01 as a String but still no luck. I have looked all around but cannot find the answer on the web.

Ideas ?


thanks!

Dr.K
02-04-2010, 09:52 AM
The datatype for combobox entries is Variant, just like Worksheet Cells.

Maybe you shouldn't use the default property for the combobox? As in, be specific about what data point you are pulling out.

Also, I could be totally wrong, but shouldn't you be .Activate 'ing the sheets instead of .Select 'ing them?

lucas
02-04-2010, 10:12 AM
Why don't you post the workbook so we can see how the combobox is populated?

Bob Phillips
02-04-2010, 10:12 AM
Sheets(Account01.Value).Select


will work

bdsii
02-04-2010, 11:52 AM
xld - Thanks! You are always great help!

Dr K and lucas - the code pasted below is how I populated the ComboBox. I noticed that the variable wk is a Worksheet but I thought it still should have worked in the code since it was referring to a worksheet. If on the other hand the combobox converted it or something to a Text type, that should have worked too. I have it working but I am not certain of the original cause of the problem.

thoughts ?




Dim wk As Worksheet
For Each wk In Worksheets
' If Statement eliminates worksheets not wanted in Listbox
If Left$(wk.Name, 5) <> "Sheet" Then
Account01.AddItem wk.Name
Account02.AddItem wk.Name
Account03.AddItem wk.Name
Account04.AddItem wk.Name
Account05.AddItem wk.Name
Account06.AddItem wk.Name
Account07.AddItem wk.Name
Account08.AddItem wk.Name
Account09.AddItem wk.Name
Account10.AddItem wk.Name
Account11.AddItem wk.Name
Account12.AddItem wk.Name
End If
Next wk

lucas
02-04-2010, 11:57 AM
You have multiple comboboxes? 01-12?

bdsii
02-04-2010, 12:28 PM
Yes....one userform is capturing and processing 12 different breakouts for one transaction. Imagine a single deposit splitting into 12 different accounts as an example.

lucas
02-04-2010, 12:33 PM
It works for me. See attached.

bdsii
02-04-2010, 12:48 PM
Yep, that works for me as well....still not sure about the Type Mismatch but marking this one closed. THanks everyone!

lucas
02-04-2010, 12:55 PM
One last question. Why didn't you provide the example workbook instead of waiting for me to construct it from your posts?

Was it just too hard to do?

bdsii
02-04-2010, 01:13 PM
lucas - no, wasn't too hard to do but didn't think it would really help all that much. I thought the code was easier to review than having to download an attachment. I could not use the original spreadsheet since it is sensitive data and had a lot more info and code that did not pertain to the problem. Since i am not very good with code, I was not sure I could break out just the code that applied and it still work. I am still learning :-)

lucas
02-04-2010, 01:22 PM
But it did obviously help, don't you agree.

I can accept insecurity as a reason but want to encourage you to try to see this from the other side of the screen. It's hard to see what you are up against. We can't see over your shoulder.