+ Reply to Thread
Results 1 to 17 of 17

Two Combo Boxes (Form Control) depending on each other

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    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!

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Two Combo Boxes (Form Control) depending on each other

    what is your actually output?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending on each other

    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.

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Two Combo Boxes (Form Control) depending on each other

    so the output is it triggers a subroutine?
    you must have many subroutines for every single option then.... that seems like a rather hard way to accomplish the task?

    what part doesnt seem to be working?
    do you have all these subroutines written already?

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending on each other

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

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Two Combo Boxes (Form Control) depending on each other

    do it programatically via code

    Please Login or Register  to view this content.
    find out what is the different in the code from each month and see if there is a systematic way to replicate it
    as you only presented one subroutine...hard to find the pattern here...a sample file with a few more examples is warranted if you need help to find this pattern

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending on each other

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

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Two Combo Boxes (Form Control) depending on each other

    i think you can take advance of the property listindex from the combobox

    does your replace code actually work?

  9. #9
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending on each other

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

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Two Combo Boxes (Form Control) depending on each other

    Quote Originally Posted by esbencito View Post
    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?

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Two Combo Boxes (Form Control) depending on each other

    Put a little demo together with what your spreadsheet may look like

    i used a command button to trigger instead of combobox Change but this should give you the idea
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending on each other

    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...
    Attached Images Attached Images

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Two Combo Boxes (Form Control) depending on each other

    my sample was only meant to serve as example

    maximum file size is 1mb
    i note your formula goes to 1605 so just chop it down the data to around odd lines
    enough to get the gist of things

    Remember to desensitize the data.

    looking at the picture your probably better off changing the formula rather than doing a "replace"

  14. #14
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending on each other

    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...
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Two Combo Boxes (Form Control) depending on each other

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

    anyways digging deeper i think you might benefit from Named Ranges to define what period you are looking at

    what i mean by this is
    assume that Region,type and comp is set to all
    your formula in b37 would be something like
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    instead i am suggesting you replace
    Formula: copy to clipboard
    Please Login or Register  to view this content.


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

    what this looks like in code

    Please Login or Register  to view this content.
    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

    i also created a named range LY (for your last year comparison)

    ps i dont know what happened to your comp code...doesn't seem to work now
    i may have a different approach for those ..but dont really have the time for that at this moment

    will look at it again next week
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending 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...

  17. #17
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Two Combo Boxes (Form Control) depending on each other

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code works with Combo Box-Form Control, but not with Combo box - ActiveX Control
    By nangys in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-01-2014, 08:51 AM
  2. [SOLVED] Setting All Form Control Combo Boxes To A Specified Value
    By andrewryan920 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2014, 03:22 PM
  3. populating list boxes and combo boxes in a user form.
    By ahceinaej in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2014, 11:54 AM
  4. Main Form Multiple Combo Boxes Control Subform textboxes
    By jvbeats in forum Access Tables & Databases
    Replies: 0
    Last Post: 02-01-2013, 01:36 PM
  5. combo box user form code for two combo boxes
    By robert.begley1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-01-2012, 02:25 PM
  6. Loading combo boxes depending on others
    By sentinela in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-28-2009, 09:12 AM
  7. combo boxes and calendar control easy ??'s
    By Jlong1980 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2006, 08:47 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1