BeforeRightClick acts only on PREVIOUSLY clicked cell

Question

I'm very new to excel VBA, I've searched many posts and sites, but cannot find out why...

My Goal: Right click on a cell, a form with a textbox opens, the clicked cell's address is shown in the textbox.

I've tried some very standard code... like so:

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    UserForm1.Show
    With UserForm1.TextBox1
     .Text = Target.Address
    End With
    End Sub

Here's the bug (step by step of my test results)

1. I right click A1, the form opens, textbox displays null.
2. I right click D3, the form opens, NOW the textbox displays A1.
3. I right click E4, the form opens, NOW the textbox displays D3.

The textbox always displays the address of the previously right-clicked cell. I need the textbox to display the address of what I just right-clicked.

Please provide some insight on why this happens and how to correct this, thanks guys!

Answer 1

The event routine is okay, target is the cell you are right-clicking. However, you show the form before you write the address into it, and .show opens the form modal, that means you code continues only after closing the form. You simply have to revert this:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With UserForm1
        .TextBox1.Text = Target.Address
        .Show
    End With
end Sub