userform question

  • Thread starter Thread starter Axcell
  • Start date Start date
A

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!!
 
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
'========================
 
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
 
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
 
Back
Top