How to Reference Last Cell Changed for Use in UserForm Code

[JohnGow383]

Hello all,

I have a small issue.
I have a worksheet_change event that calls a UserForm to enter a value into a text box. The trigger range is in Column W range W2:W200. The textbox value is autofilled into a cell on the same row as the triggered Worksheet_Change row. I am currently using

TextBox1 = ws1.Cells(ActiveCell.Row, 24).Offset(-1, 0).Value

However, this will only work when the user presses enter on the trigger cell and it looks for an offset of -1 rows. If the value is deleted, or if the user is using tab or clicks away using the mouse, then the Textbox 1 value will fill into the wrong cell.
I can identify the last changed cell within the Sheet code by using a range variable

LastCell = Target

,

MsgBox LastCell.Row

however, I am not sure how to have this variable as a global setting so it can be used in my UserForm code.
Any ideas? Any workarounds.
Any help appreciated, ty.

 

[JohnGow383]

How many columns are those info away from W2:W200 ?
I will be leaving shortly so I will post back when I come back tonight.

Hi. Yeah don't worry there's no rush. The column that contains the Purchase Requisition number is in Column B or 2. Therefore it's 22 columns to the left of Trigger range with column W being No 1 and Column B being No 22. Thanks

 

[Jaafar Tribak]

Hi John,

