View Full Version : [SOLVED:] userform indirect alternative
keith barnes
07-07-2022, 02:37 AM
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
https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html
keith barnes
07-07-2022, 11:13 AM
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
Post a sample file to illustrate your question.
keith barnes
07-07-2022, 10:10 PM
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.
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
07-08-2022, 02:18 AM
.
No need for named ranges
This absolute rubbish. Named ranges is supported by almost all of the experts and your comments are clearly false.
@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
07-08-2022, 11:21 AM
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
07-11-2022, 12:54 PM
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
07-11-2022, 05:22 PM
see code on Change event of the combo1
keith barnes
07-11-2022, 05:37 PM
Many thanks arnelgp. This is what i have been looking for for weeks:yes:yes
arnelgp
07-11-2022, 06:19 PM
:friends:
Paul_Hossler
07-11-2022, 07:16 PM
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
07-11-2022, 11:32 PM
and also you can use Table as source of you combo2.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.