Run macro when cell is selected

  • Thread starter Thread starter Corey
  • Start date Start date
C

Corey

I know how to rtun a macro if a cell value is changed like :
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring has changed!
'Do whatever you need to do...
End If
End SubBut how can i have a macro run if the cell is simply Selected instead ?Corey....
 
Thanks Dave.
You can tie into a different event: Worksheet_SelectionChange
 
Is there a way to NOT run the code i ended up with IF there is MORE THAN 1 cell selected ?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B5")) Is Nothing Then
Exit Sub
Else
UserForm2.Show
End If
End Sub

I use another macro to email the sheet, but as the B5 cell is inside the sheet range it rund the
macro again.

Is there a way around this ?

Corey....



You can tie into a different event: Worksheet_SelectionChange
 
you can give this a try


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Intersect(Target, Range("B5")) Is Nothing Then
Exit Sub
Else
UserForm2.Show
End If
end if
End Sub
 
Gary,
Thanks for the reply.

The macro does not seem to run when i use it now ??
Even if the B5 is the ONLY cell selected.

"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
you can give this a try


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Intersect(Target, Range("B5")) Is Nothing Then
Exit Sub
Else
UserForm2.Show
End If
end if
End Sub
 
as soon as i select B5, the userform is displayed. if i select any other cell or
select more than one cell along with B5 it does not display.
 
That's strange.
I can selecte ANY cell including B5 and i do not get the userform with :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Intersect(Target, Range("B5")) Is Nothing Then
Exit Sub
Else
UserForm2.Show
End If
End If
End Sub

If i use :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B5")) Is Nothing Then
Exit Sub
Else
UserForm2.Show
End If
End Sub
I get the userform when selecting B5 but ALSO get it if i select a number of cells including B5 in
them.

Why would this be ?

Corey....
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
as soon as i select B5, the userform is displayed. if i select any other cell or
select more than one cell along with B5 it does not display.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Junp start my Select Case Macro 12
Trying to run excel vba code when cell value changes from empty to entered value. 1
Datavalidation with shrinking non blank values 1
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
Run Macro If Cell have "x" value 0
Change_Event troubles, no error & no work. 6
Change_Event not responding to change made by formula. 11
Is it possible have a message ( visual or Audio ) when the Curser is place in a cell? 4

Back
Top