+ Reply to Thread
Results 1 to 8 of 8

VBA select next item from drop down list

  1. #1
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    VBA select next item from drop down list

    Hi Folks,

    I have a workbook where in sheet 1 the user can select from a dropdown list. Data is pulled from sheet 2 and populates cells in sheet 1.

    I then have a short macro that does the following:

    Copies sheet 1

    opens new workbook

    pastes

    saves to file using filepath detailed in cell H1

    Deletes contents of cell H1

    replaces formulas with values.

    Saves workbook

    closes workbook.

    The user can then select the next item in the dropdown list and run the macro again.

    Is there a way that the macro when run would select the next item in the dropdown and repeat the copy, save workbook process until all the items in the dropdown have been done?

    Thanks,

    Dec

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: VBA select next item from drop down list

    Hello Declamatory,

    Toward the end of your Post you state;

    replaces formulas with values.

    Saves workbook

    closes workbook.

    The user can then select the next item in the dropdown list and run the macro again.
    If the Workbook has been closed, how would the user then select the next item in the dropdown list and run the macro again?

    Please attach a sample workbook.

    Make sure there is just enough data to demonstrate your need.

    Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,126

    Re: VBA select next item from drop down list

    @Winon, there is a step that creates a new workbook, which I assume is the one that is saved when closing it.

    Key thing, in order to help, is to understand what your dropdown list is. Activex control / form control / data validation ?
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    Re: VBA select next item from drop down list

    Sorry guys,

    The workbook being closed is the one that was created. The original workbook with the VBA code remains open. the list is Data Validation. I'm not familiar with the other two but I'm not precious about the list and can change things if need be.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,126

    Re: VBA select next item from drop down list

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    03-09-2012
    Location
    Isle of Man
    MS-Off Ver
    Excel 2010
    Posts
    118

    Re: VBA select next item from drop down list

    Hi Andy,

    Thanks for the response. To be honest I'm struggling to see how to use this as I don't understand it very well.

    I didn't really want message boxes and just wanted all of the files created when the macro is run. I've tried to fudge your code by replacing the message box with Application.Run then my macro that saves the worksheet for the item selected.

    The code runs and wants to repeat the action but doesn't select the next item on the list. Can you advise where I have gone wrong.

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,126

    Re: VBA select next item from drop down list

    You did not show us any of your code so pretty unlikely I was going to be able to incorpare my code in yours.

    The code assumes the activecell is the cell with the data validation in it. When the code runs it executes your code where the msgbox info was.
    Try just calling your macro rather than using .Run

    Please Login or Register  to view this content.
    Without seeing actual code and workbook example it's pretty hard to determine what is not working

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: VBA select next item from drop down list

    @ Andy Pope,

    there is a step that creates a new workbook, which I assume is the one that is saved when closing it.
    Thank you Andy, you are correct, please feel free to read me my Rights, my lawyer should be up for parole within five years!

    Best Regards,

    W.

+ 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: 3
    Last Post: 07-01-2017, 09:08 AM
  2. how to auto select drop list item when closing file?
    By Imran Magsi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2016, 02:39 AM
  3. Replies: 5
    Last Post: 04-11-2014, 03:13 PM
  4. Select an item from drop down list and auto fill in next working date
    By pjlau in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2012, 12:49 AM
  5. Select Combobox Item With List Item Select
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-22-2011, 02:40 PM
  6. Replies: 3
    Last Post: 11-15-2008, 09:49 PM
  7. Select item in drop-down list in Vbasic
    By ramza in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2006, 05:59 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