bostonhero99
06-21-2018, 09:09 AM
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
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