Results 1 to 2 of 2

Thread: Compile Error with ComboBox

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Compile Error with ComboBox

    I am trying to format 2 combo boxes embedded in an excel sheet. Based off of what the first combobox changes to, the second box list will change accordingly. When I open the workbook on a new computer, I get the error: "Compile error: Method or data member not found" The combobox name is has a problem with is called "ServiceType".

    Sub ComboBox1_Change()
    Range("H16:L19").Clear
    Application.ScreenUpdating = False
    Sheets("Sheet2").Visible = True
    
    If Me.ComboBox1.Value = "Select Field" Then
        
        Dim slct(1 To 1) As Variant
        slct(1) = "Select Service"
        On Error Resume Next
        
        
        Me.ServiceType.List = slct '******* error here and every other occurence
        Me.ServiceType.ListIndex = 0
        Sheets("Sheet2").Visible = False
        Exit Sub
        
        
    ElseIf Me.ComboBox1.Value = "Cat1" Then
        Dim drlg() As Variant
        drlg = Worksheets("Sheet2").Range("C2:C29").Value
        Me.ServiceType.Clear
        Me.ServiceType.List = drlg
        Me.ServiceType.ListIndex = 0
        
        
    ElseIf Me.ComboBox1.Value = "Cat2" Then
        Dim compl() As Variant
        compl = Worksheets("Sheet2").Range("D2:D28").Value
        Me.ServiceType.Clear
        Me.ServiceType.List = compl
        Me.ServiceType.ListIndex = 0
        
        
    ElseIf Me.ComboBox1.Value = "Cat3" Then
        Dim fac() As Variant
        fac = Worksheets("Sheet2").Range("E2:E41").Value
        Me.ServiceType.Clear
        Me.ServiceType.List = fac
        Me.ServiceType.ListIndex = 0
        
        
    ElseIf Me.ComboBox1.Value = "Cat4" Then
        Dim prod() As Variant
        prod = Worksheets("Sheet2").Range("F2:F64").Value
        Me.ServiceType.Clear
        Me.ServiceType.List = prod
        Me.ServiceType.ListIndex = 0
        
        
    ElseIf Me.ComboBox1.Value = "Cat5" Then
        Dim saf() As Variant
        saf = Worksheets("Sheet2").Range("G2:G20").Value
        Me.ServiceType.Clear
        Me.ServiceType.List = saf
        Me.ServiceType.ListIndex = 0
        
        
    ElseIf Me.ComboBox1.Value = "Cat6" Then
        Dim mid() As Variant
        mid = Worksheets("Sheet2").Range("H2:H5").Value
        Me.ServiceType.Clear
        Me.ServiceType.List = mid
        Me.ServiceType.ListIndex = 0
        
        
    ElseIf Me.ComboBox1.Value = "Cat7" Then
        Dim msp(1 To 1) As Variant
        msp(1) = Worksheets("Sheet2").Range("I2").Value
        Me.ServiceType.Clear
        Me.ServiceType.List = msp
        Me.ServiceType.ListIndex = 0
        
    
    
        
    ElseIf Me.ComboBox1.Value = "Other" Then
        Range("H16").Value = "Other:"
        Range("H16").HorizontalAlignment = xlRight
        Range("I16:L16").Merge
        Range("I16:L16").HorizontalAlignment = xlCenter
        Range("I16:L16").VerticalAlignment = xlCenter
        Range("I16:L16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Dim otr(1 To 1) As Variant
        otr(1) = "Other"
        Me.ServiceType.Clear
        Me.ServiceType.List = otr
        Me.ServiceType.ListIndex = 0
        
        
        Range("H19").Value = "Other:"
        Range("H19").HorizontalAlignment = xlRight
        Range("I19:L19").Merge
        Range("I19:L19").HorizontalAlignment = xlCenter
        Range("I19:L19").VerticalAlignment = xlCenter
        Range("I19:L19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
    End If
    Me.Range("C16").Value = Me.ComboBox1.Value
    Me.Range("C19").Value = Me.ServiceType.Value
    End Sub
    Last edited by Paul_Hossler; 06-25-2018 at 07:52 AM. Reason: Added CODE Tags - Use the [#] icon to insert them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •