userform combox based on previous selection

[gringo287]

**following on from "if its not broke, recreate it with userforms"**

After many searches, i cant quite find what i need, without having to start from scratch.

The title pretty much explains it. Ive been addapting the code from http://contextures.com/xlUserForm02.html to suit my needs, but im stuck with how to set up the combo box selection so that its dictated by the previous selection. hopefully the below code should show you my additions and where im going wrong/whats missing.


Private Sub userform_initialize()
Dim cPartPost As Range
Dim cPartPre As Range 'my addition
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cPartPost In ws.Range("PostIDList")
With Me.cboprod
.AddItem cPartPost.Value
.List(.ListCount - 1, 1) = cPartPost.Offset(0, 1).Value
End With
Next cPartPost
'my addition
For Each cPartPre In ws.Range("PreIDlist")
With Me.cboprod
.AddItem cPartPre.Value
.List(.ListCount - 1, 1) = cPartPre.Offset(0, 1).Value
End With
Next cPartPre
For Each cLoc In ws.Range("LocationList")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc
Me.cboprod.Value = ("Pm")
Me.cboLocation = ("Post pay")
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtAdv.Value = Sheets("Tracker").Range("A1")
Me.cboprod.SetFocus
End Sub

[Tinbendr]

hopefully the below code should show you my additions and where im going wrong/whats missing.Since you didn't comment your code, or tell us what you expect, we can't help you.

I d/l the file, but can't figure out what you expect 'the previous selection' to do.

[mikerickson]

"based on previous selection"
Does that mean that you want the user to
1) invoke the userform
2) Select a product from the drop down
3) press the Close button
4) re-invoke the userform, which will appear with that previously selected product already selected?

One way to do that would to change

Private Sub cmdClose_Click()
Me.Hide
End Sub

[gringo287]

====tail between legs:doh: ===


Since you didn't comment your code, or tell us what you expect, we can't help you.

I d/l the file, but can't figure out what you expect 'the previous selection' to do.

Ok, after reading again, i clearly didnt actually state what exactly i wanted. apologies.

ill bullet point :-


Im "attempting" to Re-design a tracker that i set up a few months ago, but this time using a userform.
the original tracker consisted of spin buttons and some formulas etc, but very little vba. The vba that i used to create email buttons and a few other bits, were liberated from google.
I have a location (department) combobox and Product combobox
My aim is for the advisor to be able to select the location, which in turn, will dictate which list of products are populated in the product combobox. I have set up the dynamic named ranges for the extra depts, but honestly dont really know have to adapt the original code that only lists one list of products with a few depts.
i do have a basic understanding of vba, but i have only been tinkering with it for a short time.

[mikerickson]

In the attached, I put a space in the LocationList so that "Post Pay" matched the entries on sheet RawData.

Then changing this event code made the Product combobox dependent on the Location dropdown.


