Cascading Combo Box Excel UserForm

[tylindes]

I have a large Excel workbook that I have pared down for testing.

Currently (without an Excel userform) the user selects a store number in column A. In column B there is a VLookup that looks up the store number in column A on an AcctNos sheet and retrieves the Store Name. There is then a validation rule in column C that is a List with the reference of, for example, =Indirect(B2). If B2 contains Store2, the validation list will be restricted to the cells referenced in the range named Store2. (We could not use just the store number, as range names cannot begin with a number.)

This works very well. However, when I try to duplicate the same thing on a userform, after selecting the store number from CboStore, CboAcct doesn't show anything when I click the drop-down arrow and I get the message that the data being looked up wasn't found.

I can do a vlookup in the file and it's fine. Very stumped.

I have attached the file. Thanks so very much!

[agibsonsw]

I think you need to use the Val() function to convert the text in your store combo box to a number as this is what appears in the first column of your sheet AcctNos.
The column offset should be 4 if you want to list the account numbers in the 2nd combo box? However, VLOOKUP will only retrieve the first item found, not the whole list of account numbers.
The following code uses an alternative approach.

Code: Select all

Private Sub CboStore_AfterUpdate()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim x As Variant
    
    CboAcctNo.Clear
    On Error Resume Next
    x = WorksheetFunction.Match(Val(CboStore.Text), Range("StoreNos"), False)
    If Err.Number <> 0 Then
        MsgBox "No data found."
    Else
        Set rng1 = Intersect(Worksheets("AcctNos").UsedRange, Worksheets("AcctNos").Columns(1))
        For Each rng2 In rng1
            If rng2.Value = Val(CboStore.Text) Then
                CboAcctNo.AddItem rng2.Offset(0, 3).Value
            End If
        Next rng2
    End If
    On Error GoTo 0
End Sub

[tylindes]

That worked beautifully - thanks so much!

Here's a glitch, though. Some of the Stores are text - example 12A. For those, it doesn't work as they aren't values.

Any assistance would be greatly appreciated.

[agibsonsw]

Try this version, which uses the VB function ISNUMERIC:

Code: Select all

Private Sub CboStore_AfterUpdate()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim x As Variant
    
    CboAcctNo.Clear
    On Error Resume Next
    If IsNumeric(CboStore.Text) Then
        x = WorksheetFunction.Match(Val(CboStore.Text), Range("StoreNos"), False)
    Else
        x = WorksheetFunction.Match(CboStore.Text, Range("StoreNos"), False)
    End If
    If Err.Number <> 0 Then
        MsgBox "No data found."
    Else
        Set rng1 = Intersect(Worksheets("AcctNos").UsedRange, Worksheets("AcctNos").Columns(1))
        For Each rng2 In rng1
            If rng2.Value = CboStore.Text Then
                CboAcctNo.AddItem rng2.Offset(0, 3).Value
            End If
        Next rng2
    End If
    On Error GoTo 0
End Sub

[tylindes]

That worked perfectly - thanks.

I am now trying to add code that will also populate text boxes on the form. No errors, just doesn't do anything. Have tried to do this with a VLookup because, although I may be wrong, I am under the impression it is faster... tried to copy and revise your code, but obviously I'm not succeeding! :-)

Again, your help is greatly appreciated.

Private Sub CboStore_AfterUpdate()
Dim rng1 As Range
Dim rng2 As Range
Dim x As Variant

'Clear CboAcct and restrict the contents to the accounts related to the selected store
CboAcctNo.Clear
On Error Resume Next
If IsNumeric(CboStore.Text) Then
x = WorksheetFunction.Match(Val(CboStore.Text), Range("StoreNos"), False)
Else
x = WorksheetFunction.Match(CboStore.Text, Range("StoreNos"), False)
End If
If Err.Number <> 0 Then
MsgBox "No data found."
Else
Set rng1 = Intersect(Worksheets("AcctNos").UsedRange, Worksheets("AcctNos").Columns(1))
For Each rng2 In rng1
If rng2.Value = CboStore.Text Then
CboAcctNo.AddItem rng2.Offset(0, 3).Value
End If
Next rng2
End If
On Error GoTo 0

