+ Reply to Thread
Results 1 to 3 of 3

Excel 2010 VBA – Combo Box Options– Clear cell/box in 2nd Combo when 1st Combo selected

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    51

    Question Excel 2010 VBA – Combo Box Options– Clear cell/box in 2nd Combo when 1st Combo selected

    Hello and thank you for taking the time to check this out

    I have created a form in Excel 2010 which can be completed electronically, or the old fashioned way, by hand. There is no need to retain the information anywhere else after it has been completed. The user can either save it, or print it.

    I have 2 dropdown dependant comboboxes based under ‘Data Validation’.
    First combo box has Business Unit (3 Options: Office, Maintenance and Operations).
    Second combo box “Profile” provides a list of profiles based on which Business unit has been selected.

    Although this works, it’s a bit disjointed. What I mean is that when I click on the Business Unit, eg. Operations, it populates the box with Operations …. This works 
    Then I have to click the Profile combo box and make a selection based on the list for Operations. This also works 

    Currently, if the user wants to change Business Unit … say to “Office”, then the user will have to remember to go to the “Profile” combo box to make a new selection based on the list for “Office” (can’t rely on user’s memory LOL).

    What I would ideally like is, when the Business Unit has been selected, it clears/blanks the cell value of the “Profile” cell and provides the options for the selected Business Unit automatically. This way the user can’t forget to make a selection from the “Profile” list.

    I need the Business unit selection to be visible.
    Please refer to the attached sample.

    I’d appreciate any assistance, as I have been trawling through the web trying to find some code that I could modify to meet my needs, but I haven’t found any. My knowledge of VBA is equivalent to a toddler … small steps.

    I have tried to create the form by the VBA Userform but then I had problems with the option dependent combo boxes and then come across the same problem of how to clear any previous value in that cell/combo box.

    Thank you very much for your time.

    Regards,
    TheShyButterfly

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,032

    Re: Excel 2010 VBA – Combo Box Options– Clear cell/box in 2nd Combo when 1st Combo selecte

    Hi

    you need a simple macro.

    Change your definitions for Operations, Office and Maintenance to include row 2.
    You will need to go to formulas and select name manager.

    right click on your sheet name at the bottom of excel and select view code.

    Paste this code in the new window and close it.

    Thats it.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    51

    Re: Excel 2010 VBA – Combo Box Options– Clear cell/box in 2nd Combo when 1st Combo selecte

    Hi Mehmetcik,

    You are a champ Thank you so much ... it worked

    Thank you so much for your quick response.

+ 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. Replies: 1
    Last Post: 04-07-2013, 03:40 AM
  2. Add combo list with user defined data in selected cell in Excel 2003
    By sakthivel.s in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2009, 05:45 AM
  3. Replies: 2
    Last Post: 03-29-2006, 10:30 AM
  4. Cell options/combo box?
    By Paulymon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2006, 02:50 PM
  5. [SOLVED] Excel VBA Combo Box Populating dependent on other combo box choices
    By ikabodred in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2006, 11:20 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