1. Create 3 Sheets, rename it to Setting, Database, Dashboard.
2. On Setting sheet, create table for Product Name (i assume there is no duplicate name for product, so we don't have to make Product ID), in this table put whole product you have.
| Book1 |
|---|
|
|---|
| A | B | C |
|---|
| 1 | Product Name | | |
|---|
| 2 | Triclabendazole | | |
|---|
| 3 | Bupivacaine | | |
|---|
| 4 | Mebendazole | | |
|---|
| 5 | | | |
|---|
| 6 | | | |
|---|
| 7 | | | |
|---|
| 8 | | | |
|---|
| 9 | | | |
|---|
| 10 | | | |
|---|
| 11 | | | |
|---|
|
|---|
3. On Database sheet, create table for hold the information about the customer associated with the product, so you will need column Product Name, Buyer's Name, Email and Name, put your whole data into it.
4. On Dashboard sheet, put ComboBox from ActivexControl to hold the product name, Developer > Insert > choose ComboBox from Activex Control
View attachment 116753
5. Right click on that ComboBox, choose properties, change Style from "0 frmStyleDropDownCombo" to "2 frmStyleDropDownList"
6. Still on Dashboard sheet, put some header for the table display, Buyer's Name, Email and Name, YOU HAVE TO DO THIS ON ROW 3 or you will get unexpected result (if you don't want to do that on row 3 then you have to change a bit of the code to make them fit each others).
7. The idea is when someone choose something on this combobox then table below it (Table Display) will show the data associated with that product, to achieve that put this code to your Dashboard Sheet Code Environment :
VBA Code:
Option Explicit
Private Sub ComboBox1_Change()
Dim oWsDatabase As Worksheet
Dim oWsDashboard As Worksheet
Dim iLastRowDatabase, iLastRowDashboard, i As Long
Dim colData As New Collection
Dim arrData As Variant
Dim varData As Variant
Set oWsDatabase = ThisWorkbook.Worksheets("Database")
Set oWsDashboard = ThisWorkbook.Worksheets("Dashboard")
iLastRowDatabase = oWsDatabase.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iLastRowDashboard = oWsDashboard.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If iLastRowDatabase > 1 Then
arrData = oWsDatabase.Range("A2:D" & iLastRowDatabase).Value
For i = LBound(arrData) To UBound(arrData)
If arrData(i, 1) = Me.ComboBox1.Value Then
colData.Add arrData(i, 2) & "/" & arrData(i, 3) & "/" & arrData(i, 4)
End If
Next i
With oWsDashboard
If iLastRowDashboard > 3 Then .Range("A4:C" & iLastRowDashboard).Delete Shift:=xlUp
iLastRowDashboard = 4
For Each varData In colData
arrData = Split(varData, "/")
.Range("A" & iLastRowDashboard) = arrData(0)
.Range("B" & iLastRowDashboard) = arrData(1)
.Range("C" & iLastRowDashboard) = arrData(2)
iLastRowDashboard = iLastRowDashboard + 1
Next varData
End With
End If
End Sub
Private Sub Worksheet_Activate()
Dim oWs As Worksheet
Dim iLastRow As Long
Dim arrData As Variant
Set oWs = ThisWorkbook.Worksheets("Setting")
iLastRow = oWs.UsedRange.Rows.Count
arrData = oWs.Range("A2:A" & iLastRow).Value
Me.ComboBox1.List = arrData
End Sub
8. Done
