[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

Excel VBA "On change" function

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
Okki
Asked:
Okki
  • 3
  • 2
1 Solution
 
calacucciaCommented:
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
 
calacucciaCommented:
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
 
OkkiAuthor Commented:
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
 
calacucciaCommented:
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
 
OkkiAuthor Commented:
"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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now