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