'Clear Address and look up the first address found for selected store
TxtAddress.Text = ""

On Error Resume Next
If IsNumeric(CboStore.Text) Then
x = WorksheetFunction.Match(Val(CboStore.Text), Range("StoreNos"), False)
Else
x = WorksheetFunction.Match(CboStore.Text, Range("StoreNos"), False)
End If
If Err.Number <> 0 Then
MsgBox "No data found."
Else
TxtAddress.Text = Application.VLookup(Me.CboStore.Text, Range("AcctNosData"), 3, 0)

End If
On Error GoTo 0
End Sub

[agibsonsw]

Hello. It reverts to the same issue as previously - when using VLOOKUP we need to distinguish between looking for text or a numerical value:

Code: Select all

If IsNumeric(CboStore.Text) Then
    txtAddress.Text = Application.VLookup(Val(Me.CboStore.Text), Range("AcctNosData"), 3, 0)
Else
    txtAddress.Text = Application.VLookup(Me.CboStore.Text, Range("AcctNosData"), 3, 0)
End If

[tylindes]

Hello,

I thought I had replied to this, but don't see it, so will do so again.

Firstly, thank you so very much - this is all working well for extra controls I have added. I've also added some controls that perform calculations and date pickers.

Now I have one last (I hope) question.

In the worksheet where I will be placing the content of the userform once it's filled it, there are also formulas that are quite complex that are filled down each time data is entered directly on the sheet.

Is there a way that I can simply refer to the column or column number and have it fill down the formula that it sees in the same column in the row above?

Thanks very much!

[agibsonsw]

I'll assume you know the row number that the new data is added to - call it x. Assuming you want to copy the formula down in column J - the tenth column:

cells(x,10).Formula = cells(x-1,10).Formula ' or
cells(x,Range("J1").Column).Formula = cells(x-1,Range("J1").Column).Formula

should achieve this. Andy.

[StuartR]

...Is there a way that I can simply refer to the column or column number and have it fill down the formula that it sees in the same column in the row above?
...

To fill down from Cell C1 to Cell C2 you simply need

Code: Select all

Range("C1").Copy Destination:=Range("C2")

I haven't read all the code in your previous example, but you should be able to use code like this, possibly based on an offset from a known cell.

[tylindes]

Sorry I wasn't very clear, was I? When the user clicks the Add Data button, part of the code is what is below. With that in mind, I imagine an offset from lRow, 1? Thanks very much.

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

With ws
.Cells(lRow, 1).Value = Me.CboStore.Value
'and other control values in a list as in the row above
End With

[StuartR]

...
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

With ws
.Cells(lRow, 1).Value = Me.CboStore.Value
'and other control values in a list as in the row above
End With

You should be able to add something like this...

Code: Select all

dim iCol as integer

'find  first empty row in database
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

With ws
  .Cells(lRow, 1).Value = Me.CboStore.Value
  For iCol = 2 to 10 ' assuming you want to copy columns B to K, modify this as appropriate
    .Cells(lRow-1,iCol).Copy Destination:=.Cells(lRow, iCol) 
  Next iCol
'and other control values in a list as in the row above
End With

[tylindes]

That worked perfectly!

Again, I can't see my response that I posted earlier, but I wanted to thank each of you for your time and your help. It's very appreciated!

Happy New Year!

[HansV]

You should always click the Submit button after writing a reply. If you've clicked Quick Reply, the POST REPLY button in the lower left corner of the thread will remain available, but clicking it will discard the text you've written in the Quick Reply box.

(Welcome to Eileen's Lounge, by the way, and Happy New Year!)