How does one populate a Userform textbox or textboxes from multiple sheets
First I want to say I am new to VBA but have been learning alot and had great help from Davesexcel on my last question. Thanks to him I have been able to create a Userform that pulls data into textboxes on my Userform based on name chosen in combobox. The problem I am now having is each name needs to be pulled monthly from the same workbook (multiple sheets-each sheet is named by month; so Jan is sheet1; Feb is sheet 2 and so on). I can pull sheet 1 data into the Userform by name chosen and that works fine. How would I do the next months when the time comes and should they go into seperate textboxes on the Userform or should I extend the width of the textboxes already there and use one textbox for each subject. I am supplying a screen view of what the Userform looks like and hopefully the code will show up correctly as well Userform with code working but should textboxes be extended or additional textboxes used
Private Sub cbo_Agent_Change() Dim Rws As Long, ConRng As Range, AdhRng As Range, AHTRng As Range, ACWRng As Range, TcktsRng As Range, LMIRng As Range, UnderRng As Range, KnowRng As Range, OvrSatRng As Range, OvrScoRng As Range, NPSRng As Range, Agnt As Range Rws = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(2, 1), Cells(Rws, 1)) Set Agnt = Rng.Find(what:=cbo_Agent, lookat:=xlWhole) Set ConRng = Agnt.Offset(0, 1) 'set ConRng Set AdhRng = Agnt.Offset(0, 2) 'set AdhRng Set AHTRng = Agnt.Offset(0, 3) 'set AHTRng Set ACWRng = Agnt.Offset(0, 4) 'set AHTRng Set TcktsRng = Agnt.Offset(0, 5) 'set TcktsRng Set LMIRng = Agnt.Offset(0, 6) 'set LMIRng Set UnderRng = Agnt.Offset(0, 7) 'set UnderRng Set KnowRng = Agnt.Offset(0, 8) 'set KnowRng Set OvrSatRng = Agnt.Offset(0, 9) 'set OvrSatRng Set OvrScoRng = Agnt.Offset(0, 10) 'set OvrScoRng Set NPSRng = Agnt.Offset(0, 11) 'set NPSRng txt_Con = ConRng txt_Adh = AdhRng txt_AHT = AHTRng txt_ACW = ACWRng txt_tckts = TcktsRng txt_LMI = LMIRng txt_Under = UnderRng txt_Know = KnowRng txt_Osat = OvrSatRng txt_OScor = OvrScoRng txt_NPS = NPSRng End Sub Private Sub UserForm_Initialize() Dim Rws As Long, Rng As Range Rws = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(2, 1), Cells(Rws, 1)) cbo_Agent.List = Rng.Value End Sub
As with the original question; my goal once I learn enough is to have the Userform filled monthly with results but from a different workbook, but for now I want to learn and be able to pull the data in the same workbook for each month then tackle how to pull data from a closed workbook. Orignally was looking to pull the data from 20xxperformance.xlsx into a Master Ledger but for sake of learning I built the Userform in the 20xxperformance to understand the functioning aspects. I hope my inquiry is understandable and if not my apologies as I will try to be more explicit if needed. Thank you for your help and guidance as I continue to learn and grow.
Question can I not use something along this line? Dim Ws As Worksheet, rCell As Range
For Each Ws In ActiveWorkbook.Worksheets Select Case UCase(Ws.Name) Case "SHEET1", "SHEET3", "SHEET4" With Ws For Each rCell In .Range("A1", .Cells(Rows.Count, 1).End(xlUp)) ComboBox1.AddItem rCell Next rCell End With Case "SHEET2" With Ws For Each rCell In .Range("A1", .Cells(Rows.Count, 4).End(xlUp)) ComboBox1.AddItem rCell Next rCell End With End Select Next Ws
or should I use a listbox instead? I would prefer not to do monthly code and that many textboxes although it is good for learning which I will still do but need to get this sooner than later and I do like Mathew's idea for the looping but still need help on that over and above what I received as it still somehwat confuses me. Thanks
I would use additionals textboxes. You can someday (if you wish) create them dynamically and use them as an array but that it's a bit more advanced.
Use a worksheet object to get data from a worksheet that is not the active worksheet.
Dim ws as Excel.Worksheet Set ws = Application.Sheets("February")
Then use the worksheet object for your range and cell objects.
Rws = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Set Rng = ws.Range(ws.Cells(2, 1), ws.Cells(Rws, 1))