Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Greetings,

I have a UserForm with 3 ComboBoxes and several TextBoxes. I also
have 2 worksheets called 'Data' and 'List'. 'Data' is the records
database and 'List' is the named ranges storage.

I am attempting to edit the records on 'Data' by using a UserForm
setup to find a certain 'Item Number' from a certain 'Vendor' on a
certain 'Date/Time'.

On sheet 'Data', column A is the 'Vendor' name, column B is the
'Date/Time' entry and column C is the 'Item Number'. These columns
are also named ranges. Column A is 'dVendor', column B is 'dDate' and
column C is 'dNum'

On sheet 'List' is a named range of unique vendors called 'lVendor'

On the UserForm are 3 ComboBoxes: Vendor Name, Date/Time and Item
Number. There are also a few TextBoxes (the exact number is not
relevant).

ComboBox 1 gets it's RowSource from 'lVendor'. ComboBox 2 RowSource
is dependent on the value of ComboBox 1, but not referenced to
'lVendor' (the named range on 'List' with the unique list of names),
but referenced to 'dVendor' (the named range on 'Data'. 'dVendor'
does NOT have a unique names list of names, but many duplicates).

I have got as far as ComboBox 1, but I can't seem to find a way to get
the RowSource's for the next 2 ComboBoxes. The TextBoxes need to be
filled from the results of the three ComboBoxes. I was thinking of
either VLOOKUP or OFFSET finding the row that matches the ComboBoxes
results and getting the correct column for each TextBox.

I have looked at a few of the Data Validation solutions and I can't
figure out how to make any of them work.

I have been searching for answers in the news groups, chasing down web
sites, going though the code that was submitted, for the last 4 days
(there are a lot of solutions to go though, just nothing that I can
use).

Does anyone have any ideas on how to accomplish this?

Any help is appreciated!!!!

Thanks for looking at my post and any help you may be willing to
render.

-Minitman

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hi Minitman,

Look at the techniques used in xlDynamic's Dependent Dropdowns page at:

http://www.xldynamic.com/source/xld.Dropdowns.html


---
Regards,
Norman


"Minitman" wrote in message
...
Greetings,

I have a UserForm with 3 ComboBoxes and several TextBoxes. I also
have 2 worksheets called 'Data' and 'List'. 'Data' is the records
database and 'List' is the named ranges storage.

I am attempting to edit the records on 'Data' by using a UserForm
setup to find a certain 'Item Number' from a certain 'Vendor' on a
certain 'Date/Time'.

On sheet 'Data', column A is the 'Vendor' name, column B is the
'Date/Time' entry and column C is the 'Item Number'. These columns
are also named ranges. Column A is 'dVendor', column B is 'dDate' and
column C is 'dNum'

On sheet 'List' is a named range of unique vendors called 'lVendor'

On the UserForm are 3 ComboBoxes: Vendor Name, Date/Time and Item
Number. There are also a few TextBoxes (the exact number is not
relevant).

ComboBox 1 gets it's RowSource from 'lVendor'. ComboBox 2 RowSource
is dependent on the value of ComboBox 1, but not referenced to
'lVendor' (the named range on 'List' with the unique list of names),
but referenced to 'dVendor' (the named range on 'Data'. 'dVendor'
does NOT have a unique names list of names, but many duplicates).

I have got as far as ComboBox 1, but I can't seem to find a way to get
the RowSource's for the next 2 ComboBoxes. The TextBoxes need to be
filled from the results of the three ComboBoxes. I was thinking of
either VLOOKUP or OFFSET finding the row that matches the ComboBoxes
results and getting the correct column for each TextBox.

I have looked at a few of the Data Validation solutions and I can't
figure out how to make any of them work.

I have been searching for answers in the news groups, chasing down web
sites, going though the code that was submitted, for the last 4 days
(there are a lot of solutions to go though, just nothing that I can
use).

Does anyone have any ideas on how to accomplish this?

Any help is appreciated!!!!

Thanks for looking at my post and any help you may be willing to
render.

-Minitman

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hey Norman,

I've looked at that solution.

The problem with that solution is the design of the secondary lists.
I have my records setup like this:

