CODEWITHSUNDEEP
excelvbacombobox
Eduards
Eduards

Reputation: 68

VBA combobox options based on another combobox

I have a combobox with options from 300 to 650 with increment of 10 and I need another combobox to display options -5 of previous value and -15 of previous value so in case the first combobox selection is 300 then the other combobox displays options 295 and 285. I know it's possible to manually write down all the options for all the cases but it would be insane. I wonder if someone has a simple solution for this?

Private Sub UserForm_Initialize()
ComboBox1.AddItem "300"
ComboBox1.AddItem "310"
ComboBox1.AddItem "320"
End Sub

Private Sub ComboBox1_Change()
Application.EnableEvents = False
ComboBox2.Clear
Application.EnableEvents = True

Select Case ComboBox1.Value
    Case "300"
        ComboBox2.AddItem "295"
        ComboBox2.AddItem "285"
End Select
End Sub

Upvotes: 0

Views: 1507

Answers (2)

VBasic2008
VBasic2008

Reputation: 54767

Fill Combo Boxes

  • For fast loading data to a combo or a list box, it is recommended to use the List property with an array of values.

The Code

Option Explicit

Private Sub UserForm_Initialize()
    Const nMin As Long = 300
    Const nMax As Long = 650
    Const nInc As Long = 10
    Dim n As Long: n = Int((nMax - nMin) / nInc)
    Dim arr() As Long: ReDim arr(0 To Int((nMax - nMin) / nInc))
    Dim nCurr As Long: nCurr = nMin
    Dim i As Long
    For i = 0 To n
        arr(i) = nCurr
        nCurr = nCurr + nInc
    Next i
    ComboBox1.List = arr
End Sub

Private Sub ComboBox1_Change()
    With ComboBox2
        .Clear
        .AddItem ComboBox1.Value - 5
        .AddItem ComboBox1.Value - 15
    End With
End Sub

Upvotes: 1

FunThomas
FunThomas

Reputation: 29146

Use simple math:

Private Sub UserForm_Initialize()
    Dim i As Long
    ComboBox1.Clear
    For i = 300 To 650 Step 10
        ComboBox1.AddItem CStr(i)
    Next
End Sub

Private Sub ComboBox1_Change()
    Application.EnableEvents = False
    ComboBox2.Clear
    Application.EnableEvents = True
    Dim cb1Val
    cb1Val = val(ComboBox1.Value)
    If cb1Val > 0 Then
        ComboBox2.AddItem CStr(cb1Val - 5)
        ComboBox2.AddItem CStr(cb1Val - 15)
    End If
End Sub

Upvotes: 1

Related Questions