PDA

View Full Version : Selecting items in already existing Combo Boxes (Drop down list)



alas
12-12-2011, 04:27 PM
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
12-12-2011, 06:41 PM
If you are useing Forms controls rather than ActiveX, code like this should work.

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

alas
12-13-2011, 10:28 AM
If you are useing Forms controls rather than ActiveX, code like this should work.

ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value = 4: Rem selects fourth item
ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value = 0: Rem deselects all itemsNote 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
12-13-2011, 02:01 PM
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
12-13-2011, 02:25 PM
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:


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
----------

I hope that makes sense to you Mr. Erikson.

Let me know,
Thanks!

E

mikerickson
12-13-2011, 04:27 PM
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
12-13-2011, 08:05 PM
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
12-13-2011, 11:16 PM
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
12-14-2011, 02:21 PM
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
12-14-2011, 10:57 PM
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
01-09-2012, 10:45 AM
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
01-12-2012, 05:55 PM
Excel gives the control "ComboBox1, ComboBox2" etc.. I want to control ComboBox2.

mikerickson
01-12-2012, 07:00 PM
An attached workbook would be the best way to see what is going on.

alas
01-19-2012, 11:27 AM
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
01-19-2012, 11:30 AM
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
03-07-2012, 04:40 PM
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
03-07-2012, 08:31 PM
That is an ActiveX combobox. They aren't supported on my Mac and I don't know the syntax for dealing with them.

alas
04-10-2012, 09:29 AM
So what do you suggest I change to make it work for your mac and (hopefully) for me?