Forum Discussion

aekbus's avatar
aekbus
Brass Contributor
Nov 03, 2024

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 exist...
Excel
  • aekbus's avatar
    aekbus
    Nov 03, 2024

    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 

HansVogelaar's avatar
HansVogelaar
MVP

aekbus 

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 Sub

or

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 Sub

 

aekbus's avatar
aekbus
Brass Contributor
Nov 03, 2024

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 

Resources