+ Reply to Thread
Results 1 to 7 of 7

How to use vba for dependent dropdown lists

  1. #1
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    How to use vba for dependent dropdown lists

    Hi,

    I want to select the state, then dependent dropdown to town and automatically prefill postcode and MMM from this. See example spreadsheet.

    I also want to format mobile numbers, so that if they are entered as e.g."04666666666" it will turn into "0466 666 666" in the table so excel doesn't drop the "0" e.g. "66666666".

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: How to use vba for dependent dropdown lists

    give the attached a trial.
    small observations - when first forming a table only have two rows - one containing the headers and one blank row for the initial entry of data.
    if you have multiple blank rows your data 'newrow' will be placed below the blank rows.
    As you now appear to be doing more VBA, it will pay to leave all controls (textboxes, comboboxes etc.,) with their default names, you can then loop many processes through them.
    When your apps get larger this will save you many, many rows of code.
    I have left your file references intact so that you can follow on what you have already done.
    Any queries post back on this site.
    torachan.
    Attached Files Attached Files
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  3. #3
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to use vba for dependent dropdown lists

    @torachan
    In the UserForm update, you added some Textboxes just overlapping Combobox1 and 2.
    Then you manage the zorder of these Combobox and TextBox .
    What is the purpose ? Why to add TextBox ?
    Thank you for the information back, it is just to discover and understand something new for me ...!
    PCI
    - Battle without fear gives no glory - Just try

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: How to use vba for dependent dropdown lists

    @PCI,
    Although this is a relatively small form/app, it is a method I adopt on most larger apps, in post #2 I mention leaving all controls at their default names and looping through them, I do not have to bother about the comboboxes, if this was a larger app., say with 20 + textboxes you could save or delete or clear those textboxes with just three lines of code in a loop.Look back through some of my larger submissions to see.

  5. #5
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: How to use vba for dependent dropdown lists

    @awoolfe & @PCI
    Attached is a compacted file as I would normally approach the looping of the UserForm controls.
    The labels are left at default names and the column headers of the table used to name them.
    All the data input is looped through six textboxes, the ones taking data from the table are .enabled=false to prevent user input.
    As the filter comes down to a single row I have eliminated sheet 3 (I would normally use this to load a multi-line listbox with an array if there was more than one outcome).
    At first some actions may appear alien, however I have found the approaches labour saving on code and time.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to use vba for dependent dropdown lists

    Great thanks torachan, I will have a look

  7. #7
    Forum Contributor
    Join Date
    01-12-2021
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    118

    Re: How to use vba for dependent dropdown lists

    Thank you @torachan!! Thanks for posting an example of how you would format it. It's good to see options.

    It works in my spreadsheet! I find that the large number of data having all towns listed in Australia crashed my excel program a few time. I now only list the towns in the state I am in and excel is working well.

+ 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. [SOLVED] Dependent Dropdown Lists With The Same Name
    By dsand61 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-08-2018, 02:15 AM
  2. Create dependent dropdown lists
    By gonurvia in forum Excel General
    Replies: 2
    Last Post: 05-04-2017, 03:56 AM
  3. [SOLVED] Dependent dropdown lists.
    By misi06 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-08-2016, 07:21 AM
  4. [SOLVED] Dependent dropdown lists
    By ibenegal in forum Excel General
    Replies: 6
    Last Post: 01-07-2015, 10:00 AM
  5. [SOLVED] Dependent Dropdown Validation Lists
    By Root River Hardwoods in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2014, 05:30 PM
  6. Excel 2007 : Dependent DropDown lists
    By Harrison in forum Excel General
    Replies: 2
    Last Post: 07-12-2010, 06:32 PM
  7. Dependent dropdown lists
    By Axel in forum Excel General
    Replies: 1
    Last Post: 05-18-2006, 11:35 AM

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