Forum Discussion
Error Handling in a Macro
I have written a Workbook_Open() script which runs on startup and checks for the presence or absence of a named TextBox, The code that follows this check depends upon whether or not the TexBox exists.
The problem is, I get Error 1004 when I try to delete it and its not there, and the IfError statement does not handle this situation. I have not tried the condition of selecting it when its not there, but I assume I would get the same result.
Is there any other way to determing the presence or absence of a TextBox using a macro?
I had some trouble getting your first solution to work properly (not worth going into detail), but I got your second solution working and I like it. Actually, I like it better than your first because it indexes through all TextBoxes. Thanks again for all your help. I thought I was an expert on Excel - I have been using it for many years - but I was oh, so wrong.HansVogelaar
There are several ways you can do this:
Private Sub Workbook_Open() Dim shp As Shape On Error Resume Next Set shp = Worksheets("Sheet1").Shapes("NameOfTextBox") On Error GoTo 0 If shp Is Nothing Then ' Named text box does not exist Else ' Named text box exists End If End Subor
Private Sub Workbook_Open() Dim shp As Shape Dim blnExists As Boolean For Each shp In Worksheets("Sheet1").Shapes If shp.Name = "NameOfTextBox" Then blnExists = True Exit For End If Next shp If blnExists Then ' Named text box exists Else ' Named text box does not exist End If End SubI had some trouble getting your first solution to work properly (not worth going into detail), but I got your second solution working and I like it. Actually, I like it better than your first because it indexes through all TextBoxes. Thanks again for all your help. I thought I was an expert on Excel - I have been using it for many years - but I was oh, so wrong.HansVogelaar
