Excel VBA Search for Shape

1
votes

I was hoping someone could point out where I am going wrong with this code. I am using it to search for a shape on a worksheet. I want to trigger a message when that shape does not exist. With the code below, why does it go to the error handler when the shape actually does exist?

Private Sub CommandButton1_Click()

On Error GoTo errHandler

Dim SearchFor
SearchFor = UCase(InputBox("Search: "))

ActiveSheet.Shapes.Range(Array(SearchFor)).Visible = True

errHandler:
MsgBox "No Reference Found For: " & SearchFor

End Sub
1
excelvba

1 Answers

2
votes

You always go to your error handler, that's why. I added Exit Sub before reaching it:

 Private Sub CommandButton1_Click()

On Error GoTo errHandler

Dim SearchFor
SearchFor = UCase(InputBox("Search: "))

ActiveSheet.Shapes.Range(Array(SearchFor)).Visible = True
Exit sub
errHandler:
MsgBox "No Reference Found For: " & SearchFor

End Sub

It will never reach what is after Exit Sub when the shape is found. If there is an error, the GoTo will bypass it. If you want to read more on GoTo: https://msdn.microsoft.com/en-us/library/69whc95c.aspx