Selecting items in already existing Combo Boxes (Drop down list)

[alas]

Hello Everyone,


I'm pretty new to VBA so please go easy on me.

So here's the basic issue I'm having:

I have a workbook with large amounts of data. I need to extract certain data from this workbook. In this workbook, the data is categorized by drop down menus. So, I'd select an item in drop down menu, and then I can run a simple copy and paste macro. BUT, I need to be able to change the drop down selection in VBA.

Is there a way to do this? I have already identified what each shape is called, I just don't know the code to activate the value I want.

I've been searching and searching but I've only come up with codes that give me errors.

Your help would be greatly appreciated,
thank you!

Eric C.

[mikerickson]

If you are useing Forms controls rather than ActiveX, code like this should work.

[VBA]ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value = 4: Rem selects fourth item
ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value = 0: Rem deselects all items[/VBA]Note that the List for forms ListBoxes and ComboBoxes is 1 based, while the list for ActiveX or Userform controls is 0 based.

[alas]

If you are useing Forms controls rather than ActiveX, code like this should work.

[VBA]ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value = 4: Rem selects fourth item
ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value = 0: Rem deselects all items[/VBA]Note that the List for forms ListBoxes and ComboBoxes is 1 based, while the list for ActiveX or Userform controls is 0 based.

Hi mikerickson,

Thanks for the swift reply! I tried this code and I got a Run-time error '1004':

Application-defined or object-defined error.

Here's what I typed in:

ActiveSheet.Shapes("Group 27").ControlFormat.Value = 4

Just to see if it would select, but I keep getting that error. Did I format it incorrectly?

P.s. What's your full name? My name is Ericson, and it's pretty uncommon for me to run into another erickson, is that your last name? Or is it "Rickson" ?

[mikerickson]

My last name is Erickson. Born in Minnesota. There's lots of us around there (and Ericson, and Ericksen and Ericsen )

Is your control ActiveX or from the Forms menu.

Can you attach a (sensitive data removed) copy of your workbook

[alas]

My last name is Erickson. Born in Minnesota. There's lots of us around there (and Ericson, and Ericksen and Ericsen )

Is your control ActiveX or from the Forms menu.

Can you attach a (sensitive data removed) copy of your workbook

Ah, really? I guess that makes some sense..haha.

Ericson is my first name. I always get, "Is that your last name?" or - , "No, your first name please."

Back on topic- Here's a sample of what I'm trying to do:

[VBA]
Sub CopyNeededData()

ActiveSheet.Range("A1").Select
Windows("Data_Source.xls").Activate
Sheets("Rank").Select
ActiveSheet.Shapes("Selector").ControlFormat.Value = 3

Range("C22:L31").Select
Range("C24").Activate
selection.copy
Windows("Data_COLLECT.xls").Activate
Range("A1").Activate
ActiveSheet.paste
ActiveSheet.Range("A10").Select

Windows("Data_Source.xls").Activate
Sheets("Rank").Select
ActiveSheet.Shapes("Selector").ControlFormat.Value = 4
Range("C22:L31").Select
Range("C24").Activate
selection.copy
Windows("Data_COLLECT.xls").Activate
Range("A10").Activate
ActiveSheet.paste

.
..
...
.... etc Until all of my values are selected and pasted onto this sheet.

End Sub[/VBA]
----------

I hope that makes sense to you Mr. Erikson.

Let me know,
Thanks!

E

[mikerickson]

Nothing in that code looks like a problem. Is Selector a Drop Down from the forms menu? Does it have 4 (or more) items?

Can you attach that sample workbook?

[alas]

Nothing in that code looks like a problem. Is Selector a Drop Down from the forms menu? Does it have 4 (or more) items?

Can you attach that sample workbook?

I can't attach the source data, it is protected and it private material, but I can provide a screen shot of what the source sheet looks like. I'm actually not sure if it's forms or activeX.

Each drop down menu has MANY items (Hundreds).

Did you need anything else? Sorry I can't display anything else on the source book.. Does this look like a forms drop down or an ActiveX drop down menu?

I hope this helps.

[mikerickson]

When you create a new control of the same type, what name does Excel give the control? The presence and placement of spaces is important.

[alas]

When you create a new control of the same type, what name does Excel give the control? The presence and placement of spaces is important.

Hmm, I'm sorry I've never created a control. Is this the Forms thing when I create a macro? It says "UserForm1 and when I double click it, it says, UserForm_Click()

What do you think?

When I run a list properties macro I found online I get these parameters:


Shape Name Shape Type Height Width Left Top
Chart 2 Chart 2 715.5 550.5 72.75 83.25
Group 27 Group 27 21 694.5 0 50.25
Group 135 Group 135 50.25 701.25 0 0

Where Group 135 I think is my "Selector"

Does this make any sense to you? Let me know if anything else is needed.

[mikerickson]

If you can remove the sensitive data and attach a workbook, that would be the best help.

From what you have posted, you might have a userform, you have controls whose origin and names you aren't sure of and you are new to VBA.

If there were a workbook attached, it would be a lot easier.

[alas]

Hi,

Sorry for the late response.

You're talking about the control in the Source Workbook? Or, the workbook I'm creating to extract the data from the source?

[alas]

Excel gives the control "ComboBox1, ComboBox2" etc.. I want to control ComboBox2.

[mikerickson]

An attached workbook would be the best way to see what is going on.

[alas]

Here's an attachment of the workbook I'm working in. If you would respond to my PM I can send you a version of the source workbook. I definitely cannot post this workbook online.

[alas]

So, In the source book, I re-named the boxes "Combo1" and "Combo2" and finally they are being recognized but still can't get it to select what I put. So, you'll see in the .xls sheet some random tests and commented out variables.

[alas]

Okay, here's a better example and file:

combo box is named "cboVehicle"

Code used:
ActiveSheet.Shapes("cboVehicle").ControlFormat.Value = 2

List is:
Audi
BMW
Lexus
Mercedes-Benz

This code should select BMW, correct?

[mikerickson]

That is an ActiveX combobox. They aren't supported on my Mac and I don't know the syntax for dealing with them.

[alas]

So what do you suggest I change to make it work for your mac and (hopefully) for me?