none
VBA - userform combobox does match on another table and then found value is pasted in different cell RRS feed

  • Question

  • Hi everyone, 

    First post ever, so thanks for the help.

    I have a Combo box that is populated by RowSource=Full_Name which is a range belonging to a Table called EmployeeList.

    Some textboxes are populated with the Full_Name selection, by the code:

    Private Sub Full_Name_Change()

    'When user form opens, an employee name needs to be selected
    'Fields will then be populated with the below Sub

        Dim wks As Excel.Worksheet
        Dim selectedString As Variant
        Dim row As Long
        Dim value As Variant

        Set wks = Worksheets("EmployeeList")

        If Full_Name.ListIndex <> -1 Then
            selectedString = Full_Name.value

            On Error Resume Next
            row = Application.WorksheetFunction.Match(selectedString, wks.Columns(1), 0)
            On Error GoTo 0

            If row Then

                value = wks.Cells(row, 4)
                Role.value = value
                value = wks.Cells(row, 5)
                Location.value = value
                value = wks.Cells(row, 6)
                Manager.value = value
                value = wks.Cells(row, 7)
                Elliott.value = value
                value = wks.Cells(row, 8)
                Nationality.value = value

            Else

                MsgBox ("Value not found in the worksheet 'Employee List'")

            End If
        End If

    This values are then pasted in some specific cells by the code:

    Private Sub CreateIndividualReport_Click()


    'In the Personal information section, fields from the userform are pasted into specific cells in the worksheet

        Dim ws As Worksheet
        Set ws = Worksheets("IndividualReport")
        
            'Check for trainee name
            If Trim(Me.Full_Name.value) = "" Then
              Me.Full_Name.SetFocus
              MsgBox "Please select an Employee for which you want an individual report"
              Exit Sub
            End If
        
        'this piece defines the cell where info is going to be pasted. If the report/table is moved,
        'the info about the desired cell has to be moved
        
            With ws
            Cells(6, 5).value = Me.Full_Name.value
            Cells(12, 6).value = Me.Nationality.value
            Cells(13, 6).value = Me.Elliott.value
            Cells(14, 6).value = Me.Role.value
            Cells(15, 6).value = Me.Manager.value
                    
            End With
       
    End Sub

    This is working fine.

    Then, I want to use Full_Name to search in another table called MedicalDB also having in column1 Full_Name, and then fetching value of the matching row and column 12, to the another cell in the IndividualReport Worksheet. Is there a way I can do this without having to create another Sub that brings the value to a 'hidden' textbox in the userform? This is, eliminate the following code (or not having to create a textbox in the form to brind the value from the combobox).


      'Now set the Medical sheet as reference to copy restrictions to the textfield restrictions
        Dim wks2 As Excel.Worksheet
        Dim selectedString As Variant
        Dim row As Long
        Dim value As Variant

        Set wks2 = Worksheets("MedicalDB")

        If Full_Name.ListIndex <> -1 Then
            selectedString = Full_Name.value

            On Error Resume Next
            row = Application.WorksheetFunction.Match(selectedString, wks.Columns(1), 0)
            On Error GoTo 0

            If row Then

                value = wks.Cells(row, 12)
                Restrictions.value = value
            Else

                MsgBox ("Value not found in the worksheet 'Medical List'")

            End If
        End If

    Friday, February 5, 2016 10:32 AM

Answers

  • Hi luar669,

    According to your description, do you have two user forms and want to access the value (combo box) from other form?

    If so, first you may use Global Variable to store the value.

    Secondly, you may use the Tag property of UserForm to store the value.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 8, 2016 5:31 AM
    Moderator