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.

 

[Jaafar Tribak]

Try this and see if it works for you :

In the worksheet module:

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
            UserForm1.TextBox1 = Target.Value
            UserForm1.Show
        End If
    End If
End Sub

Change UserForm and TextBox names as required.

 

[JohnGow383]

Thwnls

Try this and see if it works for you :

In the worksheet module:

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
            UserForm1.TextBox1 = Target.Value
            UserForm1.Show
        End If
    End If
End Sub

Change UserForm and TextBox names as required.

Thanks for the reply but it's not working as I intend.
The problem is that when the userform is called the user is prompted to enter a number into text box1 which is then added to adjacent column. If they have pressed enter after adding value into column W then it will add the textbox value into correct cell immediately adjacent. But if they have clicked tab then it's one above.
I can account for both actions by having two identical userforms which can be called from the sheet code by comparing active cell vs last changed cell but this doesn't account for clicking elsewhere with mouse. I guess setting userform to modal =true will help. I need to be able to use the last changed cell variable inside the userform code but I honestly don't think that's possible

 

[Jaafar Tribak]

Ok - I think I understand what you want :

First, add a textbox to the userform and a commandbutton. The Textbox is obviously for taking the user input and the commandbutton is for adding the user's textbox input to the adjacent cell (one column to the right of the trigger cell).

Don't add any code in the UserForm Module.

In the worksheet module:

Option Explicit

Private WithEvents cmbtn As MSForms.CommandButton

Private Sub cmbtn_Click()
    With UserForm1
        If Len(.TextBox1.Text) Then
            Range(.Tag).Offset(0, 1).Value = .TextBox1.Text
        End If
    End With
End Sub

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 cmbtn = .CommandButton1
                .Tag = Target.Address(, , , True)
                .Show 'vbModeless  << use modal or modeless form.- works in both.
            End With
        End If
    End If
End Sub

The above should work regardless of whether the user used the Enter key after editing the trigger cell(s) , used the Tab key , used Mouseclick elswhere or used whatever.

The code should also work with Modal and Modeless forms.

Change the names of the UserForm, TextBox and CommandButton in the above code as required.

 

[JohnGow383]

Thanks.
Definitely getting closer.
It's doing as intended. It's populating correct cell.
I would like to be able to just press enter after the number has been entered and for it to populate and then unload the userform.
I achieved this in the userform code by using a textbox1_KeyUp sub and a statement if keycode =KeycodeConstants.vbReturn

Would I just add this code to the sheet code to achieve this?

Many thanks for the help thus far

 

[Jaafar Tribak]

You can do that. Just get rid of the commandButton and place 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)
                .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)
    If KeyCode = vbKeyReturn Then
        With UserForm1
            If Len(.TextBox1.Text) Then
                Range(.Tag).Offset(0, 1).Value = .TextBox1.Text
            End If
        End With
        Unload UserForm1
    End If
End Sub

BTW, when I said earlier on "Don't add any code in the UserForm Module", I should have said not adding code to the commandbutton only but you could obviously add any necessary code to the form or any other controls you may have.

Same now. You can add code to your userform and to any other controls if you have any.

 

[JohnGow383]

Thanks

You can do that. Just get rid of the commandButton and place 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)
                .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)
    If KeyCode = vbKeyReturn Then
        With UserForm1
            If Len(.TextBox1.Text) Then
                Range(.Tag).Offset(0, 1).Value = .TextBox1.Text
            End If
        End With
        Unload UserForm1
    End If
End Sub

BTW, when I said earlier on "Don't add any code in the UserForm Module", I should have said not adding code to the commandbutton only but you could obviously add any necessary code to the form or any other controls you may have.

Same now. You can add code to your userform and to any other controls if you have any.

Yeah I realized that as I was experimenting. I had managed to get it to work with pressing enter twice. I will try yours now. I'd used keyup instead of key down so will see. Sometimes I have issues with setting focus especially when modelless, I get around by selecting the wrong item first followed by the correct one. It's weird.

 

[JohnGow383]

You can do that. Just get rid of the commandButton and place 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)
                .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)
    If KeyCode = vbKeyReturn Then
        With UserForm1
            If Len(.TextBox1.Text) Then
                Range(.Tag).Offset(0, 1).Value = .TextBox1.Text
            End If
        End With
        Unload UserForm1
    End If
End Sub

BTW, when I said earlier on "Don't add any code in the UserForm Module", I should have said not adding code to the commandbutton only but you could obviously add any necessary code to the form or any other controls you may have.

Same now. You can add code to your userform and to any other controls if you have any.

Thanks very much for your help. This works perfectly.
The only other issue I have now is that in the UserForm.Caption and Label1.caption I have referened data (as PO number), which is also relative to the trigger cell.
The code for this is in the Userform Initialize section and is using the offset (assuming the user presses enter at trigger). Is there anyway to incorporate the Intialize code into the sheet code or will I just have to live with it. Don't worry if it's too much hassle, you've helped enough. Thanks

 

[JohnGow383]

Here is my Intialize code

Private Sub UserForm_Initialize()

Set ws1 = Sheet1
PR = "PR " & ws1.Cells(ActiveCell.Row, 2).Offset(-1, 0).Value & "-143" 'Assumes user presses enter after entering the quotation cost

UserForm3.Caption = PR & " - Enter Lead Time"

'TextBox1.Value = ws1.Cells(ActiveCell.Row, 24).Offset(-1, 0).Value (No longer using this)

If TextBox1.Value <> "" Then TextBox1.BackColor = &HC0FFFF
 
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 Sub

 

[Jaafar Tribak]

The UserForm.Caption and Label1.caption are relative to the trigger cell ...

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.