Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hi I am trying to use the Workshee

Status
Not open for further replies.

sabascal

IS-IT--Management
Aug 20, 2002
85
GB
Hi
I am trying to use the Worksheet_SelectionChange function to trigger an action when a particular cell is modified.
This particular cell address is:
Worksheets("Site Inspection").Range("PropertyName").Address

What's wrong in the code below?


Is there another way to trigger changes when only 1! cell is modified?

Thanks.

=======================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range(Target).Address = Worksheets("Site Inspection").Range("PropertyName").Address
MsgBox "Critical Cell Modified"
Else
MsgBox "Non-Critical Cell Modified"
End If

On Error Resume Next
End Sub
 
sabascal,

You do not want to use the selection change event to detect changes to the sheet -- use the Worksheet_Change event.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("PropertyName").Address
MsgBox "Critical Cell Modified"
Else 
MsgBox "Non-Critical Cell Modified"
End If
This is assuming that you have a range that is called "PropertyName"

Skip,
Skip@TheOfficeExperts.com
 
Thanks Skips,

But even if I paste :
=================================================
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "On cell has been modified"
End If
=================================================Nothing is poping up when I am modifying a cell.
Any idea why. I pasted the code in my worksheet Excel Object.
Anything Wrong?
 
My mistake I mityped my post.

My code is exactly the same as your. Nothing is happenning!
1) I pasted the exact same code in my worksheet Excel Object
2) I went to my "Site Inspection Form" sheet were I want to check for cell modification

Nothing happened.
 
Code is in the VBA Editor.
I am modifying from my Sheet in Excel
there is no break.
?
 
It will work!

Either, your code is suspended (in break -- clear by Run/Reset) or your code is NOT in the sheet object that you are changing. They are the ONLY 2 things that would cause the results you are experiencing.

De sure that you have ONLY ONE sub that is named Worksheet_Change in that Sheet Object.

Skip,
Skip@TheOfficeExperts.com
 
1) and 2) seems ok.
The sub should be called automatically if:
1) I paste is in my Excel Object
2) if i modify something in this excel object

Nevermind, I'll ask a collegue what's wrong.
Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top