combo box input range

MrHaggis

New Member
Joined
Oct 6, 2006
Messages
18
Hi All,
This board is amazing!! I've been searching trying to find the answer to my problem, but can't find precisely what I'm after, so I'll try a new post. Please forgive me if this is something covered in previous topics.

Is there any way to populate the input range of a combo box in a worksheet with dynamic information, ie the result of a filtered list?

I have a long list of products in say, 3 columns, each product with a grouping code attached :
A1: 0001 B1: Brakes C1: $40.00
A2: 0001 B2: Tires C2: $55.00
A3: 0001 B3: Filters C3: $ 6.00
A4: 0002 B4: Widgets C4: $10.00


(0001 Brakes, 0001 Tires, 0001 Filters would all be in group 0001). The list changes at least once a week as we add or remove products.

I want to position one combo box per group on a separate tab that uses for its input range the result of filtering the long list by the applicable grouping code. The value returned would be fed to the order sheet to populate the customer's invoice.

So combo box 1 would allow the clerk to select only from the items that have "0001" as the grouping code, combo box 2 would allow selection from all items with "0002" as the code, etc. In a list of approx 5000 products, there are about 60 groups.

I know this is probably better handled through a database, but you folks seem to be able to do ANYTHING with Excel and VBA. I'm willing to modify my approach if you can steer me in the right direction.

Many thanks!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
So you need about 60 combobox to be populated ?

Can you tell us how you gave the names to each sheet?

Are they named like

0001
0002
0003

etc?
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
just an idea;
probably you can handle it using two comboboxes;

the first combobox will capture all unique group ID ( ie; 0001,0002,etc )
the second combo will capture related values in columnB based on the selection from combo1.

so if the user select 0001 from combo1, the only available selection from combo2 will be; Brakes,Tires,Filters as an example.

is my logic correct.
 

MrHaggis

New Member
Joined
Oct 6, 2006
Messages
18
thanks for the replies.

jindon - the data is all on one sheet called "products" column A has the group numbers, column B has the Product ID, column C has the price. The lookups would be on another tab.

agihcam - yes, the logic is correct. The way I've been doing it is a messy workaround that uses code attached to a button to take the user to the product page and perform a filter based on the group number for that section. To make it even messier, then I have ANOTHER button next to it to take the user to yet another page to select an attribute for the product, based on a list of options for the product, filtered by the product selected.

The product data is all arranged in filterable columns, but the data changes regularly, so I can't name ranges to search from (unless someone can educate me on how to use VBA to name the ranges based on the occurance of the specified group number in the record).

Thanks again.
 

MrHaggis

New Member
Joined
Oct 6, 2006
Messages
18

ADVERTISEMENT

I've found exactly what I need in some previous posts from luc38luc (Sat Apr 09, 2005) pointing users to similar threads. I'm now using data validation based on named ranges. Only problem is I don't know how to cycle through and name ranges automatically.

I need to go through a vertical list with the group name in column A and the lookup value in column B:
Grp Contents
Ref1 Item 1
Ref1 Item 2
Ref2 Item 3
Ref2 Item 4

So Rangename Ref1 would include Item 1 and Item 2, and Ref2 would include Item 3 and Item 4, etc, etc.

The list is about 5000 rows long and includes about 100 possible range names (don't know if this is relevant).

I'm so close I can taste it...just need a macro to assign the rangenames. Any further help would be truly appreciated.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
just try this code
1) right click on the sheet tab that you want to have dropdown lists and select [ViewCode]
2) paste the code on the right pane then close the window
3) click other worksheet once and come back to the sheet.
you shoud be able to select items from cell A1 then corresponding list in B1.
Code:
Private dic As Object

Private Sub Worksheet_Activate()
Dim a, i As Long, txt1 As String, text2 As String, x
   Set dic = CreateObject("Scripting.Dictionary")
   dic.CompareMode = vbTextCompare
   a = Sheets("products").Range("a1").CurrentRegion.Resize(,3).Value
   For i = 1 To UBound(a,1)
      If Not IsEmpty(a(i,1)) Then
         If Not dic.exists(a(i,1)) Then
            dic.add a(i,1), a(i,2) & " : " & a(i,3)
         Else
            dic(a(i,1)) = dic(a(i,1)) & "," & a(i,2) & " : " & a(i,3)
         End If
      End If
   Next
   x = dic.keys
   With .Validation
      .Delete
      .Add Type:=xlValidationList, Formula1 = Join(x,",")
   End With
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1,1)
   If .Address(0,0) <> "A1" Then Exit Sub
   If IsEmpty(.Cells) then Exit Sub
End With
With Range("b1").Validation
   .Delete
   .Add Type:=xlValidation.List Formula1:=dic(Target.Value)
End With
End With
End Sub
 

MrHaggis

New Member
Joined
Oct 6, 2006
Messages
18

ADVERTISEMENT

Hi jindon,

I'm getting an error "variable not defined", highlighting:

x = dic.keys
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

I have just edited the previous code, so can you copy the code again?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try this one
Code:
Private dic As Object

Private Sub Worksheet_Activate()
Dim a, i As Long
   Set dic = CreateObject("Scripting.Dictionary")
   dic.CompareMode = vbTextCompare
   a = Sheets("products").Range("a1").CurrentRegion.Resize(,3).Value
   For i = 1 To UBound(a,1)
      If Not IsEmpty(a(i,1)) Then
         If Not dic.exists(a(i,1)) Then
            dic.add a(i,1), a(i,2) 
         Else
            dic(a(i,1)) = dic(a(i,1)) & "," & a(i,2) 
         End If
      End If
   Next
   With Range("a1").Validation
      .Delete
      .Add Type:=xlValidateList, Formula1 = Join(dic.keys,",")
   End With
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1,1)
   If .Address(0,0) <> "A1" Then Exit Sub
   If IsEmpty(.Cells) then Exit Sub
End With
With Range("b1")
   .Value = Empty
   With .Validation
         .Delete
         .Add Type:=xlValidateList, Formula1:=dic(Target.Value)
   EndWith
   .Select
End With
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,111,597
Messages
5,541,160
Members
410,543
Latest member
ExcelGlenn
Top