Solved

Excel VBA "On change" function

Posted on 2000-04-19
5
469 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
Outlook Free & Paid Tools
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now