[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel VBA "On change" function

Posted on 2000-04-19
5
Medium Priority
?
481 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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