Populate textboxes from multiple worksheets based on combobox selection

[Divinedar_1]

Ok I have a workbook that have 5 worksheets for a 5 year span. On each of the worksheets are there is a list of countries that make up the combobox (which works perfectly). On my userform I have a frame on the userform that represent each worksheet.

My problem is that I can't get the textboxes to populate based on the combobox selection. If that country is on three of the sheets I need for it to populate according to the year.

This code works perfectly with the exception of populating my label boxes with line one starting with column C. It will fill my last 3 userform frames but wrong dates.

Private Sub UserForm_Initialize()

Dim i As Long, j As Long
Dim dic As Object
Dim ctrl As Control, thisFrame As Control
Dim x As Integer, Uniques As Variant, Sorted As Variant

Set dic = CreateObject("Scripting.Dictionary")

'populate combobox

For Each ws In ActiveWorkbook.Worksheets
    Select Case UCase(ws.Name)
    Case "FY2019", "FY2020", "FY2021", "FY2022", "FY2023"
        With ws
            lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
            'for combo drop down
            arr = .Range("B2:B" & lr)
            For i = 1 To UBound(arr, 1)
                If arr(i, 1) <> "" And InStr(arr(i, 1), "Total") = 0 And InStr(arr(i, 1), "TOTAL") = 0 Then
                    dic(arr(i, 1)) = 1
                End If
            Next i
            'for labels
            j = 3
            Set thisFrame = Controls("frame" & ws.Name)
            For Each ctrl In thisFrame.Controls
                If TypeOf ctrl Is MSForms.Label Then
                    ctrl.Caption = Format(.Cells(1, j).Value, "mmm-yy")
                    j = j + 1
                End If
            Next ctrl
        End With
    End Select
Next ws

' Sort The Unique Values
    Uniques = dic.keys
    With CreateObject("System.Collections.ArrayList")
        For x = LBound(Uniques) To UBound(Uniques)
            .Add Uniques(x)
        Next
        .Sort
        Sorted = .ToArray
    End With

  ' Sort combobox
  cboFYList.List = Sorted

End Sub

I also need to be able to add info to the corresponding worksheet based on the fiscal year; i.e., If I have to add another country to FY2019, I need to be able to add a new country in the combobox and from the FY2019 frame add new numbers that will go into FY2019 worksheet. The trick to this is that on each worksheet in column A that is "INT'L", "INT'L" AND "US GOV" on each sheet. The newly added info has to be inserted into one of these categories on corresponding pages.

I have attached pictures of the workbook to get a better look at what I'm working with.

Click Here

ALSO NOTE: Although all the cells are filled in with amounts be advised that there will be blank cells in the mix.

So what I'm looking for is a code to fill my textboxes. I want to learn how so if someone one can get me started then then tell me what to look for I would like to try and figure out as much as possible on my own with just some guidance.

[Divinedar_1]

Ok it's been over a week and no response. If to complicated let me know and we'll take it one step at a time. I need help please if any out there.

[rproffitt]

Your choice of tags may be a little off. I never saw VB6 used for such. And what code there is may be VBA or VB.NET.

My only advice is to break the problem down to smaller steps. Rather than expect the code to cover all the problems in one go, maybe just find a way to fill in one box. Then code to fill in the next box. Step by step, until it's done.

I also worry at times folk expect others to code it up and supply a finished product. For that you post some for hire type posting.

Another way would be to make the problem smaller so that folk can see the smaller piece and see what can be done.

[chj124]

If you could provide a copy of the workbook it would be much easier to diagnose the issue

[xrjf]

Assuming 'lr' is the last populated row it could be lr = .UsedRange.rows.count instead of .cells.find(...
In any case you could change line #21 to:
dic(mid(ws.Name,3) & arr(i,1)) ' prepend the year '
or
dic(arr(i,1) & mid(ws.Name,3)) ' postfix the year '
depending on the sort preference
in this manner you'll have each country once per year.

[xrjf]

Of course, I meant the following (equal to 1):

dic(mid(ws.Name,3) & arr(i,1)) = 1 ' prepend the year
or

dic(arr(i,1) & mid(ws.Name,3)) = 1 ' postfix the year

[Divinedar_1]

Thank you for the responses but I'm just receiving them been out of touch. I will try your suggestions and get back to you. Again thank you.