clear/reset the dependent combobox in userform

[dgwan]

command button = clmClear
first combobox = AreaBox
second combobox = RoomBox


Private Sub AreaBox_Change()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("data")
Dim i As Integer
Dim n As Integer

n = Application.WorksheetFunction.Match(Me.AreaBox.Value, sh.Range("A1:H1"), 0)

Me.RoomBox.Clear
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
Me.RoomBox.AddItem sh.Cells(i, n).Value

Next i


End Sub

Private Sub cmbClear_Click()
Me.AreaBox = ""
Me.RoomBox = ""
End Sub


(help it reset the Room box but not Area and it gives me an error)

 

[dmt32]

Hi,
welcome to forum

See if this update to your code does what you want

Private Sub AreaBox_Change()
    Dim sh          As Worksheet
    Dim i           As Long
    Dim n           As Variant
    
    Set sh = ThisWorkbook.Sheets("data")
    
    n = Application.Match(Me.AreaBox.Value, sh.Range("A1:H1"), 0)
    
    If Not IsError(n) Then
        Me.RoomBox.Clear
        For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
            Me.RoomBox.AddItem sh.Cells(i, n).Value
        Next i
    End If
    
End Sub

Dave

 

[dgwan]

im having an error and it highlights this

n = Application.Match(Me.AreaBox.Value, sh.Range("A1:H1"), 0)

 

[dmt32]

You need to let us know the error you are getting but as a guess error 13 type mismatch? if this is so, you need to use all the code as published in #post 2

Dave

 

[dgwan]

Private Sub AreaBox_Change()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("data")
Dim i As Integer
Dim n As Integer

n = Application.Match(Me.AreaBox.Value, sh.Range("A1:H1"), 0)

If Not IsError Then
Me.RoomBox.Clear
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, n).EntireColumn)
Me.RoomBox.AddItem sh.Cells(i, n).Value
Next i
End If

End Sub

(and yes its error 13 type mismatch, additional to that i cant use the submit button for AreaBox too)

 

[dmt32]

but that's not the complete code I posted

Copy the entire code as published in #post 2 & try it again

Dave

 

[dgwan]

i used other variables for that so i only copied the lower part but thanks a lot. that solved my problem even the submit part

 

[dmt32]

Glad update resolved your issue & appreciate your feedback

As friendly future tip, when seeking help always use the FULL code helpers here post & test it unaltered. If you then continue to have problems, feedback the errors you are getting to the person helping you.

Using only part of any suggested solutions or making your own changes the code updates, can as you discovered, lead to other issues & take longer to resolve.

Dave