Show product selection only if there is stock?

[Chronix99]

Hey guys,

I have to write a bit of code for a small database where I'm using Excel/VBA.

I have the following database of roses that are for sale within a small company:



I have a userform running that looks like this:


I need a piece of code, where when the user selects the type of ROSE - the code will look up the available stock of each CATEGORY of that particular ROSE (i.e Ground Cover, Climber, Standard or Bush)

In the second list box, I need to show only the variety selection if there is stock (i.e Quantity > 0)

So in this instance, when the "PEACE" selection is made, the second list box should only have "BUSH" and "STANDARD"

I have no idea how to get this working.... any help?

I've gotten up to the bit of getting the first listbox and capturing the selection there, but no idea how to use this selection to fill the second list box.

 

[MickG]

Try this:-

Private Sub ListBox1_Click()
Dim Rng As Range
Dim Dn As Range
Dim n As Integer
Set Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
ListBox2.Clear
For Each Dn In Rng
    If Dn = ListBox1.Value Then
        For n = 5 To 7
            If Not Dn(, n) = 0 Then ListBox2.AddItem Cells(Rng(1).Offset(-1).Row, n)
        Next n
    End If
Next Dn
End Sub

Mick

 

[Chronix99]

Hi Mick,

Thank you for your reply. I've had to modify the code slightly because the active worksheet isn't the database-worksheet when this userform is run (it needs to show another worksheet for another purpose.)

So I have;

Private Sub LstBox3_Click()
'Populating the VARIETY Listbox
'Declare Variables
Dim Rng As Range
Dim Dn As Range
Dim n As Integer
'Set Range for existing data for VARIETY
Set Rng = ActiveWorkbook.Worksheets("Roses").Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
LstBox4.Clear
For Each Dn In Rng
If Dn = LstBox3.Value Then
For n = 5 To 7
If Not Dn(, n) = 0 Then LstBox4.AddItem Cells(Rng(1).Offset(-1).row, n)
Next n
End If
Next Dn
End Sub

However, I get an error:
Run-time error '1004':
Application-defined or object-defined error

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] ListBox1_Click()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Roses")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A3"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
Me.ListBox2.Clear
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] dn = Me.ListBox1.Value [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] n = 5 To 7
            [COLOR="Navy"]If[/COLOR] Not dn(, n) = 0 [COLOR="Navy"]Then[/COLOR] Me.ListBox2.AddItem Sheets("Roses").Cells(Rng(1).Offset(-1).Row, n)
        [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Chronix99]

You sir, are brilliant! I've just had to change one line from what you've quoted to "For n = 3 To 6" and it's working perfectly!

This isn't as critical but is it possible to show nothing if no selection has been made? At the moment its showing all four categories if nothing is selected from the 1st box. Thank you so much

 

[MickG]

With my example, when you open the Userform, there is nothing in listbox2.
Whren you click an Item in Listbox1 the Results show in listbox2.
Each time you select an item listbox2 is cleared.
If you have blank rows in the listbox1 and you select a blank space, then Listbox 2 will show all Types.
Is this what you have and trying to overcome, or some other scenario.

 

[Chronix99]

As soon as I open it all four categories are showing:



Nothing is selected yet and there are no blank selections in Listbox1

 

[MickG]

How are you loading the data into listbox1 ???

 

[Chronix99]

Using the UserForm_Activate Sub;

'Set Range for existing data for ROSES

Set ws = Worksheets("Roses")
ilastrow = ws.Range("A65536").End(xlUp).row

For irow = 3 To ilastrow
If ws.Range("A" & irow).Value <> "" Then
With LstBox3
.AddItem ws.Range("A" & irow).Value
End With
End If
Next irow

 

[MickG]

I'm sure I've had something similar in the past, Due to it being a "Change event", but can't emulate it at the moment.
You could try either a "Click event" in The first list box intead of "Change event " or You could add a "Listbox2.Clear" to the "Userform Activate event".