Get current cell after OK on user form?

[Gustaf]

I made a user form that opens when you double-click in certain cells. On
the form, you write some values, and then click OK. In the code for the
OK button, how do you know what cell the user double-clicked on?

Gustaf

 

[Per Jessen]

Hi Gustaf

This line needs to go before your code select other cells.

TargetCell= activecell.address

Regards,
Per

 

[Peter T]

Copy the worksheet code into a sheet module and add a userform with a button
for the form code. Double-click a cell within A1:B10.

' Worksheet code

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)

If Intersect(Range("A1:B10"), Target) Is Nothing Then Exit Sub
Cancel = True
Set UserForm1.mCellClicked = Target
UserForm1.Show

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

'' end worksheet module

'' userform code

Public mCellClicked As Range

Private Sub CommandButton1_Click() ' the OK button

MsgBox mCellClicked.Address & vbCr & _
mCellClicked.Parent.Name
End Sub

I suspect the above will be enough for your needs but you might also look
into Property Set/Let and Get pairs (Set for objects or Let for variables).

Regards,
Peter T