Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

what takes precedence between change and selectionchange?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
susan - 24 Jan 2006 06:03 GMT
Hi

i have a single sheet with these 2 events:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)

Is there a particular order of execution between these two in excel?

Also how can I disable the selectionchange from executing if the user
deletes a value?  When the value is deleted it puts it back

Thank you
Susan Hayes

Private Sub Worksheet_Change(ByVal Target As Range)

Dim mytime
mytime = Now
On Error GoTo ErrHandler
If Target.Column <= 26 Then
            Application.EnableEvents = False
       If mytime > Range("O" & Target.Row).Value + 0.5 And Range("P"
& Target.Row).Value = 0 And _
       Range("H" & Target.Row).Value = Range("S" & Target.Row).Value
Then
       Range("C" & Target.Row).Value = "Historical"
       
   
       End If

End If

ErrHandler:
Application.EnableEvents = True
End Sub
Patrick Molloy - 25 Jan 2006 07:28 GMT
well you can't do both at the same time, so whatever is done first, that
event gets fired.

The only way to repopulate a cell with any value or formula is to have that
value/formula saved somewhere so that if the target cell value is "" you can
recover the data.
One way would be to have a hidden sheet. each time a valid entry id  made,
the value is also copied to the same cell in the hidden sheet. if a cells
value is deleted, then the 'saved' value is recovered. of course the copy
sheet doesn't have to be hidden.

In this example sheet1 is ny data entry sheet and sheet3 is the copy.put the
code in sheet1's code page

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
   If Trim(Target.Value) = "" Then
       Target = Worksheets("sheet3").Range(Target.Address)
   Else
       Worksheets("sheet3").Range(Target.Address) = Target
   End If
End Sub

enter a value into any cell on sheet1. look at sheet3 - see that value. back
in sheet1, change the value & check sheet3. in sheet1 delete the value...the
last good value appears. enter a space and the last good value appears

> Hi
>
[quoted text clipped - 33 lines]
> Application.EnableEvents = True
> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.