+ Reply to Thread
Results 1 to 4 of 4

Show/Hide Shape VBA

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,176

    Show/Hide Shape VBA

    Within a VBA code, I have a line of code at the start of the code to show a shape. At the end of code I hide the same shape. The problem is that when I run the code, the shape is not visible. If I "step" through the code and place a stop at the line ActiveSheet.Shapes("PushPin_598").Visible = True, the shape will show up. I can't figure out why the shape won't show up if I run it with no stops and it does show up when I step through the code. Thanks in advance for any comments.
    Sub GetZip()
    
    Dim ZipCode         As Variant
    Dim BranchCode      As Double
    Dim BranchName      As String
    Dim BranchState     As String
    Dim BranchDivision  As String
    
    Dim StateCode       As String
    Dim StateName       As String
    
    Dim Message         As String
    Dim Choose          As String
    
    Again:
    
        On Error GoTo ErrorMessage
    
        ZipCode = InputBox("Enter the Zip Code", "Zip Code Search")
        If ZipCode = "" Then Exit Sub
        If Len(ZipCode) <> 5 Then GoTo ErrorMessage
    
        ActiveSheet.Shapes("PushPin_598").Visible = True
        
        BranchCode = Application.WorksheetFunction.VLookup(Left(ZipCode, 3), Range("ZipCodeTable"), 2, False)
        BranchName = Application.WorksheetFunction.VLookup(Left(ZipCode, 3), Range("ZipCodeTable"), 3, False)
        BranchState = Application.WorksheetFunction.VLookup(Left(ZipCode, 3), Range("ZipCodeTable"), 4, False)
        BranchDivision = Application.WorksheetFunction.VLookup(Left(ZipCode, 3), Range("ZipCodeTable"), 5, False)
        StateName = Application.WorksheetFunction.VLookup(BranchState, Range("StateCodeTable"), 2, False)
        
        MsgBox _
        "Data for Zip Code " & ZipCode & vbCrLf & vbCrLf & _
        "The state for Zip Code " & ZipCode & " is located in the State of " & StateName & "." & vbCrLf & vbCrLf & _
        "Division:  " & BranchDivision & vbCrLf & _
        "Branch Number:  " & BranchCode & vbCrLf & _
        "Branch Name:  " & BranchName, vbOKOnly, "Zip Code Search"
        
        ActiveSheet.Shapes("PushPin_598").Visible = False
        
        Exit Sub
    
    ErrorMessage:
            Message = "Invalid entry or the zip code entered was not found.  Try again?"
            Choose = MsgBox(Message, vbYesNo, "Error Message")
            If Choose = vbYes Then GoTo Again
            
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Show/Hide Shape VBA

    why not eliminate hide line ?
    If solved remember to mark Thread as solved

  3. #3
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,176

    Re: Show/Hide Shape VBA

    Thanks for suggestion but my manager does not want to see the shape once the code has completed executing completly. Thanks.

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Show/Hide Shape VBA

    your code seems correct, attach a samplefile for testing

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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