Private Sub cboLocation_Change()
Dim rngLocationData As Range
Dim oneCell As Range
Dim chosenLocation As String
chosenLocation = Me.cboLocation.Text
If chosenLocation <> vbNullString Then
With Sheet1.Range("C:C")
Set rngLocationData = Range(.Cells(4, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Me.cboProd
.Clear
For Each oneCell In rngLocationData
If LCase(CStr(oneCell.Value)) = LCase(chosenLocation) Then
.AddItem CStr(oneCell.EntireRow.Range("A1").Value)
.List(.ListCount - 1, 1) = CStr(oneCell.EntireRow.Range("b1").Value)
End If
Next oneCell
End With
End If
End Sub

Private Sub Userform_Initialize()
Dim cPartPost As Range
Dim cPartPre As Range 'my addition
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cLoc In ws.Range("LocationList")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc
Me.cboLocation = ("Post pay")
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtAdv.Value = Sheets("Tracker").Range("A1")
Me.cboProd.SetFocus
End Sub

[gringo287]

Hi mikerickson, thank you for your time on this. I cant tell if im doing something wrong, but the product and location comboboxes now just seem to be picking up the data from the "Raw Data" sheet?.

My initial thought with the tracker was that there would be more than 3-4 different departments that would be needing this, but it turns out that there will only be 2 and therefore only 2 productlist. What i think would be a more userfriendly method would be to have 2 option buttons, so that the option button selection would dictate the product list shown in the product combo box. Ideally what i think would work the best would be someway of allowing the user to click the option button for their department when they start their shift and that the userform would then "remember" that selection and therefore eliminating one extra button click and reducing the marging for user error.

Ive done a few searches, but cant quite find out if this is even possible. :think:

[Tinbendr]

What i think would be a more userfriendly method would be to have 2 option buttons, so that the option button selection would dictate the product list shown in the product combo box.I think Mike's solution is better because if you need to add more depts down the road, you won't have to mod the code. (If your determined to use option buttons, it can be done.)

... and that the userform would then "remember" that selection and therefore eliminating one extra button click and reducing the marging for user error. Userform selections aren't stored, but you CAN store the listindex using Document variables.

Before you unload the form.


Application.variables("cboLocation") = frmPartLoc.cboLocation.ListIndex

and in the Initialize Sub, the reverse.

[gringo287]

Hi Tinbendr,

Thanks for the response.

Mikes method more than likely would work the best, but my lack of experience is letting me down with making his alterations work. the product combo box is referencing from the "Raw Data" sheet?, instead of the "LookUpLists". The decision has sort of been decided for me by pesky management about the option button method over the location combo box. Their happy that there will only be two department product lists needed, so a simple one click option is preferred at the moment. Ive give it my best shot at working out the code for this, but im getting "" object variable or with block variable not set ""


Private Sub userform_initialize()
Dim cPartPost As Range
Dim cPartPre As Range
Dim cOptPost As Range
Dim cOptPre As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cPartPost In ws.Range("PostIDList")
With Me.cboprod
.AddItem cPartPost.Value
.List(.ListCount - 1, 1) = cPartPost.Offset(0, 1).Value
End With
Next cPartPost
For Each cPartPre In ws.Range("PreIDlist")
With Me.cboprod
.AddItem cPartPre.Value
.List(.ListCount - 1, 1) = cPartPost.Offset(0, 1).Value
End With
Next cPartPre
For Each cOptPost In ws.Range("PostIDlist")
With Me.OpBtn_post
.AddItem cOptPost.Value
End With
Next cOptPost
For Each cOptPre In ws.Range("PreIDlist")
With Me.OpBtn_pre
.AddItem cOptPre.Value
End With
Next cOptPre
Me.cboprod.Value = ""
Me.OpBtn_post = ("Post pay")
Me.OpBtn_pre = ("Pre pay")
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtAdv.Value = Sheets("Tracker").Range("A1")
Me.cboprod.SetFocus
End Sub

[gringo287]

I have looked into this error and im aware that it is to do with an invalid variable is being referenced. Although i understand what this means, i dont know how to resolve it.

[Tinbendr]

OK, I’m still having a little trouble following the code, but…

The error – You can’t refer to cPartPost after the For Each-Next ends. You'll have to assign it to a variable or hard code it, etc.

Also, I wouldn’t assign the combo boxes in the initialize event since as soon as you click an option button, the list will change. Instead, move the code that fills each combo box in the optionbutton Click event.

In the Initialize event, pick one of the option buttons as a default.


Me.OpBtn_post = True
(BTW, you can’t assign anything but a Boolean to option buttons.) When this line is run in the code, the event fires and the default combo box list will fill. So when the users first start the userform, the combo box will have a default selection ready to choose from.

I’d put the option buttons in a frame, too. (Esthetics mostly.)

So, just a quick recap. Each time the user clicks an optionbutton, the click event will fire and refill the combobox on the fly. Oh, be sure to add Optionbutton.Clear at the start of the Click event to clear the list out, else you’ll just keep adding to it.

Hope this helps.

[gringo287]

ok, so, Im clearly out of my depth here. :bug::bug:

The fog is clearing, as the advise you gave, does make sense, but being completely honest, i dont know how to put your suggestions in to practice.

Im not going to give up and im not here to get someone to just do the work for me, but i would be truly grateful for just a little more guidence.

There are actually a number of other pieces to this tracker, so im pretty much stuck at the first hurdle. Im currently having a search for the best vba guides to buy, so i can super glue them to my hands, to help me with the rest of this project, but i just need a push past this point for now, if possible.

below is the full userform code, minus the spin buttons. :help


Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Raw Data")

'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboprod.ListIndex

'check for a part number
If Trim(Me.cboprod.Value) = "" Then
Me.cboprod.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboprod.Value
.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)
.Cells(lRow, 3).Value = Me.OpBtn_post.Value
.Cells(lRow, 4).Value = Me.OpBtn_pre.Value
.Cells(lRow, 5).Value = Me.txtDate.Value
.Cells(lRow, 6).Value = Me.txtQty.Value
.Cells(lRow, 7).Value = Me.txtAdv.Value
.Cells(lRow, 8).Value = Me.CallsTaken.Value
.Cells(lRow, 9).Value = Me.TotalUsed.Value
.Cells(lRow, 10).Value = Me.Maybe_No.Value
.Cells(lRow, 11).Value = Me.Acw.Value
.Cells(lRow, 12).Value = Me.Pledge.Value


End With

'clear the data
Me.cboprod.Value = ""
Me.OpBtn_post.Value = ""
Me.OpBtn_pre.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtAdv.Value = Sheets("Tracker").Range("A1")
Me.Offers_Made = Sheets("Raw Data").Range("F1")
Me.cboprod.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub OpBtn_post_Click()
cboprod.Value = "post"
cboprod.List = Worksheets("LookupLists").Range("$A$1:$B$35").Value
End Sub

Private Sub OpBtn_pre_Click()
cboprod.Value = "pre"
cboprod.List = Worksheets("LookupLists").Range("$C$1:$D$103").Value
End Sub

Private Sub userform_initialize()
Dim cPartPost As Range
Dim cPartPre As Range
Dim cOptPost As Range
Dim cOptPre As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cPartPost In ws.Range("PostIDList")
With Me.cboprod
.AddItem cPartPost.Value
.List(.ListCount - 1, 1) = cPartPost.Offset(0, 1).Value
End With
Next cPartPost

For Each cPartPre In ws.Range("PreIDlist")
With Me.cboprod
.AddItem cPartPre.Value
.List(.ListCount - 1, 1) = cPartPost.Offset(0, 1).Value
End With
Next cPartPre

For Each cOptPost In ws.Range("PostIDlist")
With Me.OpBtn_post
.AddItem cOptPost.Value
End With
Next cOptPost

For Each cOptPre In ws.Range("PreIDlist")
With Me.OpBtn_pre
.AddItem cOptPre.Value
End With
Next cOptPre

Me.cboprod.Value = ""
Me.OpBtn_post = ("Post pay")
Me.OpBtn_pre = ("Pre pay")
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtAdv.Value = Sheets("Tracker").Range("A1")
Me.cboprod.SetFocus


End Sub

[Tinbendr]

OK, I don't mind helping at all.

Look at the userform code page.

Run the userform. Post will start selected. Because I set the value of the optionbutton Post in the Initialize event, the optionbutton click event automatically fires. (Just like calling a subroutine) I moved the combobox code into the Click event so it will be populated. Try it out, click the combobox and look at the values.

Now click the Pre optionbutton. As soon as you click it, the Click event for the Pre runs and fills the combobox with the filtered data.

I'm still confused as to the roll of Location vs Column A or Col C.

Let me know if we have a meeting of the minds.

[gringo287]

Thank you, so much, that is sooo close to what i was after.


Baiscally, the location cbobox was what i initially thought would be necessary, because i assumed there would be a number a different depts needed. Because only two dpets need this, it was decided that the option buttons would be the "neatest" option. Therefore, the cboLocation is surplus and needs removing. Am i right in thinking that the below section of the code is what would need adjusting. In the "Raw Data" sheet, i have one a LOCATION column, which now i suppose needs changing to DEPT, to aviod more confusion. is it possible to adapt the below code to add either "post or "pre", or would i have to have one column for "post" and one for "pre", so that either one would be left blank if it was the opposite Dept that was chosen?


Private Sub OpBtn_post_Click()
Dim cPartPost As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

Me.cboprod.Clear
'cboprod.Value = "post"
'cboprod.List = Worksheets("LookupLists").Range("$A$1:$B$35").Value

For Each cPartPost In ws.Range("PostIDList")
With Me.cboprod
.AddItem cPartPost.Value
.List(.ListCount - 1, 1) = cPartPost.Offset(0, 1).Value
End With
With Me.cboLocation
.AddItem cPartPost.Offset(0, 8).Value
End With
Next cPartPost
'Selects the first in the dropdown.
Me.cboprod.ListIndex = 0
Me.cboLocation.ListIndex = 0
End Sub

Private Sub OpBtn_pre_Click()
Dim cPartPre As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

Me.cboprod.Clear
' cboprod.Value = "pre"
' cboprod.List = Worksheets("LookupLists").Range("$C$1:$D$103").Value
For Each cPartPre In ws.Range("PreIDlist")
With Me.cboprod
.AddItem cPartPre.Value
.List(.ListCount - 1, 1) = cPartPre.Offset(0, 1).Value
End With
With Me.cboLocation
.AddItem cPartPre.Offset(0, 6).Value
End With
Next cPartPre


'Selects the first in the dropdown.
Me.cboprod.ListIndex = 0
Me.cboLocation.ListIndex = 0

End Sub

[Tinbendr]

Are you talking about adding the data from the userform to the worksheet? No. That is in the Add Click event.

I adjusted that code a little so it would post correctly.

[snb]

Did you know ?


Private Sub OpBtn_post_Change()
with cboprod
.List = Range(IIf(OpBtn_post, "PostIDList", "PreIDlist")).Value
.ListIndex = 0
end with
end sub

Does the same job as

Private Sub OpBtn_post_Click()
Dim cPartPost As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

Me.cboprod.Clear

For Each cPartPost In ws.Range("PostIDList")
With Me.cboprod
.AddItem cPartPost.Value
.List(.ListCount - 1, 1) = cPartPost.Offset(0, 1).Value
End With
Next cPartPost
Selects the first in the dropdown.
Use -1 for No selection. (or Remove this line)
Me.cboprod.ListIndex = 0

End Sub

Private Sub OpBtn_pre_Click()
Dim cPartPre As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

Me.cboprod.Clear

For Each cPartPre In ws.Range("PreIDlist")
With Me.cboprod
.AddItem cPartPre.Value
.List(.ListCount - 1, 1) = cPartPre.Offset(0, 1).Value
End With
Next cPartPre

Selects the first in the dropdown.
Use -1 for No selection. (or Remove this line)
Me.cboprod.ListIndex = 0

End Sub

[gringo287]

:bow::bow: and one for snb :bow:

Thank you, so much guys, thats amazing!!.

One more question, just to throw a spanner in the works. I need some way to to cancel out the product value for occasions where an offer was made but not accepted (hence the maybe/no spin button). The offer still needs to be recorded, just at a £0.00 value.


Any suggestions on the best books/pdf guides to get hold of, so i dont need to keep pestering kind sould like yourselves. Ive now got a copy of an excel 2007 book by Steve johnson, but it only touches on vba a little.

[Tinbendr]

In column B of Raw Data?


'<snip>From Add_Click
.Cells(lRow, 9).Value = Me.Maybe_No.Value
If Me.Maybe_No.Value > 0 Then
.Cells(lRow, 10).Value = Me.Acw.Value
Else
.Cells(lRow, 10).Value = 0
End If
.Cells(lRow, 11).Value = Me.Pledge.Value
'<snip>


For book recommendations, look at my profile.

[gringo287]

yeh, column B.

It doesnt seem to do anything. I think that this may be due to my not giving a full breakdown of exactly what the tracker does. I may be going out on a limb here but i dont think it would work this way, as the spin buttons will be "played with" at points during the advisors shift. the reason for this is that the tracker is for the advisor to be able to control their stats and also to sort of work out how much they need to do to get their targets. so the spin buttons will be spun up and down during their breaks say, so they can see on the dashboard sheet what for example 2 more "maybe/no's" they could cope with. Would it have to be an extra option or a toggle button. Sorry i know im taking advantage now, but your being so helpful, i cant help asking more questions.

[gringo287]

Ive just had a stab at it but no difference. Am i thinking along the right lines or way off?


With ws
.Cells(lRow, 1).Value = Me.cboprod.Value
If Me.TbZero.Value = True Then
.Cells(lRow, 2).Value = "0"
End If
.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)

