save combobox value as global variable and select row of selected value

Question

I want to save selected combobox value as global variable and select row of selected value I have excel file, where i want to make caluclations based on inputs in sheet1 and data on sheet2. Inputs are provided by combobox1 (list of names from coluom A in sheet 2), combobox2(case yes/no) and combobox3 (values 1,2,3). After I select value in combobox1(for exsample: ABC which is value A7 from sheet2), I want to calculate from data in row 7 in sheet2:B7(sheet2) +C7(sheet2)combobox3 value+D7(sheet2)(combobox2(yes=2/no=0). can anyone help me with that?

Public SelectedComboBox1 As String
Public SelectedComboBox2 As String
Public SelectedComboBox3 As intiger
Public calculate2 As Integer

    Private Sub ComboBox1_DropButtonClick()
    Sheet1.ComboBox1.List = Sheet2.Range("A3:A46").Value
    SelectedComboBox1 = Me.ComboBox1.Value
       End Sub

    Private Sub ComboBox2_DropButton()
    With Me.ComboBox2
    .AddItem "YES"
    .AddItem "NO"
    End With
     SelectedComboBox2 = Me.ComboBox2.Value
    End Sub
    
    Private Sub ComboBox3_DropButton()
    With Me.ComboBox3
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    End With
     SelectedComboBox3 = Me.ComboBox3.Value
    End Sub
    
    Public Sub Calculate2_click()
    calculate2 = Sheet2.Range("B7") * Sheet2.Range("C7") * SelectedComboBox3+Sheet2.Range("D7")*??
    Sheet1.Range("H10").Value = calculate2
    End Sub

You need to bind the assigning part variable = combobox.value to the OnChanged-event. — Johanness

Johanness Johanness · Accepted Answer · 2021-07-05T15:00:34

It seems like you added a ActiveX combobox. You might want to use a form-combobox instead for Excel Sheets. Nevertheless: in the Editor you can add varioous actions to events in the combobox. What you did is add a reaction to the button-down-click Event. There you told excel to fill the list and set the SelectedCombobox variable to the - as of yet undefined - value of the combobox. What you might want is one sub to fill the list as you did and another sub reacting to the change event of the combobox. That sub will be called as soon as someone changes the value of the box:

Private Sub ComboBox1_DropButtonClick()
  Sheet1.ComboBox1.List = Sheet2.Range("A3:A46").Value
End Sub

Private Sub ComboBox1_Change()
  SelectedComboBox1 = Me.ComboBox1.Value
End Sub

This should get you a good start. There are plenty of resources out-there that teach you how to write effective macros in Excel.

If you just need a result that uses the value of these three comboboxes, you could also connect a simple form-comboboxes with respective cells and claculate the result out of those cells. But if you still want to use vba, think about using just one button to trigger a sub and access the values immediately:

Private Sub Button1_click
  cells("A1").value=Me.ComboBox1.Value * Me.ComboBox2.Value...
End Sub

save combobox value as global variable and select row of selected value

1 Answers