Multiple combobox and change events
 

Hi All, please bare with me, I've been using VBA for a little over a
week now.

I have a form with two combobox's. The first pulls some data from the
worksheet and fills in some textbox's after running a simple equation
on them. The second combobox updates those same textbox's with the
same equation, but uses listindex from the first combobox in that
equation. Both do there work in _Change events subroutines.

At runtime I get an error because listindex isn't being passed to the
second combobox _Change event Sub. I get Run-time error: '1004':
Method 'Range' of object '_Global' failed. If I change the event to
AfterUpdate it works fine, but you have to click on the first combobox
to get the fields to update.

Am I doing something stupid?


Private Sub BinParameter_Change()
Dim ColumnReference As String
ColumnReference = "" & ColNo2ColRef(BinParameter.ListIndex + 1) &
":" _
& ColNo2ColRef(BinParameter.ListIndex + 1) &
""

Dim BinIncrement As Single
BinIncrement =
(Application.WorksheetFunction.Max(Range(ColumnRef erence)) - _

Application.WorksheetFunction.Min(Range(ColumnRefe rence))) /
(NumberOfBins.ListIndex + 1)

Bin1.Text =
Truncate(Application.WorksheetFunction.Min(Range(C olumnReference)) +
BinIncrement * 1, 8)
Bin2.Text =
Truncate(Application.WorksheetFunction.Min(Range(C olumnReference)) +
BinIncrement * 2, 8)
Bin3.Text =
Truncate(Application.WorksheetFunction.Min(Range(C olumnReference)) +
BinIncrement * 3, 8)
End Sub


Private Sub NumberOfBins_Change()
Dim ColumnReference As String
ColumnReference = "" & ColNo2ColRef(BinParameter.ListIndex + 1) &
":" _
& ColNo2ColRef(BinParameter.ListIndex + 1) &
""

Dim BinIncrement As Single
BinIncrement =
(Application.WorksheetFunction.Max(Range(ColumnRef erence)) - _

Application.WorksheetFunction.Min(Range(ColumnRefe rence))) /
(NumberOfBins.ListIndex + 1)

Bin1.Text =
Truncate(Application.WorksheetFunction.Min(Range(C olumnReference)) +
BinIncrement * 1, 8)
Bin2.Text =
Truncate(Application.WorksheetFunction.Min(Range(C olumnReference)) +
BinIncrement * 2, 8)
Bin3.Text =
Truncate(Application.WorksheetFunction.Min(Range(C olumnReference)) +
BinIncrement * 3, 8)

End Sub