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