| Vendor Name | Date | Item Number | Misc 1 | Misc 2|
| store 1 | 3/21/03 | 1 | Fish | Frozen|
| store 1 | 3/21/03 | 2 | Lettuce | Fresh |
| store 1 | 3/21/03 | 3 | Chili | 12 oz |
| store 1 | 3/21/03 | 4 | Mayo | 29 oz |
| store 1 | 3/21/03 | 5 | Coke | 32 oz |
| store 1 | 3/21/03 | 6 | Eggs | 1 doz |
| store 1 | 4/21/03 | 1 | TP | 8 pak |
| store 1 | 4/21/03 | 2 | PT | 3 pak |
| store 1 | 4/21/03 | 3 |Mustard | 29 oz |
| store 1 | 4/21/03 | 4 |Ketsup | 24 oz |
| store 1 | 4/21/03 | 5 |Hot Dogs| 8 pak |
| store 1 | 4/21/03 | 6 |Charcole| 10 lb. |
| store 1 | 4/21/03 | 7 | Buns | 10 pak |
| store 1 | 4/21/03 | 8 | Coke | 24 pak|
| store 1 | 4/21/03 | 9 | Plates | 1 ea. |
| store 2 | 3/21/03 | 1 | Gasoline| 10 gal |
| store 3 | 4/01/03 | 1 | Shovel | 1 ea. |
| store 3 | 4/01/03 | 2 | Rake | 1 ea. |
| store 3 | 4/01/03 | 3 | Fertilizer | 40 lb |
| store 3 | 4/01/03 | 4 | Petunias | 1 doz |
| store 3 | 4/01/03 | 5 | Dirt | 60 lb |

.... And a lot more of the same.

What I need is to choose the vendor (store name) from ComboBox 1 and
have the dates for only that store show up in ComboBox 2 without
making any additional lists. And once there is a date then have the
item numbers for only the store on that date show up in ComboBox 3.
When there is an entry in ComboBox 3 then the rest of that record will
show up in the appropriate TextBox (thus the VLOOKUP or OFFSET).

I hope this clarifies the problem.

-Minitman



On Tue, 4 Jul 2006 23:17:57 +0100, "Norman Jones"
wrote:

Hi Minitman,

Look at the techniques used in xlDynamic's Dependent Dropdowns page at:

http://www.xldynamic.com/source/xld.Dropdowns.html


---
Regards,
Norman


"Minitman" wrote in message
.. .
Greetings,

I have a UserForm with 3 ComboBoxes and several TextBoxes. I also
have 2 worksheets called 'Data' and 'List'. 'Data' is the records
database and 'List' is the named ranges storage.

I am attempting to edit the records on 'Data' by using a UserForm
setup to find a certain 'Item Number' from a certain 'Vendor' on a
certain 'Date/Time'.

On sheet 'Data', column A is the 'Vendor' name, column B is the
'Date/Time' entry and column C is the 'Item Number'. These columns
are also named ranges. Column A is 'dVendor', column B is 'dDate' and
column C is 'dNum'

On sheet 'List' is a named range of unique vendors called 'lVendor'

On the UserForm are 3 ComboBoxes: Vendor Name, Date/Time and Item
Number. There are also a few TextBoxes (the exact number is not
relevant).

ComboBox 1 gets it's RowSource from 'lVendor'. ComboBox 2 RowSource
is dependent on the value of ComboBox 1, but not referenced to
'lVendor' (the named range on 'List' with the unique list of names),
but referenced to 'dVendor' (the named range on 'Data'. 'dVendor'
does NOT have a unique names list of names, but many duplicates).

I have got as far as ComboBox 1, but I can't seem to find a way to get
the RowSource's for the next 2 ComboBoxes. The TextBoxes need to be
filled from the results of the three ComboBoxes. I was thinking of
either VLOOKUP or OFFSET finding the row that matches the ComboBoxes
results and getting the correct column for each TextBox.

I have looked at a few of the Data Validation solutions and I can't
figure out how to make any of them work.

I have been searching for answers in the news groups, chasing down web
sites, going though the code that was submitted, for the last 4 days
(there are a lot of solutions to go though, just nothing that I can
use).

Does anyone have any ideas on how to accomplish this?

Any help is appreciated!!!!

Thanks for looking at my post and any help you may be willing to
render.

-Minitman

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Minitman,
Try the below codes
It works for me, thanks to Mike.

I think it is what you need also.
CHANGE the ComboBox numbers etc to suit.

Corey....



