Solved

Excel VBA "On change" function

Posted on 2000-04-19
5
475 Views
Last Modified: 2012-05-04
The following code reacts to changes made to any cell on the sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

I want a sub that starts only if a certain cell or range (named FNR) is changed.

Any suggestions ?
0
Comment
Question by:Okki
  • 3
  • 2
5 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 50 total points
ID: 2732710
Hi Okki,

Just use the Target variable from inside the Worksheet_SelectionChange Sub.

For example:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = Range("FNR").Address Then
MsgBox ("Hi")
End If
End Sub

Watch out: The Selection Change event will be activated every time you change the SELECTION, not the contents.

If you only want to trigger changes in contents of your specific range, use the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("FNR").Address Then
MsgBox ("Hi")
End If
End Sub

Hope this helps
Calacuccia
Calacuccia
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2732719
If using a range (named for example FNR) consisting of multiple cells, use the intersect method to see if your target (the changed cell or changed selection) intersects with your named range.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("FNR")) Is Nothing Then
MsgBox ("Hi")
End If
End Sub

Calacuccia
0
 
LVL 4

Author Comment

by:Okki
ID: 2732770
Thanks Calacuccia, the second alternative was the one I was looking for.

How/when would I use the first one ?

I thought it might be a code that showed a MsgBox when I selected (clicked in) a cell but noticed when I tried it that it wasn't.

That would be useful sometimes but this seemed to be something else that I could not figure out.

Thanks,
Johan
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 2732782
Hi Johan,

The first alternative (Worksheet_SelectionChange) should work anytime you click a cell which is corresponding to the range of your 'If... Then' condition.

To test this, you could eliminate the 'If Then' condition, and try this one:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
MsgBox ("Hi")
End Sub

Now, you'll get the message box "Hi" anytime you click another cell, or move the selection with cursor.

This property can also be used in certain applications. For example, you could imagine warning the user if he tries to enter certain cells, like e.g. "Don't enter text in this cell"

Calacuccia

Calacuccia
0
 
LVL 4

Author Comment

by:Okki
ID: 2733814
"warning the user if he tries to enter certain cells, like e.g. "Don't enter text in this cell" "

This is exactly what I meant by "would be useful sometimes" because sometimes they do things the wrong way and I want to prevent that.

I suppose I can always use the last example you gave me (with "If Not Application.Intersect") because it seems to work nomatter if the cell is merged or not which the first one doesn't.

The following example worked fine and with different messages on two areas of my sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("Export")) Is Nothing Then
    MsgBox ("Hi")
    End If
    If Not Application.Intersect(Target, Range("AA2")) Is Nothing Then
    MsgBox ("Hej Calacuccia!")
    End If
End Sub

Maybee this code is not clean enough and will take lots of space if I define many areas on the same sheet ?  Well, it works and that's most important.

Thanks again
Johan
Sweden
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
This article will show you how to use shortcut menus in the Access run-time environment.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question