VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-303038%22%20slang%3D%22en-US%22%3EVBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303038%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BMy%20question%20now%20is%20what%20error%20is%20there%20in%20my%20code%20that%20it%20will%20not%20work%20with%202%20different%20actions%20all%20in%20the%20same%20sub%3F%20They%20both%20work%20independently%20when%20I%20test%20them%20out%2C%20but%20I%20cannot%20figure%20out%20how%20to%20get%20the%20entire%20macro%20to%20work%20simultaneously%20in%20the%20same%20sub.%20Does%20anyone%20know%20how%20to%20compile%20them%20properly%3F%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%20that%20I%20have%20put%20into%20excel%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20StatusTableRange%20As%20Range%3CBR%20%2F%3EDim%20StatusMyDateTimeRange%20As%20Range%3CBR%20%2F%3EDim%20StatusMyUpdatedRange%20As%20Range%3C%2FP%3E%3CP%3E'Your%20data%20Status%20table%20range%3CBR%20%2F%3ESet%20StatusTableRange%20%3D%20Range(%22N23%3AO500000%22)%3C%2FP%3E%3CP%3E'Check%20if%20the%20changed%20cell%20is%20in%20the%20data%20tabe%20or%20not.%3CBR%20%2F%3EIf%20Intersect(Target%2C%20StatusTableRange)%20Is%20Nothing%20Then%20Exit%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'Stop%20events%20from%20running%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3C%2FP%3E%3CP%3E'Column%20for%20the%20Status%20date%2Ftime%3CBR%20%2F%3ESet%20StatusMyDateTimeRange%20%3D%20Range(%22P%22%20%26amp%3B%20Target.Row)%3CBR%20%2F%3E'Column%20for%20last%20Status%20updated%20date%2Ftime%3CBR%20%2F%3ESet%20StatusMyUpdatedRange%20%3D%20Range(%22Q%22%20%26amp%3B%20Target.Row)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'Determine%20if%20the%20input%20date%2Ftime%20should%20change%3CBR%20%2F%3EIf%20StatusMyDateTimeRange.Value%20%3D%20%22%22%20Then%3C%2FP%3E%3CP%3EStatusMyDateTimeRange.Value%20%3D%20Now%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3E'Update%20the%20updated%20date%2Ftime%20value%3CBR%20%2F%3EStatusMyUpdatedRange.Value%20%3D%20Now%3C%2FP%3E%3CP%3E'Turn%20events%20back%20on%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'Timestamp%20Data%3CBR%20%2F%3EDim%20myTableRange%20As%20Range%3CBR%20%2F%3EDim%20myDateTimeRange%20As%20Range%3CBR%20%2F%3EDim%20myUpdatedRange%20As%20Range%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'Your%20data%20table%20range%3CBR%20%2F%3ESet%20myTableRange%20%3D%20Range(%22E23%3AL500000%22)%3C%2FP%3E%3CP%3E'Check%20if%20the%20changed%20cell%20is%20in%20the%20data%20tabe%20or%20not.%3CBR%20%2F%3EIf%20Intersect(Target%2C%20myTableRange)%20Is%20Nothing%20Then%20Exit%20Sub%3C%2FP%3E%3CP%3E'Stop%20events%20from%20running%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'Column%20for%20the%20date%2Ftime%3CBR%20%2F%3ESet%20myDateTimeRange%20%3D%20Range(%22B%22%20%26amp%3B%20Target.Row)%3CBR%20%2F%3E'Column%20for%20last%20updated%20date%2Ftime%3CBR%20%2F%3ESet%20myUpdatedRange%20%3D%20Range(%22R%22%20%26amp%3B%20Target.Row)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E'Determine%20if%20the%20input%20date%2Ftime%20should%20change%3CBR%20%2F%3EIf%20myDateTimeRange.Value%20%3D%20%22%22%20Then%3C%2FP%3E%3CP%3EmyDateTimeRange.Value%20%3D%20Now%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3E'Update%20the%20updated%20date%2Ftime%20value%3CBR%20%2F%3EmyUpdatedRange.Value%20%3D%20Now%3C%2FP%3E%3CP%3E'Turn%20events%20back%20on%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-303038%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303047%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303047%22%20slang%3D%22en-US%22%3E%3CP%3EGreat.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303043%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303043%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%2C%20that%20worked!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303039%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303039%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20not%26nbsp%3Bhe%20first%20occurrence%20of%20this%26nbsp%3Bline%20that's%20causing%20you%20issues%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%3EIf%20Intersect(Target%2C%20StatusTableRange)%20Is%20Nothing%20Then%20Exit%20Sub%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BCould%20do%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EIf%20Intersect(Target%2C%20StatusTableRange)%20Is%20Nothing%20Then%20GoTo%20LabelName%3C%2FPRE%3E%3CP%3EAnd%20then%20add%20LabelName%3A%20before%20the%202nd%20half%20of%20your%20macro%20runs.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
lillyg424
Occasional Contributor

 

 My question now is what error is there in my code that it will not work with 2 different actions all in the same sub? They both work independently when I test them out, but I cannot figure out how to get the entire macro to work simultaneously in the same sub. Does anyone know how to compile them properly? 

Here is the code that I have put into excel:

 

Private Sub Worksheet_Change(ByVal Target As Range)

 

Dim StatusTableRange As Range
Dim StatusMyDateTimeRange As Range
Dim StatusMyUpdatedRange As Range

'Your data Status table range
Set StatusTableRange = Range("N23:O500000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, StatusTableRange) Is Nothing Then Exit Sub

 

'Stop events from running
Application.EnableEvents = False

'Column for the Status date/time
Set StatusMyDateTimeRange = Range("P" & Target.Row)
'Column for last Status updated date/time
Set StatusMyUpdatedRange = Range("Q" & Target.Row)


'Determine if the input date/time should change
If StatusMyDateTimeRange.Value = "" Then

StatusMyDateTimeRange.Value = Now

End If

'Update the updated date/time value
StatusMyUpdatedRange.Value = Now

'Turn events back on
Application.EnableEvents = True


'Timestamp Data
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range


'Your data table range
Set myTableRange = Range("E23:L500000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False


'Column for the date/time
Set myDateTimeRange = Range("B" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("R" & Target.Row)


'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

'Turn events back on
Application.EnableEvents = True

 

End Sub

3 Replies

Is it not he first occurrence of this line that's causing you issues?

 

If Intersect(Target, StatusTableRange) Is Nothing Then Exit Sub

 Could do:

 

If Intersect(Target, StatusTableRange) Is Nothing Then GoTo LabelName

And then add LabelName: before the 2nd half of your macro runs. 

Thank you so much, that worked!

Great. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies