Auto populate TextBox on UserForm using the same type formula from Worksheet?

[jmpatrick]

Hope I can explain this properly.

I have a formula in column E that matches data in column C and displays a hyperlinked name based on that data. Looks like this:

Now I've created a UserForm to make data entry on the main sheet easier. I'd like to add the same auto populate feature found in column E to the UserForm "Field Manager" text box. So, when I type data in the "Sub/Lot Code" text box I need the "Field Manager" text box to auto populate based on that data. The "Field Manager" text box is named FieldManagerReturn.

I'm not sure where the code that does the matching would reside. Perhaps with the actual Form?

Option Explicit
Private Sub cmdReset_Click()
    Call Reset
End Sub
Private Sub cmdSave_Click()
    Call Submit
    Call Reset
End Sub
Private Sub GarageHandling_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = 13 Then
             Submit
        End If
        If KeyCode = 13 Then
             Reset
        End If
End Sub
Private Sub Frame1_Click()
End Sub
Private Sub txtID_Change()
End Sub
Private Sub Frame2_Click()
End Sub
Private Sub Label1_Click()
End Sub
Private Sub UserForm_Initialize()
    Call Reset
End Sub
Private Sub userform_terminate()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("Database").Visible = True
    Sheets("Database").Select

    Dim WorkRange As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.ClearContents
    Next Cell

    Sheets("Database").Visible = False
    Sheets("Calendar").Select
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub
Private Sub CloseButton_Click()
  Unload Me
End Sub

 

[jmpatrick]

Here's what I've come up with so far. Doesn't work.

Private Sub SubLotCode_Change()
    If Me.SubLotCode = "*1125*" Then
    Me.SubdivisionReturn = "AO - Arbor Oaks"
    If Me.SubLotCode = "*1126*" Then
    Me.SubdivisionReturn = "FS - Fieldstone"
    If Me.SubLotCode = "*1127*" Then
    Me.SubdivisionReturn = "GVS - Grandview Estates South"
    If Me.SubLotCode = "*1128*" Then
    Me.SubdivisionReturn = "BP - Beacon Pointe"
End If
End Sub

 

[jmpatrick]

Some success! This works:

Private Sub SubLotCode_Change()
    If Me.SubLotCode = "1125" Then
    Me.SubdivisionReturn = "AO - Arbor Oaks"
End If
End Sub

Next up is adding more SubLotCodes to match.

 

[jmpatrick]

This seems to work

Private Sub SubLotCode_Change()
    If Me.SubLotCode = "1125" Then
    Me.SubdivisionReturn = "AO - Arbor Oaks"
    End If
    If Me.SubLotCode = "1126" Then
    Me.SubdivisionReturn = "FS - Fieldstone"
End If
End Sub

 

[jmpatrick]

Getting more adventurous. I've added the scanning of a second condition, which works fine. What I cannot seem to figure out is how I can add an ELSE so that the target textboxes remain blank if no match is made. Here's my current progress:

Private Sub SubLotCode_Change()

    If Me.SubLotCode = "1125" Then
    Me.SubdivisionReturn = "AO - Arbor Oaks"
    End If
    If Me.SubLotCode = "1126" Then
    Me.SubdivisionReturn = "FS - Fieldstone"
    End If
    If Me.SubLotCode = "1127" Then
    Me.SubdivisionReturn = "GVS - Grandview Estates South"
    End If
    If Me.SubLotCode = "1128" Then
    Me.SubdivisionReturn = "BP - Beacon Pointe"
    End If
    
    If Me.SubLotCode = "1125" Then
    Me.FieldManagerReturn = "Shaun Jaward"
    End If
    If Me.SubLotCode = "1126" Then
    Me.FieldManagerReturn = "Jacob Johnson"
    End If
    If Me.SubLotCode = "1127" Then
    Me.FieldManagerReturn = "Mike McGraw"
    End If
    If Me.SubLotCode = "1128" Then
    Me.FieldManagerReturn = "Jamie Celestini"
    End If
    
End Sub

 

[Rick Rothstein]

Two things with your code so far. First, you do not need separate If..Then blocks for the same SubLotCodes, actions for like SubLotCodes can be grouped in the same If..Then blocks. Second, since only one SubLotCode can be "active" at any one time, you do not need separate If..Then blocks for each... you can use ElseIf statements to link them into a single "If..End.If" overall block. This is what your code would look like if we do these two suggestions (note that I added the Else block that you asked about)...

Private Sub SubLotCode_Change()

    If Me.SubLotCode = "1125" Then
        Me.SubdivisionReturn = "AO - Arbor Oaks"
        Me.FieldManagerReturn = "Shaun Jaward"
    ElseIf Me.SubLotCode = "1126" Then
        Me.SubdivisionReturn = "FS - Fieldstone"
        Me.FieldManagerReturn = "Jacob Johnson"
    ElseIf Me.SubLotCode = "1127" Then
        Me.SubdivisionReturn = "GVS - Grandview Estates South"
        Me.FieldManagerReturn = "Mike McGraw"
    ElseIf Me.SubLotCode = "1128" Then
        Me.SubdivisionReturn = "BP - Beacon Pointe"
        Me.FieldManagerReturn = "Jamie Celestini"
    Else
        ' This is where you would place your code if none of the If's are True
    End If

End Sub

 

[jmpatrick]

Two things with your code so far. First, you do not need separate If..Then blocks for the same SubLotCodes, actions for like SubLotCodes can be grouped in the same If..Then blocks. Second, since only one SubLotCode can be "active" at any one time, you do not need separate If..Then blocks for each... you can use ElseIf statements to link them into a single "If..End.If" overall block. This is what your code would look like if we do these two suggestions (note that I added the Else block that you asked about)...

Private Sub SubLotCode_Change()

    If Me.SubLotCode = "1125" Then
        Me.SubdivisionReturn = "AO - Arbor Oaks"
        Me.FieldManagerReturn = "Shaun Jaward"
    ElseIf Me.SubLotCode = "1126" Then
        Me.SubdivisionReturn = "FS - Fieldstone"
        Me.FieldManagerReturn = "Jacob Johnson"
    ElseIf Me.SubLotCode = "1127" Then
        Me.SubdivisionReturn = "GVS - Grandview Estates South"
        Me.FieldManagerReturn = "Mike McGraw"
    ElseIf Me.SubLotCode = "1128" Then
        Me.SubdivisionReturn = "BP - Beacon Pointe"
        Me.FieldManagerReturn = "Jamie Celestini"
    Else
        ' This is where you would place your code if none of the If's are True
    End If

End Sub

Thanks! I guess I was on the right trail.

This is really the first thing I've written from (almost) scratch.

This place is amazing.

 

[jmpatrick]

Can I use wildcards to do partial matches? Tried this and it doesn't work:

If Me.SubLotCode = "*1125*" Then
Me.SubdivisionReturn = "AO - Arbor Oaks"
Me.FieldManagerReturn = "Shaun Jaward"

Perhaps a better way is looking only at the first four characters from the left. I need 1125 to work even if it's 1125-99.

 

[Rick Rothstein]

You can use wildcards with the Like operator. For the first four characters to be 1125...

If Me.SubLotCode Like "1125*" Then

 

[jmpatrick]

You can use wildcards with the Like operator. For the first four characters to be 1125...

If Me.SubLotCode Like "1125*" Then

Thanks! Learned something else new today.