userform question

[Axcell]

Hello, I am a novice to VBA and Userforms and I'm stuck. Please help?

I have a userform which populates an annual 'orders worksheet' using
VBA with date, customer, product, and location.

Using named ranges from 'customersdb' worksheet I am able to fill the
sources for each combobox on form except the locations.

Some customers (col B) may have one or up to six locations (Cols C:H).
These locations listed on customersdb WS are in multiple columns but
are in the same row associated with the customer.

Col ___ B_________ C _______ D _______
E _______ F_______
Row Jim Smith Battery Park South Central Train Stop Main Street

How can I fill the 'location' combobox2 to just list the locations for
the customer chosen in the 'customer' combobox1 while still in
userform?

Any help would be greatly appreciated, thanks!!

 

[Debra Dalgleish]

If the customer combobox is named cboCust and the location combobox is
named cboLoc:

'========================
Private Sub cboCust_Change()
Dim ws As Worksheet
Dim c As Range
Dim rng As Range
Dim rngLoc As Range
Dim r As Long
Dim col As Integer
Set ws = Sheets("Customersdb")
Set rng = ws.Range("CustList")
r = Application.WorksheetFunction _
.Match(cboCust.Value, rng, 0) + 1
col = Application.WorksheetFunction _
.CountA(ws.Range("C" & r & ":H" & r))
Set rngLoc = ws.Range("C" & r) _
.Offset(0, 0).Resize(1, col)
cboLoc.Clear
For Each c In rngLoc
cboLoc.AddItem c.Value
Next c
cboLoc.ListRows = col
End Sub
'========================

 

[Axcell]

Debra,

Thanks so much, that was it!!

It works great!

 

[Axcell]

Debra -

Oops....the code works fine for filling the location combobox but upo
executing the 'OK' command button to populate the worksheet with th
userform values I get:

"Run Time Error 1004":

"Unable to get the Match Property of the Worksheetfunction class"

Here is your code edited to meet my userform.

Private Sub customer_Change()
Dim ws As Worksheet
Dim c As Range
Dim rng As Range
Dim rngLoc As Range
Dim r As Long
Dim col As Integer
Set ws = Sheets("Customersdb")
' List of customers
Set rng = ws.Range("B6:B3000")
r = Application.WorksheetFunction _ <------ Match property error.
.Match(customer.Value, rng, 0) + 5 < ------
col = Application.WorksheetFunction _
' Customer location columns G through L
.CountA(ws.Range("G" & r & ":L" & r))
Set rngLoc = ws.Range("G" & r) _
.Offset(0, 0).Resize(1, col)
location.Clear
For Each c In rngLoc
location.AddItem c.Value
Next c
location.ListRows = col
End Sub

Anything jump out at you

 

[Debra Dalgleish]

Ar you clearing the customer combobox as part of the OK button code? If
so, you could add a few lines to the customer_Change code, to check for
an empty string:

Private Sub customer_Change()
Dim ws As Worksheet
Dim c As Range
Dim rng As Range
Dim rngLoc As Range
Dim r As Long
Dim col As Integer
If customer = "" Then
Exit Sub
Else
Set ws = Sheets("Customersdb")
' List of customers
Set rng = ws.Range("B6:B3000")
r = Application.WorksheetFunction _
.Match(customer.Value, rng, 0) + 5 'starts with period
' Customer location columns G through L
col = Application.WorksheetFunction _
.CountA(ws.Range("G" & r & ":L" & r))
Set rngLoc = ws.Range("G" & r) _
.Offset(0, 0).Resize(1, col) 'starts with period
location.Clear
For Each c In rngLoc
location.AddItem c.Value
Next c
location.ListRows = col
End If
End Sub

 

[Axcell]

Yes I do clear, that was it. Once again thank you very much.

Great Job!