VBA code to click/select drop down lists

[hockeyadc]

I have a userform that I have created, and as the user goes through the drop downs, it filters the next drop down list in order. What i would like to do, is have some VBA code that simulates a user clicking on a drop down box and making a selection. Is there a way to do this? I want to automate some steps basically and I need to simulate this.

Thanks!

[Solus Rankin]

What do you mean by simulate? Like make a selection in the combobox?

You can use code to change the value of a combobox but i don't believe it will trigger a click event, what event are you trying to 'simulate'?

[hockeyadc]

I'm trying to simulate the clicking of the combobox, and selecting a certain value. When the user does this and makes a selection, it filters the combobox next to it. so I am trying to make an automated version of a user going through and clicking comboboxes and their values.

[hockeyadc]

So basically, Combobox_Change() will initiate the script to filter the next combobox

[Norie]

Why do you need the combobox?

Can't you just loop through the data it's populated with?

[hockeyadc]

Well I was basically trying to skip re-writing all the code that goes into filtering the comboboxes. I think I have figured it out... if I make the selection what I want, and then call that combobox_change() sub, it seems to be doing what I want. I guess just needed to type out my question lol.

[Solus Rankin]

So you're writing code to test your code?

Seems kinda circular.

[hockeyadc]

So you're writing code to test your code?

Seems kinda circular.

It is. What my program does right now, is gives the user the ability to go through drop down lists to formulate a part number, and then it runs a macro which spits out the build of material. The drop down lists are all running their own macros, in order to only provide correct part numbers.

What I am now programming, is the ability to just enter in an already created part number, press a button, and get the build of material. I am decoding each part number, piece by piece, and then basically running it through my initial program, as if they entered it themselves. The reason for this, is that all of the checks and balances are already built in to make sure a correct part number is created. If I do it this way, I can make sure that each piece they put in, matches up with whats in the combobox.

Here is my issue now though. I can set the combobox to a value with "combobox1 = value" except this doesn't select a value that has been pre-filtered in the list. Is there a way that instead of just setting the combobox to a value, I can select a value? That way if the value isn't there, I can throw up a msgbox, stating it is an invalid part number..

[hockeyadc]

I'll run this by you...

The other way I can do it, would be to loop my program, to make every single valid part number there is instead of "decoding" them each time one by one. The issue with this, is there would be millions of part numbers, way more than excel can handle so I would have to probably export them to a DB file and read/write from there.

Would this be easier?

[hockeyadc]

Edit: Double post

[Solus Rankin]

I'm assuming there are fairly strict rules for what constitutes a valid part number and what doesn't.

Can you check for those at the beginning of the button click?

If they're not valid then msgbox please enter valid part number.

[hockeyadc]

I'm assuming there are fairly strict rules for what constitutes a valid part number and what doesn't.

Can you check for those at the beginning of the button click?

If they're not valid then msgbox please enter valid part number.

Well I have to check each individual piece of the part number. Its more like, the part number is broken down into 8 sections or so. Each section can have its own set of values, which is determined by the former. This is why I've built this program, to make it easier for people to put them together.

I think I've figured everything out, except the selecting of a value only of its in the drop down. Any ideas how I can do that? For instance, if the part number I'm checking has an "X" in position 10, and the drop down usually responsible for the selection of this by the user only shows "Y", "W", "Z" as options, I need it to throw the message box up that says "Invalid part number" and require them to change it before the program proceeds.

Edit: It needs to go like this:

If the value I am checking for, is in the range of selectable values in the combobox, assign combobox to that value. Otherwise, throw up the MSG box.

[Solus Rankin]

You'll have to loop through the list of the combobox checking all items in the lest against the "section" of part number that it corresponds to.

[hockeyadc]

You'll have to loop through the list of the combobox checking all items in the lest against the "section" of part number that it corresponds to.

Is there a way I can do some kind of "contains" code? For instance, if Combobox.list contains value then....

How would I look through them? As a combobox index item?

[Solus Rankin]

Please Login or Register  to view this content.

[hockeyadc]

Please Login or Register  to view this content.

Nice! Thank you. Starting to think I should have just made this in Access or another language to begin with...

[Solus Rankin]

Well VBA works in access as well. I'm not sure I understand the entire scope of what you're trying to accomplish so I can't comment on that.

If you're looking at storing data access is a good bet. If you'd like to modify, compile etc, then I'd stick with excel.

[hockeyadc]

Wait a sec, VBA works in access too? I thought it was only MYSQL!

[Solus Rankin]

VBA works for most Microsoft applications. Word,outlook,IE,Powerpoint. Its Visual Basic changed to apply to Applications = VBA (as apposed to VB.Net)

[hockeyadc]

Thanks for the tip!