Userform ComboBox - Type Mismatch error in code

[bdsii]

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]

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]

Why don't you post the workbook so we can see how the combobox is populated?

[Bob Phillips]

Sheets(Account01.Value).Select

will work

[bdsii]

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]

You have multiple comboboxes? 01-12?

[bdsii]

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]

It works for me. See attached.

[bdsii]

Yep, that works for me as well....still not sure about the Type Mismatch but marking this one closed. THanks everyone!

[lucas]

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]

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]

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.