Combobox entry activates range for anoher Combobox

[Strom323]

First up Hi people. I have been having some success with using vba forms to write to a table. My next task that im seeking assistance with is.

I have a Form with 2 Comboboxes

Id like to be able to achieve the following

select item in first combo box in userform based on column in MTable (done)
write entry to table (not sure how to do this and stay in the procedure/userform for next step)
MTable calculates range and shows it in a column cell for second combobox (this table is already created and called MTable)
second combobox reads the range from the table cell and displays range items accordingly

The calculated range for the second Combobox is in the 7th column of MTable

hope this makes sense.

 

[Strom323]

My edit button is gone in the forum so i will need to clarify in this post.

1st combobox reads from MTable column

it writes selection to STable column

STable column 7 calculates MTable range for second Combo box in user form

 

[Arjan77]

The cells which are changed by ComboBox1 are updated instantly if you use ComboBox1_Change(), within the ComboBox1_Change sub you can also update ComboBox2. See simple example below:

Dim ws As Worksheet


Private Sub UserForm_Initialize()
ComboBox1.AddItem "A"
ComboBox1.AddItem "B"
ComboBox1.AddItem "C"
End Sub

Private Sub ComboBox1_Change()
Set ws = ThisWorkbook.Worksheets("Sheet1")
Ctest
ComboBox2.Clear
For i = 1 To 3
ComboBox2.AddItem ws.Cells(i, 6)
Next

End Sub



Sub Ctest()
Set ws = ThisWorkbook.Worksheets("Sheet1")

Select Case ComboBox1.ListIndex
Case 0
ws.Cells(1, 4) = "A"
Case 1
ws.Cells(1, 4) = "B"
Case 2
ws.Cells(1, 4) = "C"
End Select

End Sub

 

[Strom323]

Thanks Arjan77. I have looked into this and will need to do some reinterpretation of the code. So far I'm getting a runtime error 70 for the userform code when it hits the comobox definition.

 

[Arjan77]

Is your combobox defined as ComboBox1? Can you provide your code as it is now?

 

[Strom323]

Is your combobox defined as ComboBox1? Can you provide your code as it is now?

Arjan I noticed that I should have double clicked to open a new sub for code for the cboGeneric2_Change() routine which I have since done and placed the code into.

However the previous error was triggered by this line

cboGeneric2.AddItem "A"

I think the problem is where do I double click to open a new sub for the

Private Sub UserForm_Initialize()

part of the code you have posted that I have replicated as

Private Sub SaveSelectionTableEntry1_Click()

Private Sub cboGeneric2_Change()

Set STable = Worksheets("STable")
Ctest
cboLegend2.Clear
For i = 1 To 3
cboLegend2.AddItem STable.Cells(i, 6)
Next

End Sub

Private Sub SaveSelectionTableEntry1_Click()

Dim STable As Worksheet

cboGeneric2.AddItem "A"
cboGeneric2.AddItem "B"
cboGeneric2.AddItem "C"

End Sub

Sub Ctest()
Set STable = Worksheets("STable")

Select Case cboGeneric2.ListIndex
Case 0
STable.Cells(1, 4) = "A"
Case 1
STable.Cells(1, 4) = "B"
Case 2
STable.Cells(1, 4) = "C"
End Select

End Sub

 

[Arjan77]

The code is just an example how it might work, what was the original input for cboGeneric2? If you already had an instance of userform_initialize you can paste the code which I use in that sub. When is the SaveSelectionTableEntry1_Click() sub triggered?

 

[Strom323]

From a button on a form so should be OK as error/debug appears after pressing button.