Select Cell and open UserForm

[Rick S.]

I found this information that gets me started:
Use the selection change event.
'======
Right click on the sheet tab and select view code.

In the left dropdown at the top of the module select Worksheet
in the right dropdown at the top of the module select SelectionChange

this will place the declaration in the module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you can put your code here:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if Target.Address = "$A$1" then
Userform1.Show
End if
End Sub
'====== Thanks Tom Ogilvy
(From URL:
http://www.microsoft.com/office/com...335d46-bc1b-4706-aeda-28f4675a0af4&sloc=en-us )

What I would like to know is how can this be dynamic? Example: If a user
deletes a row, how or can the above event still function?
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Chip Pearson]

I'm not quite sure what you're asking. A worksheet will always have a cell
A1, and your code will always run if cell A1 (and only A1) is selected. You
might try using a defined name. Select your target cell on the worksheet and
give it a name. You do this by clicking on the name box (the box above the
"1" row number and the "A" column letter, to the left of the formula bar)
and typing in a name (no spaces allowed). Then, test the Name of the Target
cell. For example, the following code will show UserForm1 is the user clicks
on the cell with a Name of "TheCell".


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim S As String
On Error Resume Next
S = Target.Name.Name
On Error GoTo 0
If S = "TheCell" Then
UserForm1.Show
Else
' do nothing
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rick S.]

For not quite understanding my question, you hit it right on the head!
Works perfect, whether I add/delete rows and or columns.

I'd hug you, but, I'm a guy, your a guy, and well, you know....It would be
wierd.

Happy Holidays Chip!

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007