CODEWITHSUNDEEP
vbaexcel
ptpapa
ptpapa

Reputation: 11

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

Upvotes: 0

Views: 1476

Answers (1)

MatthewD
MatthewD

Reputation: 6761

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))

Upvotes: 3

Related Questions