Invalid, Unqualified Referance error

I have realized I need to trap for an error when a Shape on the Structures sheet does not exist. So when

.Column(1, .ListCount - 1) = Worksheets("Structures").Shapes(Struct.Text).Name

errors because that Shape does not exist I want to referance

.Column(1, .ListCount - 1) = Worksheets("Structures").Shapes("Blank").Name

I have tried

On error resume next

But then the user gets an error when they select an item that does not have an associated Shape. So I wanted to bring up a shape named Blank instead.

Here is the code. Currently I am getting a Compile Error, Invalid, Unqualified referance in the last line before the End.

  Dim LastRow As Long
  Dim StartRow As Long
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("TOC")
    StartRow = 3
    LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
    n = 0
      
      With ComboBox1
        For Each Struct In Wks.Range(Cells(StartRow, "A"), Cells(LastRow, "A"))
          .AddItem
          .Column(0, .ListCount - 1) = Struct.Text
              Select Case Struct
                 Case Is = "#6", "#7", "#8"
                    .Column(1, .ListCount - 1) = Worksheets("Structures").Shapes("#6").Name
                 Case Else
                    .Column(1, .ListCount - 1) = Worksheets("Structures").Shapes(Struct.Text).Name
                End Select

       On Error GoTo NoStruct

          Next Struct
      End With
     
      ComboBox1.ListIndex = 0

NoStruct:
  .Column(1, .ListCount - 1) = Worksheets("Structures").Shapes("Blank").Name


End Sub

Hello Bope,

I revised your macro to trap the error. You're getting the error because this line of code belongs to the control block With ComboBox1. The period prefix indicates this.

  .Column(1, .ListCount - 1) = Worksheets("Structures").Shapes("Blank").Name

Revised Macro with Error Handling

  Dim LastRow As Long
  Dim StartRow As Long
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("TOC")
    StartRow = 3
    LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
    n = 0
      
      With ComboBox1
        For Each Struct In Wks.Range(Cells(StartRow, "A"), Cells(LastRow, "A"))
          .AddItem
          .Column(0, .ListCount - 1) = Struct.Text
              Select Case Struct
                 Case Is = "#6", "#7", "#8"
                    .Column(1, .ListCount - 1) = Worksheets("Structures").Shapes("#6").Name
                 Case Else
                    On Error Resume Next
                      .Column(1, .ListCount - 1) = Worksheets("Structures").Shapes(Struct.Text).Name
                      If Hex(Err) = "80070057" Then
                        Err.Clear
                        .Column(1, .ListCount - 1) = Worksheets("Structures").Shapes("Blank").Name
                      End If
                End Select
           'Reset system error handling 
            On Error GoTo 0

          Next Struct
      End With
     
      ComboBox1.ListIndex = 0

End Sub

Sincerely,
Leith Ross

Thanks Leith. That works perfect now. I love doing this stuff but I don't get a chance often enough to learn all I need to know. I am sure I have forgotten more VBA than I know now.