+ Reply to Thread
Results 1 to 7 of 7

Automatizing from a drop down menu

  1. #1
    Registered User
    Join Date
    11-17-2017
    Location
    Myanmar
    MS-Off Ver
    2016
    Posts
    8

    Automatizing from a drop down menu

    Hello Excel Masterminds,
    I'm currently working for a small company that often needs to put about 100 photos into an Excel sheet. About a week ago in a separate post a generous user helped me to build a data validation system so we can simply select the photos from the drop down menu. I extended the system so it is on all of our pages and works like a charm.

    However, as I learn more about macros and data validation I was wondering if it is possible to go the next step (when you give an excel newbie a short cut.... ). The drop down menus in the template I built will always automatically select for the name of the correct photo. That is the photos are named A1, A2....B1 B2 ect...and my team will name the photos to match the space on the menu. Even when I swap in a completely new photo folder the list still auto selects the correct photo as long as the naming scheme is consistent (which is always will be).

    Given those parameters, is it possible to create a macro, or some sort of VBA code that will simply insert all the photos when run? Currently we just have to select the drop down and press enter, but if something did that for all the drop down menus on a sheet, that will speed up the process even more. If that's not possible or practical, please let me know. I'm here to learn!

    Here is a sample workbook. Let me know if you have any questions and thank you in advance.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatizing from a drop down menu

    I've probably got something that might help you.
    https://www.linkedin.com/pulse/excel...hans-hallebeek

    I've attached the file in case the link doesn't work.
    Would this be a solution for you?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    11-17-2017
    Location
    Myanmar
    MS-Off Ver
    2016
    Posts
    8

    Re: Automatizing from a drop down menu

    Hey thanks for the response!
    Hmmm, not quite what I'm looking for. Being able to insert or view the photos isn't the issue, so much as the time consumption of going through each drop down list to, clicking it and pressing enter. The photos we need are already selected in the drop down menu by nature of how the template is built. Any idea is there is a way to create a macro/button that will effectivly click on the drop down menus, and click enter for a sheet?
    The sample worksheet I attached might clarify the problem. Basically imagine about a 100 photos across several sheets that need to be inserted. We need to do it about 4 times a year for 110 sits, so I'm trying to automize as much as possible.

    Thanks again.
    -K-Beaux

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatizing from a drop down menu

    Okay, I'll take a look at the file you posted and see if I can help some there.
    The sample file is just straight forward, select the folder with pictures, photos etc and you can scroll through each one, just an idea, nothing more, nothing less

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatizing from a drop down menu

    Since I get a ref error because the picture folder you link to doesn't exist I have the following question.
    In the worksheets Before and Tower you have a dropdown where to can select one file
    What is it exactly you need / require?

    I have attached a VBA Function I use to load ribbon icons as well as pictures in userforms or cells, maybe it can help you
    I came across it long time after I wrote the sample I attached earlier but maybe you can put it to use, not much explanation in it but I think its clear how it works

    I'll see if I can invent some pictures and make you sample work, but I hate renaming pictures or having hardcoded names in my programs
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-17-2017
    Location
    Myanmar
    MS-Off Ver
    2016
    Posts
    8

    Re: Automatizing from a drop down menu

    I think that system could work well, maybe even a little faster than the drop downs/naming system. I'll do some further testing!

    I can upload a prenamed sample folder in the morning too. (I'm 12ish hrs ahead probably) Too answer your question basically I was wondering if it is possible to automate to the point where I can run a macro that will import all the photos in a sheet without having to individually click on each drop down menu and press enter. The drop-down will automatically select the correct photo (D3 will always be the correct photo ect....)..so given that it seemed like further automation may be possible/easy. Like a button that basically tells the drop downs to open and press enter? But as a newbie I wasn't sure. Does that make sense? If it doesn't it's probably just not possible. Still learning, sorry!

    I know the names are annoying but I think it's the naming system set up by our subcons.

    Thanks for taking a look. May be a goose chase so I'm super appreciative.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Automatizing from a drop down menu

    You're about 5 1/2 hours ahead it's 22:15 at your place now it's 16:45 here (Holland)

+ 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] How a drop down menu's list can change depending on another dropdown menu.
    By marc1980 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-21-2014, 04:58 PM
  2. Resetting a drop down menu by selecting another drop down menu
    By Chris07 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2013, 11:57 AM
  3. Replies: 1
    Last Post: 02-07-2013, 11:03 AM
  4. [SOLVED] I cant work out how to create a drop down menu that relates to another drop down menu
    By louise2613 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-18-2012, 01:49 PM
  5. Replies: 0
    Last Post: 06-25-2012, 03:22 PM
  6. Replies: 2
    Last Post: 01-30-2009, 04:23 PM
  7. [SOLVED] Drop down menu-How do you produce a drop down menu in a single cell in Excel
    By Freddo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-04-2006, 04: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