Private Sub UserForm_Activate()
Dim i As Integer, j As Integer
Dim addit As Boolean, addit2 As Boolean
ComboBox3.Clear
For Each wks In Worksheets
addit = True
For i = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListCount = 0 Then Exit For
If wks.Range("B3").Text = ComboBox3.List(i) Then addit = False Next i
If addit Then ComboBox3.AddItem wks.Range("B3").Text
Next wks
End Sub

Private Sub ComboBox3_Change() ComboBox4.Clear For Each wks In Worksheets
If wks.Range("b3").Text = ComboBox3.Value Then addit2 = True
For j = 0 To ComboBox4.ListCount - 1
If ComboBox4.ListCount = 0 Then Exit For
If wks.Range("D3").Text = ComboBox4.List(j) Then addit2 = False
Next j
If addit2 Then ComboBox4.AddItem wks.Range("D3").Text End If
Next wks
End Sub

Private Sub Combobox4_Change() Dim combolist
If ComboBox4.ListCount = 0 Then Exit Sub
For Each wks In Worksheets
If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
ComboBox4.Value Then combolist = combolist & wks.Name & Chr(10)
Next wks
' MsgBox (combolist)
End Sub

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hey Corey,

Thanks for the reply.

I can't seem to find any points of reference between what I am looking
for and your code. I'm not even sure what your code is doing.

As I mentioned earlier, I have 3 ComboBoxes (CB1, CB2 & CB3) and 2
TextBoxes (TB5 & TB6). These are on a UserForm.

In the workbook are two sheets (Enter & Data)I start the UserForm from
Enter and sore the imputed data on Data. On the UserForm, I am trying
to find which row on the data sheet matches up with a set of criteria
containing three items (Vendor, Date/Time & Item number) and move over
to columns E & F to pick up misc 1 & misc 3 information and place this
info into TB5 & TB6..

I have a sample workbook, which shows the problem, that I can email or
FTP to anyone. It is about 63 K after zipping.

Thanks again for your reply.

-Minitman


On Fri, 7 Jul 2006 08:37:47 +1000, "Corey"
wrote:

Minitman,
Try the below codes
It works for me, thanks to Mike.

I think it is what you need also.
CHANGE the ComboBox numbers etc to suit.

Corey....



Private Sub UserForm_Activate()
Dim i As Integer, j As Integer
Dim addit As Boolean, addit2 As Boolean
ComboBox3.Clear
For Each wks In Worksheets
addit = True
For i = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListCount = 0 Then Exit For
If wks.Range("B3").Text = ComboBox3.List(i) Then addit = False Next i
If addit Then ComboBox3.AddItem wks.Range("B3").Text
Next wks
End Sub

Private Sub ComboBox3_Change() ComboBox4.Clear For Each wks In Worksheets
If wks.Range("b3").Text = ComboBox3.Value Then addit2 = True
For j = 0 To ComboBox4.ListCount - 1
If ComboBox4.ListCount = 0 Then Exit For
If wks.Range("D3").Text = ComboBox4.List(j) Then addit2 = False
Next j
If addit2 Then ComboBox4.AddItem wks.Range("D3").Text End If
Next wks
End Sub

Private Sub Combobox4_Change() Dim combolist
If ComboBox4.ListCount = 0 Then Exit Sub
For Each wks In Worksheets
If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
ComboBox4.Value Then combolist = combolist & wks.Name & Chr(10)
Next wks
' MsgBox (combolist)
End Sub

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

The code worked by If:
combobox 1 had a selection,
then ONLY related to that selection, combobox 2 listed values would be
displayed.

Corey....
"Minitman" wrote in message
...
Hey Corey,

Thanks for the reply.

I can't seem to find any points of reference between what I am looking
for and your code. I'm not even sure what your code is doing.

As I mentioned earlier, I have 3 ComboBoxes (CB1, CB2 & CB3) and 2
TextBoxes (TB5 & TB6). These are on a UserForm.

In the workbook are two sheets (Enter & Data)I start the UserForm from
Enter and sore the imputed data on Data. On the UserForm, I am trying
to find which row on the data sheet matches up with a set of criteria
containing three items (Vendor, Date/Time & Item number) and move over
to columns E & F to pick up misc 1 & misc 3 information and place this
info into TB5 & TB6..

I have a sample workbook, which shows the problem, that I can email or
FTP to anyone. It is about 63 K after zipping.

