Worksheet SelectionChange

[dcoledc]

I am fairly new to VBA and have yet to figure out how to use the variables created when using a SelectionChange.

I have some code in a module that I only want to run if a selected range is changed. My thoughts are to create a global variable that is set within the SelectionChange module when and if the range is changed. Then test against that variable in my other module. The problem is, I don't know how to test whether the contents have changed. I don't get how you use the "Target" variable.

Let's say the range on the sheet is "R6:T10". How do I set my variable if the contents of those cells are changed?

 

[Jonmo1]

Target is a default object variable that refers to the cell(s) that changed.

Try using INTERSECT

If Not Intersect(Target, Range("R6:T10")) Is Nothing Then
    'Code to execute if something changed in R6:T10
Else
    'Do Nothing
End If

Hope that helps.

 

[Smitty]

You use a Change event to test if a cell's value has changed. The SelectionChange event is fired based on movement around the worksheet. He's some boilerplate Change event code. Check the comments for where to put your range:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[dcoledc]

Still can't get it. First, I set a global variable Change2 as Boolean. Then, I placed this code in my worksheet Mod:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("P6:T31")) Is Nothing Then
    Change2 = True
Else
    Change2 = False
End If
End Sub

Then in mod1, I placed this code:

If Change2 = True Then
      Do stuff
Else
      Do stuff
End If

No matter what, the variable gets set to True even though I have made no changes. Unless selecting one of the cells in the range constitutes a change.

What am I doing wrong?

 

[Jonmo1]

As Smitty pointed out..
Worksheet_SelectionChange is triggered simply by changing which cell is selected.
Simply moving from A1 to B1 triggers the selection change.
Whether you actually changed a value in a cell or not.

The Worksheet_Change event triggers when you actually change a value in a cell.

So use
Private Sub Worksheet_Change(ByVal Target As Range)


Also, the change event will not trigger a macro in a standard module.

Whatever it is you want to do when a cell changes, just do it within the worksheet_change code.
Don't bother trying to pass the event on to another macro...


Hope that helps.

 

[dcoledc]

Moving it to the WorkSheet Change sub worked.

Thanks!

 

[dcoledc]

If you are still out there, can I not run this code in the Worksheet Change Sub

Sheets("Standard Report").Shapes("TextBox 3").TextFrame.Characters.Text = "Save Changes"

I run this in a normal mod and it works fine, but in the worksheet mod I get an error saying "Unable to set the Text Property...."

P.S. Smitty, thanks for your help as well

 

[dcoledc]

Nevermind, I had the sheet protected