Two Combo Boxes (Form Control) depending on each other

Hi all,

I have done this previously with two ActiveX Control Combo Boxes, but somehow the code doesn't seem to work with a Form Control Combo Box.

Below the code that I am using for a single Form Control Combo Box:

Please Login or Register  to view this content.

Above code works perfectly fine, but I don't know how to add a second Form Control Box (with 'month' attributes) to it to make them dependent on each other. I tried it with a code that worked for my ActiveX Control boxes (see below)

Please Login or Register  to view this content.

But that doesn't seem to work!

It just triggers a Macro depending on the month and fiscal year selected. If that's what you mean by output?

e.g. for the combination FiscalYearComboBox.Value = "FY 2016" And MonthComboBox.Value = "MONTH"

Please Login or Register  to view this content.

well, there are 3 fiscal years and 12 months plus the total fiscal year, which means 3 x 13 = 39 possible combinations. painful to write all of them, but I couldn't find an easier way to do it. The Macro basically just shifts the formula by 'one' column. Any suggestions?

I have tried it with only 1 fiscal year so far and yes, I have written all corresponding Macros for that fiscal year already. Though, it returns a "run-time error '424' object required" message every time...

As I mentioned, the code is pretty much the same, the only thing that changes is the column (see below)

Please Login or Register  to view this content.

As you can see, nothing spectacular, no crazy math, simply a shift in column based on the fiscal year and month selected in each of the form control boxes...

How would such a listindex code look like?

Yes! The replace code works just fine. I used it before triggered by one combo box, now I need to figure out how to trigger it with two combo boxes that depend on each other...

Originally Posted by esbencito

Hi all,
Below the code that I am using for a single Form Control Combo Box:

Please Login or Register  to view this content.

Above code works perfectly fine, but I don't know how to add a second Form Control Box (with 'month' attributes) to it to make them dependent on each other. I tried it with a code that worked for my ActiveX Control boxes (see below)

But that doesn't seem to work!

Are you sure?
ComboBox from Form control doesn't have event handler.
Which ComboBox are you referring to?

Thanks for attaching the example spreadsheet! Unfortunately, this is just part of a bigger story. There are more combo boxes with other attributes that also change my array formulas. For some reason I cannot upload the file to this forum, even though it is .xlsb and less than 3MB. It returns an "upload failed" error every time...

Desensitized the data and reduced the file to a few kilobytes! Thanks!

Not sure how not using a replace function would work as I want the combo boxes to display what the client is currently looking at. If you just change the formula every time then how would it match with what the other attributes are showing>? Unless of course they would all depend on each other...

Firstly you are using FORMS controls and not ActiveX so my example does not apply for you

That's what I said at the very beginning

now in the VBA code all you have to do is change what "Timeperiod" refers to instead of doing this replace thing

This is awesome!! I knew this was somehow possible, but I'm still not very familiar with code! Thanks for enlightening me!

i only did 2016 and 2017..a little time is required to do the mapping but you can get the hang of it from this snippet

already added the other years, it works better than I had wished for!

ps i dont know what happened to your comp code...doesn't seem to work now

I have fixed that already. My previous replace code was looking for )),'Location which had disappeared since you added "timePeriod" and "LY" instead. The find/replace code only looks for )), now which solves the problem!

i may have a different approach for those ..but dont really have the time for that at this moment

Please let me know if you do, as I'm always interested in simplifying solutions, I'm sure my way isn't the smartest or most flexible...

Please Login or Register  to view this content.

what can I do if the "TimePeriod" refers to different sheets (same ranges) for different columns in my dashboard? Say, I have a copy of the "Location Plan" sheet which is named "GM$". Can I simply adjust the code and leave out the 'refers to worksheet code' and then just add it back to the formula in each column?

adjusted code:

Please Login or Register  to view this content.

adjusted formula in column:

Please Login or Register  to view this content.

I guess no, as it doesn't seem to work...