PDA

View Full Version : UserForm Retrieve Data From Correct Sheet, based on Combo Box Value



Rodger
12-26-2017, 04:15 PM
I have an Excel workbook where a Client ID is assigned. As Clients sign up for services, they have data entered on to a couple of sheets, where they are all co-mingled, but their finances are entered on an individual sheet, named as their assigned Client ID. For the sake of argument, I'll use Client ID "RB1". As their finances change (weekly, bi-weekly and/or monthly), I want the User to be able to launch a form where they select the Client ID. The form then pulls certain fields from the appropriate sheet.

I'm struggling with how to code a scripting dictionary to allow me to do this. Also, I'm quite new to VBA and am using this exercise to teach myself.

Any thoughts/suggestions?

SamT
12-27-2017, 10:52 AM
Assign non-client, (ie: Static,) sheet names to a string Variable

NCSheets = "Sheet1, Sheet2, etc"

assign all other sheets' names to a listbox

For each Sht in Worksheets
If Not CBool(Instr(NCSheets, Sht.Name)) Then
ListBox.List.Add Sht.Name

Assign Listbox selection to a variable

Private Sub ListBox_Change()
ClientSheetName = ListBox

Get the details from that sheet

With Sheets(ClientSheetName)
Me.Control1 = .Range("ABC123")
Me.Control2 = .Range("DEF456")