Thanks again for your reply.

-Minitman


On Fri, 7 Jul 2006 08:37:47 +1000, "Corey"
wrote:

Minitman,
Try the below codes
It works for me, thanks to Mike.

I think it is what you need also.
CHANGE the ComboBox numbers etc to suit.

Corey....



Private Sub UserForm_Activate()
Dim i As Integer, j As Integer
Dim addit As Boolean, addit2 As Boolean
ComboBox3.Clear
For Each wks In Worksheets
addit = True
For i = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListCount = 0 Then Exit For
If wks.Range("B3").Text = ComboBox3.List(i) Then addit = False Next i
If addit Then ComboBox3.AddItem wks.Range("B3").Text
Next wks
End Sub

Private Sub ComboBox3_Change() ComboBox4.Clear For Each wks In Worksheets
If wks.Range("b3").Text = ComboBox3.Value Then addit2 = True
For j = 0 To ComboBox4.ListCount - 1
If ComboBox4.ListCount = 0 Then Exit For
If wks.Range("D3").Text = ComboBox4.List(j) Then addit2 = False
Next j
If addit2 Then ComboBox4.AddItem wks.Range("D3").Text End If
Next wks
End Sub

Private Sub Combobox4_Change() Dim combolist
If ComboBox4.ListCount = 0 Then Exit Sub
For Each wks In Worksheets
If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
ComboBox4.Value Then combolist = combolist & wks.Name & Chr(10)
Next wks
' MsgBox (combolist)
End Sub

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hey Corey,

I really appreciate your help with this problem.

I re tabbed your code and I am not sure if I got it right, could you
check it for errors? Thanks.

I do have a couple of questions.

1) What is "wks"? It doesn't seem to be defined.
2) What do Range("B3") and Range("D3) represent?
3)What is the advantage in using UserForm_Activate over
UserForm_Initialize?
4)What does "combolist" represent?

I am assuming that ComboBox3 = the first ComboBox and ComboBox 4 = the
second ComboBox. I'm not sure where my 3rd ComboBox fits in here or
my 2 TextBoxes.

I hope to here from you soon.

-Minitman




On Fri, 7 Jul 2006 08:37:47 +1000, "Corey"
wrote:

Minitman,
Try the below codes
It works for me, thanks to Mike.

I think it is what you need also.
CHANGE the ComboBox numbers etc to suit.

Corey....



Private Sub UserForm_Activate()
Dim i As Integer, j As Integer
Dim addit As Boolean, addit2 As Boolean
ComboBox3.Clear
For Each wks In Worksheets
addit = True
For i = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListCount = 0 Then Exit For
If wks.Range("B3").Text = ComboBox3.List(i) Then
addit = False
End If
Next i
If addit Then ComboBox3.AddItem wks.Range("B3").Text
Next wks
End Sub

Private Sub ComboBox3_Change()
ComboBox4.Clear
For Each wks In Worksheets
If wks.Range("B3").Text = ComboBox3.Value Then addit2 = True
For j = 0 To ComboBox4.ListCount - 1
If ComboBox4.ListCount = 0 Then Exit For
If wks.Range("D3").Text = ComboBox4.List(j) Then
addit2 = False
End If
Next j
If addit2 Then
ComboBox4.AddItem
wks.Range("D3").Text
End If
Next wks
End Sub

Private Sub Combobox4_Change()
Dim combolist
If ComboBox4.ListCount = 0 Then Exit Sub
For Each wks In Worksheets
If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
ComboBox4.Value Then
combolist = combolist & wks.Name & Chr(10)
End If
Next wks
End Sub

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

RE Below:

"Minitman" wrote in message
...
Hey Corey,

I really appreciate your help with this problem.

I re tabbed your code and I am not sure if I got it right, could you
check it for errors? Thanks.

I do have a couple of questions.

1) What is "wks"? It doesn't seem to be defined. <=======
wks=WorkSheets
2) What do Range("B3") and Range("D3) represent? <===== B3 is the range
where the combo box(x) will populate its list from, D3 is the list fro
combo box (y) (Number to suit yourself)
3)What is the advantage in using UserForm_Activate over
UserForm_Initialize? <====================== Don't know, but worked
for me
4)What does "combolist" represent? <============== The list of data that
is diplayed in the combo box

