Fill userform, combobox and frame based on data from multiple worksheets

Divinedar · #1 04-18-2018, 12:21 PM

I'm going to attach the file but want to explain a few things.

1. I need to fill the labels on the form and I want to do this using vba because each year it changes. And if I change the worksheet in one cell they all change and this way all the labels will change. The labels are worksheets FY2019 to FY2023 and the row is C1:N1 on each worksheet.

2. I need for my combobox (which I was able to get it to create a dynamic list from each worksheet) to not have blanks or duplicates. The objective is to select a country and fill in the five frames with information from each worksheet based on the select in the combobox

3. I need to be able to add info to a 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 number 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 page.

So I've attached the file and I await your response on any way I can be helped. Thank you for your help in advance.

Divinedar · #2 04-23-2018, 05:18 AM

Can someone at least help me get started? Please.

NoSparks · #3 04-23-2018, 09:27 AM

to get started... try this for your form Initialization

Code:

Private Sub UserForm_Initialize()

Dim Ws As Worksheet, lr As Long, i As Long, j As Long
Dim dic As Object, arr As Variant
Dim ctrl As Control, thisFrame As Control

Set dic = CreateObject("Scripting.Dictionary")

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
            
'populate combobox
cboFYList.List = Application.Transpose(dic.keys)

End Sub

Divinedar · #4 04-23-2018, 11:13 AM

NoSparks that is so totally cool. Now that you've done it I can read and see what it does. That's what I need. I know how to write just not sure how to write it if you know what I mean. Thank you so much. I'm going to work on filling the text boxes on my own if I need help I'll let you know. Thank you so much so totally cool.

One more thank; can it go in alpha order?

NoSparks · #5 04-23-2018, 02:20 PM

Had no idea how to alpha sort the dictionary keys.
My buddy 'Google' lead me to this which seems to do the trick.

Replace

Code:

'populate combobox
cboFYList.List = Application.Transpose(dic.keys)

with

Code:

  ' 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
  
  ' Populate Combo with Sorted Uniques
  cboFYList.List = Sorted

You'll also need to declare the additional variables near the beginning along with the other Dim statements

Code:

Dim X As Integer, Uniques As Variant, Sorted As Variant

Divinedar · #6 04-24-2018, 05:28 AM

OMG Thank you so much that works perfectly.

Now my next endeavor is to populate the text boxes on the userform. They have to populate from each worksheet to each frame based on the year and be able to update each worksheet with new input.

Thank you so much for your help. I'm learning more and more everyday.

NoSparks · #7 04-24-2018, 06:42 AM

I'm sure you'll find that interesting.

I'd suggest starting by aligning the textbox names with the associated label names.
Textbox1 with Label1 and Textbox2 with Label2 etc., not Dte08012019 with Label1 and Textbox1 with Label2.

Good luck with your project.

Divinedar · #8 04-26-2018, 10:05 AM

Ok I can't seem to get my textboxes to fill in with the information from each of the worksheets. HELP!!!!

HTML Code:

Private Sub cboFYList_Change()
Dim i As Long, LastRow As Long
Worksheets(“ShtFY2019”).Select
LastRow = Worksheets(“FY2019”).Range(“C” & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Worksheets(“FY2019”).Cells(i, “A”).Value = (Me.cboFYList) Or _
Worksheets(“FY2019”).Cells(i, “A”).Value = Val(Me.cboFYList) Then
Me.txtl0119 = Worksheets(“FY2019”).Cells(i, “C”).Value
Me.txtl0219 = Worksheets(“FY2019”).Cells(i, “D”).Value
End If
Next
End Sub

I have tried every code I know and this was the last one I tried. I don't know if it's because of the frame I can't get them to fill in and can't see where I'm going wrong.

I need to now get the textboxes populated with the information from each worksheet. If you look at the userform above there is 5 frames and each frame is a fiscal year etc., FY2019, FY2020,...

I need to be able to fill each frame from each worksheeet and if edited to be writable back to each worksheet updated.
Also I need that if info need to be added to be added before each total line under the category of INT'L, INT'L GOV and US GOV. Is this possible?
PLEASE HELP! Thank you in advance.

NoSparks · #9 04-26-2018, 05:07 PM

Quote:

can't see where I'm going wrong

I have a feeling you're not putting much effort into this.

What happens if...
you use the same sheet name throughout what you have ?
you look in the right column for what's been selected in the combobox ?
you do away with half the OR statement and have the other half looking to be equal to Me.cboFYList.Value ?
and have numbers in the cells so that something will actually show up in each of the text boxes when you test things ?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Summing data from multiple worksheets in a workbook and placing into another worksheet	safrac	Excel	17	10-04-2022 12:21 PM
Merging 2 different cells containing IF formula & change in cell values based on multiple time frame	jay_excel	Excel	0	07-29-2017 11:04 PM
Excel to Word data import based on UserForm entry	jhancock1994	Word VBA	8	05-16-2017 04:41 PM
Using combobox to pass data from an Excel spread sheet to text boxes on userform	Stewie840	Word VBA	14	12-17-2015 10:13 PM
Conditional color fill based on presence of data	avanderh	Excel	11	07-28-2011 09:10 AM