UserForm Identifying Correct Sheet Based on cobo Value

Question

I have a workbook that houses Client information. There is a sheet for each Client, and each sheet is labeled with the Client's unique ID. I'm wanting to launch a UserForm where the User will select a Client from a cobo box. Then, data from the last row of the appropriate sheet populates the UserForm.

In other code within the same workbook, I'm using a scripting dictionary, but those all tie to a specific range, in a specific sheet. I have no clue how to code for the UserForm to search across all sheets to find the one that has the same name as the value in the cobo_ClientID field, and then bring in the correct data elements from the row with the MAX updated date.

Here's an example of the scripting dictionary that I've been using in other areas:

Set coboDict = CreateObject("Scripting.Dictionary")
With coboDict
    For Each cStatsClientID In ws1.Range("StatsClientID")
        If Not .exists(cStatsClientID.Value) Then
            .Add cStatsClientID.Value, cStatsClientID.Row
        Else
            If CLng(cStatsClientID.Offset(, -2).Value) > CLng(ws1.Range("B" & .Item(cStatsClientID.Value))) Then
            .Item(cStatsClientID.Value) = cStatsClientID.Row
            End If
        End If
    Next cStatsClientID
    Me.cobo_ClientID.List = Application.Transpose(.keys)
    End With

Can you not use the Worksheet.Name property to get the correct worksheet and then a [lastRow](https://www.rondebruin.nl/win/s9/win005.htm) calculation to retrieve the last row? — QHarr, Dec 27 '17 at 13:53
To be honest, I'm not sure, as I'm still relatively new to VBA and haven't used that property before. In looking it up, I'm not sure how it would allow me to A - identify the correct sheet, based on the value of the cobo_ClientID or B - be able to bring over the values from the correct sheet, found in the row with the MAX updated date. — Rodger, Dec 27 '17 at 13:59
Each Client sheet is named the same as the Client ID. So, if there are 3 Clients with an ID of "RB1", "RB2" and "QJ4" respectively, there are 3 Client sheets named "RB1", "RB2" and "QJ4". I've been able to get the code to identify the correct sheet, but still can't get the code to pull in the data from cells on that sheet,from the last row. — Rodger, Dec 27 '17 at 15:20
Struggling to work out from the above but you should be able to use a variable that is the sheet to work with e.g. Set ws1 = Worksheets(cobo_ClientID) , and to determine the last row see the link i provided in earlier comment, then are the values you want within specific columns in that row? E.g. myValue1 = ws1.Cells(lastRow, "A") — QHarr, Dec 27 '17 at 15:30
Between the LastRow link you provided, and some advice from another forum, I think I may have it. So far, this code seems to be working: Private Sub cobo_ClientID_Change() Dim Sht As String Dim LastRow As Long Sht = Me.cobo_ClientID With ActiveSheet LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row End With txt_Name = Sheets(Sht).Range("E").Value txt_DPPymtAmt = Sheets(Sht).Range("H" & LastRow).Value End Sub — Rodger, Dec 27 '17 at 15:47
Not sure why, but I don't see an option to post the code properly, as in my original post. — Rodger, Dec 27 '17 at 15:48
Got it! Thank you VERY much for your help. My laptop was about to have to grow wings as it flew through the window. — Rodger, Dec 27 '17 at 15:54

score 0 · Answer 1 · answered Dec 27 '17 at 15:53

Between the LastRow link that was provided, and some advice from another forum, I think I have the solution. The issue seemed to be with how I was setting the LastRow, as well as finding the correct sheet.

Private Sub cobo_ClientID_Change()

Dim Sht As String
Dim LastRow As Long

Sht = Me.cobo_ClientID

With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
End With

txt_Name = Sheets(Sht).Range("E" & LastRow).Value
txt_DPPymtAmt = Sheets(Sht).Range("H" & LastRow).Value

End Sub

Darren Bartrup-Cook · Answer 2 · 2017-12-27T17:01:41.640

This code will look at each sheet name and list them within the combo box. When you select one of the sheets it will take the values from the last row and place them in the text boxes on the form.

Add these controls to a userform:

Combo box called cmbSheets
Three text boxes named txtColA, txtColB and txtColC.

-

Private Sub UserForm_Initialize()

    Dim wrkSht As Worksheet

    'Populate the combo-box with sheet names.
    For Each wrkSht In ThisWorkbook.Worksheets
        With Me.cmbSheets
            .AddItem wrkSht.Name
        End With
    Next wrkSht

End Sub


'Will place the values from the last row columns A:C in textboxes on the form.
Private Sub cmbSheets_Change()
    Dim rLastCell As Range
    Dim shtSelected As Worksheet

    'Set a reference to the sheet selected by the combo box.
    Set shtSelected = ThisWorkbook.Worksheets(cmbSheets.Value)

    Set rLastCell = LastCell(shtSelected)

    With shtSelected
        Me.txtColA = .Cells(rLastCell.Row, 1)
        Me.txtColB = .Cells(rLastCell.Row, 2)
        Me.txtColC = .Cells(rLastCell.Row, 3)
    End With
End Sub

'This function can be placed in a normal module.
'Finds the last cell given a worksheet reference.
Public Function LastCell(wrkSht As Worksheet) As Range

    Dim lLastCol As Long, lLastRow As Long

    On Error Resume Next

    With wrkSht
        lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
        lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0

End Function

Note - if any of the values require certain formatting then that should be added using the FORMAT command when copying.
E.g. If a cell has the date 01/05/2016 for 1st May 2016 then it appears in the text box as 5/1/2016 (converted to US date format).
Using the code Me.txtColC = Format(.Cells(rLastCell.Row, 3), "dd-mmm-yy") will display the date as 01-May-16 on the form.
Similarly currency should be added as Me.txtColB = Format(.Cells(rLastCell.Row, 2), "Currency") otherwise £15 will appear as 15.

If you want to exclude certain sheets have a look at SELECT CASE...END SELECT code blocks (or IF...ELSE...END IF)

If you want the sheet to change as you select different values in the combo box just add shtSelected.Select to the end of the cmbSheets_Change() event.

UserForm Identifying Correct Sheet Based on cobo Value

2 Answers2