+ Reply to Thread
Results 1 to 17 of 17

Drop downlist Help - set default..

  1. #1
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Drop downlist Help - set default..

    Hi All,

    Not sure if this is in the correct area on here.

    Is there a way to set a default in a drop down list.

    Basically, in the current work book the main page has buttons to take you to different sheets within the workbook. What I need is, when button one is clicked it takes you to a 'booking' worksheet. On the 'booking' worksheet the customer has to select their customer number from a drop down list. What I need to do it set a default on the drop down list when the customer leaves the booking worksheet by clicking a button to go back to the main page..

    Thanks

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Drop downlist Help - set default..

    You could use the Worksheet Change event.
    First name the drop down cell say "mydropdown"

    and then use the following macro

    Please Login or Register  to view this content.
    Where Sheet2 is the VBA code name (NOT the tab name) for the main sheet, and the word 'Main' is a word in the drop down list.

    But why not just have a dedicated button on the sheet that has a simple procedure like the following assigned to it.

    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Drop downlist Help - set default..

    Administrative note

    Still using XL2003 as indicated in your profile?
    If not,perhaps update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to? Members tailor answers based on your Excel version.
    Thanks

  4. #4
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    Hi

    For some reason its not resetting the dropdown list to Select
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    Good Point office version updated .. Thank you

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Drop downlist Help - set default..

    WHat's the VBA code name of the sheet you want to Activate?

    If it's not Sheet2, that was what I used as an example on my test workbook and it's probably different to yours, then you need to change the Sheet2.Activate to whatever is your VBA sheet code name

  7. #7
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    Hi Richard.

    The vba code is sheet2, I have amended sheet2.activate tot he following and still it doesn't work
    Please Login or Register  to view this content.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Drop downlist Help - set default..

    Quote Originally Posted by janger View Post
    Hi Richard.

    The vba code is sheet2, I have amended sheet2.activate tot he following and still it doesn't work
    Please Login or Register  to view this content.

    The code I suggested was
    Please Login or Register  to view this content.
    are you saying you've now got

    Please Login or Register  to view this content.
    Please upload the workbook so I can take a look. Difficult to know otherwise what you may have done.
    Last edited by Richard Buttrey; 02-26-2020 at 04:40 PM.

  9. #9
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    Hi

    I have attached the work book ..

    Much appreciated
    Attached Files Attached Files

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Drop downlist Help - set default..

    As I said in #6 you need to change the example Sheet2 I used to whatever is your main sheet VBA Code Name.
    The Main sheet is Sheet1 not Sheet2 hence use

    Please Login or Register  to view this content.
    Note it's good practice to use VBA sheet Code names rather than the sheet tab names since they are too easily changed by a user and thne a macro using a tab name will fail.

  11. #11
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    Hi

    It does work when you choice 'select' in the dropdown list. However, I need the dropdown list to default to the 'select' option when the user selects another worksheet ..

    thanks

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Drop downlist Help - set default..

    Sorry I don't understand.
    Please explain exactly what sheets and cells you are selecting and what you want to happen.
    At the moment when you select "select' in the Booking drop down it remains as the active selection when you return

    Maybe you just want

    Please Login or Register  to view this content.
    in the Worksheet Change event

  13. #13
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    Hi

    Sorry I don't explain things very well,

    I do want the drop down to default to 'select" but only when the user clicks the refresh button to home page button.

    Thanks

  14. #14
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    All sorted

    I have added the "Range("D6") = "Select" to the button macros and its works ..

    Thanks very much for your help Rich

  15. #15
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Drop downlist Help - set default..

    Just add the

    Please Login or Register  to view this content.
    to Macro5

    Incidentally all the .Select stuff is rarely needed. It simply adds processing time since the macro needs to jump back to Excel and reurn to VBA for the next instruction
    Just address objects, cells, ranges,...etc directly. Whenever you capture code with the macro recorder it alway needs editing to make it more efficient

    So where for instance you have stuff like

    Please Login or Register  to view this content.
    Replace that with
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    08-20-2010
    Location
    uk
    MS-Off Ver
    Office Pro Plus 2016
    Posts
    85

    Re: Drop downlist Help - set default..

    Nice one, thanks for the tip ..

  17. #17
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Drop downlist Help - set default..

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

+ 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. Summing Values from a drop downlist
    By AJHANSEN77777 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 09-19-2018, 04:23 AM
  2. Initial default drop downs and dependent default drop downs
    By hudsone777 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-07-2016, 07:05 PM
  3. default value of drop down list
    By Jeff_badz2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2016, 12:22 AM
  4. Error with Dependant Drop downlist (using INDIRECT function)
    By johnkelly11 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-28-2013, 07:10 AM
  5. Help! Calulation based upon a drop downlist??
    By DinDin! in forum Excel General
    Replies: 1
    Last Post: 01-18-2012, 09:53 AM
  6. default drop down box
    By damo07 in forum Excel General
    Replies: 3
    Last Post: 06-25-2010, 08:40 PM
  7. default drop box to a value?
    By juan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-23-2005, 03:06 PM

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