I am assuming that ComboBox3 = the first ComboBox and ComboBox 4 = the
second ComboBox. I'm not sure where my 3rd ComboBox fits in here or
my 2 TextBoxes. <======================== Change the numbers of the combo
boxes to suit your application. If you have 2 bombo boxes in yuor file
then name them ComboBox 1 & 2

I hope to here from you soon.

-Minitman




On Fri, 7 Jul 2006 08:37:47 +1000, "Corey"
wrote:

Minitman,
Try the below codes
It works for me, thanks to Mike.

I think it is what you need also.
CHANGE the ComboBox numbers etc to suit.

Corey....



Private Sub UserForm_Activate()
Dim i As Integer, j As Integer
Dim addit As Boolean, addit2 As Boolean
ComboBox3.Clear
For Each wks In Worksheets
addit = True
For i = 0 To ComboBox3.ListCount - 1
If ComboBox3.ListCount = 0 Then Exit For
If wks.Range("B3").Text = ComboBox3.List(i) Then
addit = False
End If
Next i
If addit Then ComboBox3.AddItem wks.Range("B3").Text
Next wks
End Sub

Private Sub ComboBox3_Change()
ComboBox4.Clear
For Each wks In Worksheets
If wks.Range("B3").Text = ComboBox3.Value Then addit2 = True
For j = 0 To ComboBox4.ListCount - 1
If ComboBox4.ListCount = 0 Then Exit For
If wks.Range("D3").Text = ComboBox4.List(j) Then
addit2 = False
End If
Next j
If addit2 Then
ComboBox4.AddItem
wks.Range("D3").Text
End If
Next wks
End Sub

Private Sub Combobox4_Change()
Dim combolist
If ComboBox4.ListCount = 0 Then Exit Sub
For Each wks In Worksheets
If wks.Range("B3") = ComboBox3.Value And wks.Range("D3") = _
ComboBox4.Value Then
combolist = combolist & wks.Name & Chr(10)
End If
Next wks
End Sub

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hi Minitman,

In the Userform module try something like:

Hi Minitman,

Try somrthing like:

'=============
Option Explicit

Private Sub ComboBox1_Change()
Call LoadCB2(Me.ComboBox1.Value)
End Sub

'-------------

Private Sub ComboBox2_Change()
Call LoadCB3(Me.ComboBox1.Value, Me.ComboBox2.Value)
End Sub

'-------------

