VBA comboBox not returning second drop down list

user_18191130 0
Viewed 76

Ok, not really sure what I did wrong but when I run it, it gives me an overflow error. The drop down list in the first combo Box works fine, but when I select the "subcat" from the list it gives me the overflow error.

Any clues?

Option Explicit
Private Sub ComboBox1_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Detailed")
Dim i As Integer
Dim n As Integer

n = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("3:3"), 0)
Me.ComboBox2.Clear
For i = 4 To Application.WorksheetFunction.CountA(sh.Cells(3, n).EntireColumn)
    Me.ComboBox2.AddItem sh.Cells(i, n).Value
Next i

End Sub


Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Detailed")

Dim i As Integer

Me.ComboBox1.Clear
For i = 1 To Application.WorksheetFunction.CountA(sh.Range("3:3"))
    Me.ComboBox1.AddItem sh.Cells(3, i).Value
    'ComboBox1.Text = "SubCat"
    
Next i

End Sub

enter image description here

enter image description here

vba excel
2 Answers

Change the declaration of i to be of type Long, since the number of cells in EntireColumn dramatically exceeds the capacity of an Integer (the extant type for i).

user_188247270

There are several issues in the error procedure ComboBox1_Change():

  • Declare all your Integers as Long to avoid an overflow error 6, which btw is the typical for cases with row values exceeding the positive integer limit of 32 767.
  • Define your data's HeaderRow, e.g. as constant Const HeaderRow As Long = 3
  • Extend the loop range by adding the number of rows before the HeaderRow, i.e. HeaderRow - 1:
For i = HeaderRow + 1 To Application.WorksheetFunction.CountA(sh.Cells(HeaderRow, n).EntireColumn) + (HeaderRow - 1)
    ' ...
Next i

Further hints

Note that the chosen way to count the number of data rows would ignore empty cells, so that I'd prefer something like

Dim LastRow as Long
LastRow = sh.Cells(sh.Rows.Count, n).End(xlUp).Row
For i = HeaderRow + 1 To LastRow
    '...
Next i

As adding each element one by one can be time-consuming,
I'd prefer to avoid a cellular loop and to write the whole column data to a variant datafield array and assign them eventually to the combobox'es .List property in one go.

With sh
    Dim data As Variant
    data = .Range(.Cells(HeaderRow+1,n),.Cells(LastRow,n)).Value2
    Me.ComboBox1.List = data
End With

which could be shortened even to

With sh
    Me.ComboBox1.List = .Range(.Cells(HeaderRow+1,n),.Cells(LastRow,n)).Value2
End With
user_64602970
Related