Please, get rid of the code you have in the UserForm_Initialiize event (The one you showed in Post#9) and try the following :

In the Worksheet Module:

Option Explicit

Private WithEvents TxtBox As MSForms.TextBox

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Union(Target, Range("W2:W200")).Address = Range("W2:W200").Address Then
            With UserForm1
                Set TxtBox = .TextBox1
                .TextBox1.TabIndex = 0
                .Tag = Target.Address(, , , True)
                Call AddCaptions(Target)
                .Show 'vbModeless  '<< use modal or modless form.- works in both.
            End With
        End If
    End If
End Sub

Private Sub TxtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Application.OnTime Now, Me.CodeName & ".HighLightTextBox"
    If KeyCode = vbKeyReturn Then
        If Len(TxtBox.Text) Then
            Range(UserForm1.Tag).Offset(0, 1).Value = TxtBox.Text
        End If
        Unload UserForm1
    End If
End Sub

Private Sub HighLightTextBox()
    With TxtBox
        .BackColor = IIf(Len(.Value), &HC0FFFF, vbWhite)
    End With
End Sub

Private Sub AddCaptions(ByVal Target As Range)
    Dim PR As String

    PR = "PR " & Me.Cells(Target.Row, 2)
    With UserForm1
        .Caption = PR & " - Enter Lead Time"
        .Label1.Caption = Format(Now, "mmm d, yyyy") & ": " & vbNewLine & vbNewLine & _
        PR & " has received a quotation. " & vbNewLine & "Add Lead Time in days in the text box below." & vbNewLine & "Press Enter." & vbNewLine _
        & vbNewLine & "If LT is not known, remember to enquire and add into Column X." & vbNewLine & _
        "Add comments as required."
    End With
End Sub

The UserForm Module is left without any code in it hence, you now have a more compact code that is hosted in its entirety in one single module, (worksheet module) easier to manage and with no need to have messy global variables floating around different modules.

 

[JohnGow383]

Hi John,

Please, get rid of the code you have in the UserForm_Initialiize event (The one you showed in Post#9) and try the following :

In the Worksheet Module:

Option Explicit

Private WithEvents TxtBox As MSForms.TextBox

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Union(Target, Range("W2:W200")).Address = Range("W2:W200").Address Then
            With UserForm1
                Set TxtBox = .TextBox1
                .TextBox1.TabIndex = 0
                .Tag = Target.Address(, , , True)
                Call AddCaptions(Target)
                .Show 'vbModeless  '<< use modal or modless form.- works in both.
            End With
        End If
    End If
End Sub

Private Sub TxtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Application.OnTime Now, Me.CodeName & ".HighLightTextBox"
    If KeyCode = vbKeyReturn Then
        If Len(TxtBox.Text) Then
            Range(UserForm1.Tag).Offset(0, 1).Value = TxtBox.Text
        End If
        Unload UserForm1
    End If
End Sub

Private Sub HighLightTextBox()
    With TxtBox
        .BackColor = IIf(Len(.Value), &HC0FFFF, vbWhite)
    End With
End Sub

Private Sub AddCaptions(ByVal Target As Range)
    Dim PR As String

    PR = "PR " & Me.Cells(Target.Row, 2)
    With UserForm1
        .Caption = PR & " - Enter Lead Time"
        .Label1.Caption = Format(Now, "mmm d, yyyy") & ": " & vbNewLine & vbNewLine & _
        PR & " has received a quotation. " & vbNewLine & "Add Lead Time in days in the text box below." & vbNewLine & "Press Enter." & vbNewLine _
        & vbNewLine & "If LT is not known, remember to enquire and add into Column X." & vbNewLine & _
        "Add comments as required."
    End With
End Sub

The UserForm Module is left without any code in it hence, you now have a more compact code that is hosted in its entirety in one single module, (worksheet module) easier to manage and with no need to have messy global variables floating around different modules.

Hi, thanks for taken the time to do this. Very much appreciated.
I have tried the code however I'm getting an error. It may be my mistake not sure yet, but it's calling the userform okay and the labels are updating but when I type a number in its saying cannot run the macro HighlightedTextBox. The macro may not be available in this workbook or all macros are disabled. Any ideas?

 

[JohnGow383]

Hi John,

Please, get rid of the code you have in the UserForm_Initialiize event (The one you showed in Post#9) and try the following :

In the Worksheet Module:

Option Explicit

Private WithEvents TxtBox As MSForms.TextBox

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Union(Target, Range("W2:W200")).Address = Range("W2:W200").Address Then
            With UserForm1
                Set TxtBox = .TextBox1
                .TextBox1.TabIndex = 0
                .Tag = Target.Address(, , , True)
                Call AddCaptions(Target)
                .Show 'vbModeless  '<< use modal or modless form.- works in both.
            End With
        End If
    End If
End Sub

Private Sub TxtBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Application.OnTime Now, Me.CodeName & ".HighLightTextBox"
    If KeyCode = vbKeyReturn Then
        If Len(TxtBox.Text) Then
            Range(UserForm1.Tag).Offset(0, 1).Value = TxtBox.Text
        End If
        Unload UserForm1
    End If
End Sub

Private Sub HighLightTextBox()
    With TxtBox
        .BackColor = IIf(Len(.Value), &HC0FFFF, vbWhite)
    End With
End Sub

Private Sub AddCaptions(ByVal Target As Range)
    Dim PR As String

    PR = "PR " & Me.Cells(Target.Row, 2)
    With UserForm1
        .Caption = PR & " - Enter Lead Time"
        .Label1.Caption = Format(Now, "mmm d, yyyy") & ": " & vbNewLine & vbNewLine & _
        PR & " has received a quotation. " & vbNewLine & "Add Lead Time in days in the text box below." & vbNewLine & "Press Enter." & vbNewLine _
        & vbNewLine & "If LT is not known, remember to enquire and add into Column X." & vbNewLine & _
        "Add comments as required."
    End With
End Sub

The UserForm Module is left without any code in it hence, you now have a more compact code that is hosted in its entirety in one single module, (worksheet module) easier to manage and with no need to have messy global variables floating around different modules.

My apologies.
The code works perfectly. I'd misspelled the macro HighlightTextBox in the KeyDown sub as I'm typing the code manually from the phone.
It all works perfectly. I have added so it doesn't trigger when removing or deleting a value in column W. I have another worksheet change event that copies the row into another sheet and then deletes the row when a PO is canceled. When deleting a row manually it doesn't trigger the userform call but when in auto it did. So I added an error handler and it's fine now.

Thanks very much again for all the effort, it's very clean code and has a good few lines of code I've not come across before. I'm due you a drink sir