Search code examples
excelvbacomboboxuserform

Clear Combo box when user form is shown, I keep getting an error no matter where I put the clear command


I have tried a number of ways to get the combobox I have on a userform to clear the value before showing. See code below, as my last attempt.

Sub Call_Userform4()

Sheet8.Activate
    Sheet8.ListObjects("Table1").AutoFilter.ShowAllData
    Sheet8.ListObjects("Table1").Sort.SortFields.Clear
    Sheet8.ListObjects("Table1").Sort.SortFields.Add2 _
        Key:=Range("Table1[[#All],[Name (Last, First)]]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            With Sheet8.ListObjects("Table1").Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With


    With UserForm4
      .StartUpPosition = 0
      .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
      .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
      .ComboBox1.Clear
      .Show
    End With
    

End Sub

Solution

  • Figured it out.

    Sub Call_Userform4()
    
      Sheet8.Activate
        Sheet8.ListObjects("Table1").AutoFilter.ShowAllData
        Sheet8.ListObjects("Table1").Sort.SortFields.Clear
        Sheet8.ListObjects("Table1").Sort.SortFields.Add2 _
            Key:=Range("Table1[[#All],[Name (Last, First)]]"), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortTextAsNumbers
                With Sheet8.ListObjects("Table1").Sort
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
    
    
        With UserForm4
          .StartUpPosition = 0
          .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
          .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
          .Show
        End With
        
        With UserForm4.ComboBox1
            .Value = ""
        End With
        
    End Sub