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
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 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
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies