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 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?

  • 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 

  • 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

      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