+ Reply to Thread
Results 1 to 16 of 16

How to hide/unhide shapes using drop down list in Excel VBA?

  1. #1
    Registered User
    Join Date
    08-23-2019
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    5

    How to hide/unhide shapes using drop down list in Excel VBA?

    I'm trying to make a dynamic map, where if I choose from a drop down list, a cell will show a specific text, and that will prompt the shape I use for the map to become visible.

    My method is using a base map, the selected shape that appear will cover the base map, making it look like the selected region is highlighted. I have named my shapes and put them in a list labeled Region 1, Region 2, Region 3, so on. I plan for the shapes to be transparent in the first place, and only appear when the reference cell calls for it, which will be prompted by the drop down cell.

    I am just beginning to learn VBA so everything is very new to me. I don't have background in coding, but I can vaguely guess what is happening in the codes. As you may see below, my changes are very basic and amateurish.

    Please Login or Register  to view this content.
    So far there is no error showing, but the codes do not work either.

    Any help would be greatly appreciated, thanks.

    SOLVED: I had put the codes in module instead of the worksheet itself. The code is fine and it works. I have re-upload the corrected file if people want to see.
    Attached Files Attached Files
    Last edited by VBAqueries; 08-28-2019 at 06:21 AM. Reason: Grammar

  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: How to hide/unhide shapes using drop down list in Excel VBA?

    First thing: Worksheet_Change code MUST be in a WORKSHEET vba section
    Will see what I can do further. Understand your question

    I avoided the hide all images, so it looks like nothing is been done
    Attached Files Attached Files
    ---
    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
    08-23-2019
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    5

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    Quote Originally Posted by Keebellah View Post
    First thing: Worksheet_Change code MUST be in a WORKSHEET vba section
    Got it. This was exactly what was wrong with the code. I had put the codes in module instead of the sheet itself. Thanks for the help!

  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: How to hide/unhide shapes using drop down list in Excel VBA?

    Here's the answer I think your were looking for
    I updated the code and re-attached the file
    Attached Files Attached Files

  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: How to hide/unhide shapes using drop down list in Excel VBA?

    Some more changes.
    You seem to have a lot of duplicate shapes on that sheet, but that's something you'll need to workout yourself.
    Try this
    Attached Files Attached Files

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

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    Found what I was looking for, I think this works better.
    Attached Files Attached Files

  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: How to hide/unhide shapes using drop down list in Excel VBA?

    It's a pity you haven't take the time to even answer or say anything about the sample files.
    Anyway, the Final version here as I would do it.
    Hope you like it
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-23-2019
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    5

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    Sorry for the late reply. I didn't really comment because while it's very insightful, it's slightly not what I was looking for. You've already helped tremendously with my problem, and I greatly appreciate your help. I have attached my file so you can see what I was trying to do, note that there is actually a base map that the shapes will overlap on top of, hence making it look like the region is highlighted. I had to delete the base map because there is a limit on the file size that I can attach.
    Thank you again for your help!
    Attached Files Attached Files

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

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    If you look at the final I sent you yesterday, without the base map, you get the same idea.
    the highlight in my idea is the red border around the region, you can change this by coloring all the regions grey and only color the selected region accordingly.
    Your sample does not tell me more than that anyway, you could have added a screen shot of your worksheet as you have it, then the size is not limited.
    Another thing I noticed is that you are entire inconsistent with your region numbers see the attache image
    First thing is to make data consistent
    With the Final I attached your have the code and ideas, hope you can make it work

    I attached a screenshot of my file working
    Attached Images Attached Images

  10. #10
    Registered User
    Join Date
    08-23-2019
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    5

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    Below is the screenshot of my end product.
    The regions are different because I'm using different colors to highlight the region when it reaches certain threshold in GDP, as referenced by the legend.
    For example, Bali with GDP under 3M is light blue (region 3), but once it reaches above 3M, it is medium blue (region 10).
    Using this method I only have to make the shapes appear without needing to write longer codes assigning different color fills for the different GDP.
    I hope this explains my decision.
    Attached Images Attached Images
    Last edited by VBAqueries; 08-26-2019 at 02:52 AM.

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

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    Then I guess you did not correctly at MY file, all the regions show the color for the threshold, if you change the year you will see the colors changing.
    Well, In the file I was missing the other regions to the right, but your first attachment wasn't complete.
    But then again, if you did not look correctly you did not see the colors changing as you select another year. I rest my case. You can work with what I gave you in the FINAl I attached
    Happy coding

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

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    My last Update ( I copied the Base map from your file and placed it here too)
    Maybe this is better?
    Late me know (please?)
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-23-2019
    Location
    Indonesia
    MS-Off Ver
    2016
    Posts
    5

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    I thank you once again for your input, but I'm okay working with the codes I have right now. I have seen your file actually, but like I said it wasn't exactly what I wanted and visually I'm quite happy with my map.
    I have looked at your newest version, I think there's something a little wrong with it. I won't claim to be a coding expert (in fact far from it, I barely know coding), but it's not the most stable as you can see from my screenshot, the map would sometimes give me double highlight. Also, in terms of the colors it's not the most correct as you can also see from the other screenshot (3M-6M should be medium blue).
    Please don't get me wrong, I truly thank you for your help.
    Attached Images Attached Images
    Last edited by VBAqueries; 08-26-2019 at 10:45 PM.

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

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    Yeas, I found my error I forgot a 0

    Chekc this part of the code
    Please Login or Register  to view this content.
    It says 600000 in the code I sent you, it should be 600000
    Sorry for that.
    And no hard feelings. what's fine for you is what is important

  15. #15
    Registered User
    Join Date
    08-12-2019
    Location
    Illinois
    MS-Off Ver
    asa
    Posts
    10

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    I learned, thank you~

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

    Re: How to hide/unhide shapes using drop down list in Excel VBA?

    @Thomasmas: That's the whole idea of this forum, hope you can out it to good use

+ 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] Hide/Unhide named shapes from a list in the same sheet with VBA
    By ploup in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2019, 07:48 AM
  2. Hide/Unhide Sheets based on drop down list
    By toci in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-01-2016, 08:06 AM
  3. [SOLVED] Auto Hide/Unhide Rows Anytime Drop Down List Changes
    By jkle4596 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 10-01-2015, 10:02 AM
  4. Hide/Unhide cells based on drop-down list
    By Savvy25 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2014, 02:36 AM
  5. [SOLVED] Unhide & hide Shapes.
    By pipoliveira in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-14-2013, 07:41 AM
  6. [SOLVED] hide or unhide rows based on text within a drop down list
    By souimet in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2013, 01:32 AM
  7. Hide/unhide columns based on the drop down list value
    By vagif in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2012, 02:13 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