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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top