[Tinbendr]

Probably like this.

With ws
.Cells(lRow, 1).Value = Me.cboprod.Value
If Me.TbZero.Value = True Then
.Cells(lRow, 2).Value = "0"
else
.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)
End If

[gringo287]

No, its not making any difference. Have i insrted it correctly. im not getting any errors, its just not changing anything.


Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Raw Data")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'check for a part number
If Trim(Me.cboprod.Value) = "" Then
Me.cboprod.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If
'Position in the list
lPart = Me.cboprod.ListIndex
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboprod.Value
If Me.TbZero.Value = True Then
.Cells(lRow, 2).Value = "0"
.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)
End If
.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)
' .Cells(lRow, 3).Value = Me.OpBtn_post.Value
' .Cells(lRow, 4).Value = Me.OpBtn_pre.Value
'Check if Postpay button is selected.
'This is the same as If Me.OpBtn_post = True
If Me.OpBtn_post Then
.Cells(lRow, 3).Value = "PostPay"
Else 'If it's not post then it has to be Pre.
.Cells(lRow, 3).Value = "Prepay"
End If
.Cells(lRow, 4).Value = Me.txtDate.Value
.Cells(lRow, 5).Value = Me.txtQty.Value
.Cells(lRow, 6).Value = Me.txtAdv.Value
.Cells(lRow, 7).Value = Me.CallsTaken.Value
.Cells(lRow, 8).Value = Me.TotalUsed.Value
.Cells(lRow, 9).Value = Me.Maybe_No.Value
.Cells(lRow, 10).Value = Me.Acw.Value
.Cells(lRow, 11).Value = Me.Pledge.Value
End With
'clear the data
'Reset to the first entry. If you want it
'blank instead (or no selectioin), then use -1.
Me.cboprod.ListIndex = 0
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.txtAdv.Value = Sheets("Tracker").Range("A1")
Me.Offers_Made = Sheets("Raw Data").Range("F1")
Me.cboprod.SetFocus
End Sub

[Tinbendr]

If Me.TbZero.Value = True Then
.Cells(lRow, 2).Value = "0"
.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)
End If

This is overwriting the "0" with whatever's in cboProd.

It has to be an If/Else.


If Me.TbZero.Value = True Then
.Cells(lRow, 2).Value = "0"
else
.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)
End If


If you can't make it work., u/l a updated workbook.

[gringo287]

I think i prefer error messages. at least they give you somewhere to aim for.

[gringo287]

***edit***

I left an extra


.Cells(lRow, 2).Value = Me.cboprod.List(lPart, 1)
below the End If.

You are a the MAN. Thank you. Ill leave alone for now, while i get ordering your recommended study material. :cloud9: