-2

I would like to create an ActiveX Command Button that will populate cells on one worksheet based off of data from a separate, hidden sheet (still in the same workbook though). However, I want the button to know to pull specific data depending on selections that were chosen in list boxes.

For example: Let’s say someone is working in “Worksheet 1,” where 2 ActiveX List Boxes and 1 ActiveX Command button are located. If this individual chooses “Animal” in the first list box, then the second list box will display a following list of animals. Then, let’s say that person chooses “dog” in the second list box. I then want the ActiveX Command Button to recognize that someone chose both “animal” and “dog” in the two previous list boxes, and then when clicked, be able to pull data from a separate hidden sheet (“Worksheet 2”) to display on “Worksheet 1.” So, in other words the data for the selection “dog” would be hidden away from the individual, but when they choose “dog” in the second list box, and then click the ActiveX command button, then the data would appear.

I already know how to write the code in VBA for dependent list boxes, but I am unsure on how to make an ActiveX Command Button dependent upon selections in a list box. Is this possible, and if so, can anyone provide me with a VBA code that I can use to fulfill my specifications? Furthermore, does anyone know a VBA code for an ActiveX Command Button that will pull data from one sheet, and display it on another? Any input is appreciated. Thanks!

Community
  • 1
  • 1
Stlcards13
  • 1
  • 1

3 Answers3

0

Hard to be specific with the info given, but hopefully this can point you in the right direction. This assumes that you only have one column in your 2nd ListBox:

Sub cmdActiveX_Click()
    Dim strBox1 as String
    Dim strBox2 as String

    strBox1 = Worksheets("SheetWithListBoxes").ListBox1.List(Worksheets("SheetWithListBoxes").ListBox1.ListIndex))
End Sub

A cleaner way might be:

Sub cmdActiveX_Click()
    Dim lbo as ListBox
    Dim strBox1 as String

    Set lbo = Worksheets("SheetWithListBoxes").ListBox1
    strBox1 = lbo.List(lbo.ListIndex) 
End Sub

So, you get the selected item's index from ListIndex, and then you pass that to the List method. This also assumes that MulitSelect is off (single).

Have fun!

Hauffa
  • 11
  • 2
  • I very much appreciate the help, but I am still having issues with the code. I attempted to attach an example Excel file to this message, but can't seem to find an area where that's possible. That being said, to be more specific, these are the names of each of the ActiveX controls: The first List Box is titled: "cboCategoryList" The second List box is titled: "cboDependentList" The command button is titled: "CommandButton1" The sheet with the list boxes on it is titled: "Report" The sheet with the data is titled: "Data" – Stlcards13 May 30 '17 at 14:38
  • What have you tried? What error are you getting? Where are you getting the error? The more specific you are, the more help you'll get. ;) – Hauffa May 30 '17 at 16:08
  • I keep getting an error that says something along the lines of: "Object doesn't support this property or method." I obviously have tried substituting my exact names/data into the codes, but to no avail. I guess my question to you is, what else would you like to know (or want me to clarify) so that you have a better idea of what it is that I am looking for. Again, I wish I could simply attach the example Excel template that I have so that you can see exactly what I am working with, but there doesn't seem to be an area where you can include attachments. – Stlcards13 May 30 '17 at 18:34
  • You could paste your code, and share what line the error is occurring on, for starters. – Hauffa May 31 '17 at 15:09
0

Well, I believe I actually found a way to have the Command Button pull data from one sheet and place it on another sheet by creating this module:

Sub PopulateMain(ByVal lRowNumber As Long)

Dim shReport As Worksheet
Dim shData As Worksheet

' Assign sheets to the variable.
Set shReport = ThisWorkbook.Worksheets("Report")
Set shData = ThisWorkbook.Worksheets("Data")

' Paste the value in sheet Data Range H2 into sheet Report Range C12.
shReport.Range("C14").Value = shData.Range("H2").Value
shReport.Range("C16").Value = shData.Range("I2").Value
shReport.Range("C18").Value = shData.Range("J2").Value
shReport.Range("C20").Value = shData.Range("K2").Value
shReport.Range("K14").Value = shData.Range("L2").Value
shReport.Range("K16").Value = shData.Range("M2").Value
shReport.Range("K18").Value = shData.Range("N2").Value

End Sub

And then using this code:

Private Sub CommandButton1_Click()

Call PopulateMain(2)

End Sub

However, I still don't know how to link the Command Button to the List Boxes so that it knows a particular choice has been made. So, again, if someone chose "Animals" in the first box and "Dog" in the second box, I need the Command Button to recognize those two choices in order to pull the correct data from the hidden sheet.

Stlcards13
  • 1
  • 1
0

Ok, try something like this (by the way, are these list boxes or combo boxes? Confused by your naming if they are list boxes or not...if combo, change the As ListBox to As ComboBox):

Sub cmdActiveX_Click()
    Dim lboCategory as ListBox
    Dim lboDependent as ListBox
    Dim strBox1 as String

    Set lboCategory = Worksheets("Report").cboCategoryList
    strCategory = lboCategory.List(lboCategory.ListIndex) 

    Set lboDependent = Worksheets("Report").cboDependentList
    strCategory = lboCategory.List(lboCategory.ListIndex) 

End Sub

Then use the 2 string values to find your data...make sense?

Hauffa
  • 11
  • 2