+ Reply to Thread
Results 1 to 11 of 11

Hide/Unhide a Shape problem

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    NC
    MS-Off Ver
    Excel 2011
    Posts
    5

    Hide/Unhide a Shape problem

    Please someone, tell me what I'm doing wrong! I've looked at this until I'm blue in the face! I get a Sub or function does not exist error. I've attached an example. Just click the grey rectangle below the call out.

    Thanks a million!


    Dim strShapeName As String
    Dim shpCallOut As Shape

    Sub ToggleShape()
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    If Not shpCallOut.Visible Then
    ActiveSheet.shpCallOut.Visible = True
    Else
    ActiveSheet.shpCallOut.Visible = False
    End If
    Application.ScreenUpdating = True
    ActiveSheet.Protect
    End Sub

    Sub ToggleCallOut1()
    strShapeName = "Callout1"
    Set shpCallOut = Shapes(strShapeName)
    Call ToggleShape
    End Sub

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,712

    Re: Hide/Unhide a Shape problem

    Welcome to the forums … Does the below code do what you need ?

    Please Login or Register  to view this content.
    PS: Please use code tags in the future by clicking the # icon & inserting your code between the tags
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

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

    Re: Hide/Unhide a Shape problem

    Please Login or Register  to view this content.
    Couple of things.
    You need to specify what object you are look for shapes in.
    You variable shpCallOut does not require the Activesheet prefix.
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    04-10-2013
    Location
    NC
    MS-Off Ver
    Excel 2011
    Posts
    5

    Re: Hide/Unhide a Shape problem

    Sorry, I don't understand how to use your post. Since I don't know what is wrong with my code, I'm not sure how to integrate yours. Can you please restate it in context?

    Thanks

    PS-I'll add #s between tags next time

  5. #5
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,712

    Re: Hide/Unhide a Shape problem

    Just delete all the code you posted in post #1 from your file & replace it with the code I posted in post #2 & run the macro to see if it does what you need

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,460

    Re: Hide/Unhide a Shape problem

    @GeriJ

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Registered User
    Join Date
    04-10-2013
    Location
    NC
    MS-Off Ver
    Excel 2011
    Posts
    5

    Re: Hide/Unhide a Shape problem

    Thanks Andy, but the object will change. If you are referring to the worksheet, it will be which ever sheet it's on. I'm not sure how to apply your answer. Can you be more specific? I've tried various permutations, but nothing has worked yet.

    Geri

  8. #8
    Registered User
    Join Date
    04-10-2013
    Location
    NC
    MS-Off Ver
    Excel 2011
    Posts
    5

    Re: Hide/Unhide a Shape problem

    To AliGW:
    I'm sorry, I just followed the natural flow of your site. I do not intend to flaunt your rules, just unsure as to what your prose means, particularly for a first time user. I did wonder why my code rendered so poorly. Why not just write the formatting into the code? I truly appreciate your site as I have used to to increase my own capabilities for years and years. Unfortunately, my first post was met with truly unhelpful responses and a finger wag! I'll survive, but I wished for better.

    Geri

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

    Re: Hide/Unhide a Shape problem

    I was explaining why your code was not working.
    Again the revised code should replace your existing code.

    In your example code you explicit set the name of the shape, strShapeName = "Callout1".
    In your reply you now say, "but the object will change".

    Which one is it?
    If the object will change how should the code know what object to hide/show?

  10. #10
    Registered User
    Join Date
    04-10-2013
    Location
    NC
    MS-Off Ver
    Excel 2011
    Posts
    5

    Re: Hide/Unhide a Shape problem

    Each call out will have its own Toggle sub. Only the call out number in 2 places would have to change.

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

    Re: Hide/Unhide a Shape problem

    @GeriJ, not sure if you still need help or not. If you do attach an example file with the code you current have and the issue with that code

+ 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. problem of hide/unhide columns based on drop down list
    By rachelhe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2019, 03:15 AM
  2. [SOLVED] Click a shape to unhide and hide columns
    By Rocky2013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2018, 10:18 AM
  3. [SOLVED] Show shape on cell select, Hide shape when cell is deselected
    By brendangroff in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-25-2016, 03:36 PM
  4. Hide Visibility hide and unhide menu using macro ?
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-30-2015, 09:05 PM
  5. show hide shape to a cell problem with code
    By pavlos_x in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-26-2015, 02:43 PM
  6. Excel 97 hide unhide problem
    By Catt in forum Excel General
    Replies: 2
    Last Post: 06-01-2006, 11:45 AM
  7. Replies: 3
    Last Post: 05-31-2006, 09:30 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