userform indirect alternative

[keith barnes]

I have used "indirect" very successfully when getting a data validation box to populate another data validation box.

Question.

Can someone please provide a simple vba script that that populates a combobox1 with a range which in turn populates a combobox2 with a list based on its own range.

I am trying to get these to work in a userform.

Thanks guys in advance for any help

[snb]

https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[keith barnes]

Hi Forum Member

Many thanks for the mind blowing link to all of the combo / list macros, they were very interesting and i will use some of them for sure.

Whilst they seem to cover all combobox / list options, i could not see a solution to a combobox to combobox based on a range.

Could you please help further.

Many thanks

[snb]

Post a sample file to illustrate your question.

[keith barnes]

Hi snb and other Members

I have attached a brief workbook in an attempt to explain what i am aiming to do.

The form has two combobox, one combobox1 and the other combobox2.

The first one is populated by a range "product" thats the easy bit, but the other one needs to be populated by the the first selection.

I have demonstrated this on the initial sheet as normal data validation dropdown boxes using the =indirect() method.

Appreciate you help with some code to enable the userform to replicate this.

Note, i need the comboboxes to be generated by ranges as these need to be dynamic.

Thanks in advance.

[snb]

VBA is much more dynamic than Excel (named ranges, indirect etc.)
Avoid rowsource anytime.
Reduce the interaction in VBA with a worksheet as much as possible.
No need for 'Named Ranges'; no need for recalculation named ranges: I removed them all.

[Aussiebear]

.
No need for named ranges

This absolute rubbish. Named ranges is supported by almost all of the experts and your comments are clearly false.

[snb]

@aussie

If you open the file you will notice that it performs 100% what has been asked for.
The file contains no named range at all.
Ergo: to let the code perform what has been asked for, no named range is necessary.
Probably you misread my supporting text.

[keith barnes]

Hi snb
Many thanks. I have made this alternative work and it all seems to do what i hoped it would do.

Thanks again:yes

[keith barnes]

Hi Guys

Unfortunately, i have to come back to you guys for help again on the same matter. I have again attached the sample userform in the hopes that someone can help with my initial problem.

Just to recap, i wish to create a dependent combobox2 based on the results of combobox 1. The issue being all list details of both comboboxes need to be from "named ranges" and not column results from a worksheet.

Appreciate any help.

Regards

keith

[arnelgp]

see code on Change event of the combo1

[keith barnes]

Many thanks arnelgp. This is what i have been looking for for weeks:yes:yes

[arnelgp]

:friends:

[Paul_Hossler]

Named ranges have their uses, but sometimes they might cause subtle problems as data gets added and deleted



29946

I'd go more old school



Option Explicit


Private Sub combobox1_Change()
Dim i As Long
Dim r As Range

i = 0
On Error GoTo 0
i = Application.WorksheetFunction.Match(Me.combobox1.Value, ActiveSheet.Rows(1), 0)
On Error GoTo 0


If i = 0 Then Exit Sub


Set r = ActiveSheet.Cells(1, i)
Set r = Range(r, r.End(xlDown))
Set r = r.Cells(2, 1).Resize(r.Rows.Count, 1)


With Me.Combobox2
.Value = ""
.RowSource = r.Address

End With
End Sub




Private Sub UserForm_Initialize()
Dim r As Range

Set r = ActiveSheet.Cells(1, 1)
Set r = Range(r, r.End(xlDown))
Set r = r.Cells(2, 1).Resize(r.Rows.Count, 1)


Me.combobox1.RowSource = r.Address
End Sub

[arnelgp]

and also you can use Table as source of you combo2.