?
Solved

Excel VBA "On change" function

Posted on 2000-04-19
5
Medium Priority
?
480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 200 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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