Sub LoadCB2(sVendor As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim iRow As Long
Dim CB2 As ComboBox

Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set CB2 = Me.ComboBox2

CB2.Clear

For iRow = 2 To Rng1.Rows.Count
If Rng1(iRow).Value = sVendor Then
CB2.AddItem Rng2(iRow).Value
End If
Next iRow

CB2.ListIndex = 0
End Sub

'-------------

Sub LoadCB3(sVendor As String, sdate As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim iRow As Long
Dim CB3 As ComboBox

Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set Rng3 = Sh.Range("dNum")
Set CB3 = Me.ComboBox3

CB3.Clear

For iRow = 2 To Rng1.Rows.Count
If Rng2(iRow).Value = sdate _
And Rng1(iRow).Value = sVendor Then
CB3.AddItem Rng3(iRow).Value
End If
Next iRow

CB3.ListIndex = 0
End Sub
'<<=============


---
Regards,
Norman


"Minitman" wrote in message
...
Hey Norman,

I've looked at that solution.

The problem with that solution is the design of the secondary lists.
I have my records setup like this:

| Vendor Name | Date | Item Number | Misc 1 | Misc 2|
| store 1 | 3/21/03 | 1 | Fish
| Frozen|
| store 1 | 3/21/03 | 2 | Lettuce
| Fresh |
| store 1 | 3/21/03 | 3 | Chili
| 12 oz |
| store 1 | 3/21/03 | 4 | Mayo
| 29 oz |
| store 1 | 3/21/03 | 5 | Coke
| 32 oz |
| store 1 | 3/21/03 | 6 | Eggs
| 1 doz |
| store 1 | 4/21/03 | 1 | TP
| 8 pak |
| store 1 | 4/21/03 | 2 | PT
| 3 pak |
| store 1 | 4/21/03 | 3 |Mustard
| 29 oz |
| store 1 | 4/21/03 | 4 |Ketsup
| 24 oz |
| store 1 | 4/21/03 | 5 |Hot Dogs|
8 pak |
| store 1 | 4/21/03 | 6 |Charcole|
10 lb. |
| store 1 | 4/21/03 | 7 | Buns
| 10 pak |
| store 1 | 4/21/03 | 8 | Coke
| 24 pak|
| store 1 | 4/21/03 | 9 | Plates
| 1 ea. |
| store 2 | 3/21/03 | 1 |
Gasoline| 10 gal |
| store 3 | 4/01/03 | 1 | Shovel
| 1 ea. |
| store 3 | 4/01/03 | 2 | Rake
| 1 ea. |
| store 3 | 4/01/03 | 3 |
Fertilizer | 40 lb |
| store 3 | 4/01/03 | 4 | Petunias
| 1 doz |
| store 3 | 4/01/03 | 5 | Dirt
| 60 lb |

... And a lot more of the same.

What I need is to choose the vendor (store name) from ComboBox 1 and
have the dates for only that store show up in ComboBox 2 without
making any additional lists. And once there is a date then have the
item numbers for only the store on that date show up in ComboBox 3.
When there is an entry in ComboBox 3 then the rest of that record will
show up in the appropriate TextBox (thus the VLOOKUP or OFFSET).

I hope this clarifies the problem.

-Minitman



On Tue, 4 Jul 2006 23:17:57 +0100, "Norman Jones"
wrote:

Hi Minitman,

Look at the techniques used in xlDynamic's Dependent Dropdowns page at:

http://www.xldynamic.com/source/xld.Dropdowns.html


---
Regards,
Norman


"Minitman" wrote in message
. ..
Greetings,

I have a UserForm with 3 ComboBoxes and several TextBoxes. I also
have 2 worksheets called 'Data' and 'List'. 'Data' is the records
database and 'List' is the named ranges storage.

I am attempting to edit the records on 'Data' by using a UserForm
setup to find a certain 'Item Number' from a certain 'Vendor' on a
certain 'Date/Time'.

On sheet 'Data', column A is the 'Vendor' name, column B is the
'Date/Time' entry and column C is the 'Item Number'. These columns
are also named ranges. Column A is 'dVendor', column B is 'dDate' and
column C is 'dNum'

On sheet 'List' is a named range of unique vendors called 'lVendor'

On the UserForm are 3 ComboBoxes: Vendor Name, Date/Time and Item
Number. There are also a few TextBoxes (the exact number is not
relevant).

ComboBox 1 gets it's RowSource from 'lVendor'. ComboBox 2 RowSource
is dependent on the value of ComboBox 1, but not referenced to
'lVendor' (the named range on 'List' with the unique list of names),
but referenced to 'dVendor' (the named range on 'Data'. 'dVendor'
does NOT have a unique names list of names, but many duplicates).

I have got as far as ComboBox 1, but I can't seem to find a way to get
the RowSource's for the next 2 ComboBoxes. The TextBoxes need to be
filled from the results of the three ComboBoxes. I was thinking of
either VLOOKUP or OFFSET finding the row that matches the ComboBoxes
results and getting the correct column for each TextBox.

I have looked at a few of the Data Validation solutions and I can't
figure out how to make any of them work.

I have been searching for answers in the news groups, chasing down web
sites, going though the code that was submitted, for the last 4 days
(there are a lot of solutions to go though, just nothing that I can
use).

Does anyone have any ideas on how to accomplish this?

Any help is appreciated!!!!

Thanks for looking at my post and any help you may be willing to
render.

-Minitman

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hi Minitman,

Try replacing my suggested code with the following:

'=============
Option Explicit
Public blStopEvents As Boolean

'-------------

Private Sub ComboBox1_Change()
Call LoadCB2(Me.ComboBox1.Value)
End Sub

'-------------

Private Sub ComboBox2_Change()
If blStopEvents Then Exit Sub
Call LoadCB3(Me.ComboBox1.Value, Me.ComboBox2.Value)
End Sub

'-------------

Sub LoadCB2(sVendor As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim iRow As Long
Dim CB1 As ComboBox
Dim CB2 As ComboBox
Dim CB3 As ComboBox
Dim blLoaded As Boolean

If blStopEvents Then Exit Sub

Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set CB1 = Me.ComboBox1
Set CB2 = Me.ComboBox2
Set CB3 = Me.ComboBox3
blStopEvents = True
CB2.Clear
For iRow = 2 To Rng1.Rows.Count
If Rng1(iRow).Value = sVendor Then
CB2.AddItem Rng2(iRow).Value
End If
Next iRow

CB2.ListIndex = 0
blStopEvents = False
Call LoadCB3(CB1.Value, CB2.Value)

End Sub

'-------------

Sub LoadCB3(sVendor As String, sdate As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim iRow As Long
Dim CB3 As ComboBox
Dim blLoaded As Boolean

If blStopEvents Then Exit Sub

Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set Rng3 = Sh.Range("dNum")
Set CB3 = Me.ComboBox3

CB3.Clear

For iRow = 2 To Rng1.Rows.Count
If Rng2(iRow).Value = sdate _
And Rng1(iRow).Value = sVendor Then
CB3.AddItem Rng3(iRow).Value
End If
Next iRow
blStopEvents = True

CB3.ListIndex = 0
blStopEvents = False

End Sub
'<<=============


--
Regards,
Norman

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hey Norman,

Thanks for taking the time to help me, I really appreciate it.

Vba is not liking the line: CB2.Clear

It is calling it an "unspecified error" (whatever that is).

I don't understand all of your short cuts, which makes it a bit hard
to follow the logic. for example: "Set CB2 = Me.ComboBox2" looks like
all the code is doing is changing the name of ComboBox2 or am I
missing something.

Would it be easier if I sent you a sample workbook of what I am trying
to do? (I don't think I can send it to the newsgroup)

Please let me know, thanks.

-Minitman

On Sun, 9 Jul 2006 23:58:00 +0100, "Norman Jones"
wrote:

Hi Minitman,

Try replacing my suggested code with the following:

'=============
Option Explicit
Public blStopEvents As Boolean

'-------------

Private Sub ComboBox1_Change()
Call LoadCB2(Me.ComboBox1.Value)
End Sub

'-------------

Private Sub ComboBox2_Change()
If blStopEvents Then Exit Sub
Call LoadCB3(Me.ComboBox1.Value, Me.ComboBox2.Value)
End Sub

'-------------

Sub LoadCB2(sVendor As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim iRow As Long
Dim CB1 As ComboBox
Dim CB2 As ComboBox
Dim CB3 As ComboBox
Dim blLoaded As Boolean

If blStopEvents Then Exit Sub

Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set CB1 = Me.ComboBox1
Set CB2 = Me.ComboBox2
Set CB3 = Me.ComboBox3
blStopEvents = True
CB2.Clear
For iRow = 2 To Rng1.Rows.Count
If Rng1(iRow).Value = sVendor Then
CB2.AddItem Rng2(iRow).Value
End If
Next iRow

CB2.ListIndex = 0
blStopEvents = False
Call LoadCB3(CB1.Value, CB2.Value)

End Sub

'-------------

Sub LoadCB3(sVendor As String, sdate As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim iRow As Long
Dim CB3 As ComboBox
Dim blLoaded As Boolean

If blStopEvents Then Exit Sub

Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set Rng3 = Sh.Range("dNum")
Set CB3 = Me.ComboBox3

CB3.Clear

For iRow = 2 To Rng1.Rows.Count
If Rng2(iRow).Value = sdate _
And Rng1(iRow).Value = sVendor Then
CB3.AddItem Rng3(iRow).Value
End If
Next iRow
blStopEvents = True

CB3.ListIndex = 0
blStopEvents = False

End Sub
'<<=============

Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
 

Hi Minitman,

Vba is not liking the line: CB2.Clear

Is your second ComboBox named ComboBox2?

If not, change the line:

Set CB2 = Me.ComboBox2

to reflect your changed name.

I don't understand all of your short cuts, which makes it a bit hard
to follow the logic. for example: "Set CB2 = Me.ComboBox2" looks like
all the code is doing is changing the name of ComboBox2 or am I
missing something.

I use the object variable CB2 to refer to the second ComboBox, This does not
rename the control but, for me, it is easier, and shorter, to refer to CB2
rather than , potentially repeatedly, using the full name. You can clearly
choose to do either.

Would it be easier if I sent you a sample workbook of what I am trying
to do? (I don't think I can send it to the newsgroup)

Rather than that, I will send you my test workbook in response to an email
to::

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )



---
Regards,
Norman