Attempting to get Office 365 Excel to work with two pieces of VBA code that seem to conflict?

%3CLINGO-SUB%20id%3D%22lingo-sub-1085890%22%20slang%3D%22en-US%22%3EAttempting%20to%20get%20Office%20365%20Excel%20to%20work%20with%20two%20pieces%20of%20VBA%20code%20that%20seem%20to%20conflict%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1085890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%20All%2C%20I%20have%20two%20pieces%20of%20code.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20first%20is%20a%20%22select%20and%20move%22%20piece%20of%20code%20to%20take%20a%20line%20from%20one%20worksheet%20and%20transfer%20it%20to%20another%20worksheet%20dependent%20on%20cell%20content%2C%20it%20also%20unlocks%20and%20then%20locks%20the%20worksheet%20to%20perform%20this%20task.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20second%20piece%20of%20code%20I%20have%20copied%20from%20an%20online%20post%20which%20is%20a%20%22save%20and%20close%22%20piece%20of%20code%20that%20will%20close%20the%20spreadsheet%20after%201%20minute%20of%20inactivity%20as%20I%20cannot%20use%20the%20first%20piece%20of%20code%20in%20%22shared%22%20mode.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EBoth%20codes%20works%20fine%20on%20their%20own%2C%20but%20when%20I%20try%20to%20combine%20the%20two%20the%20%22auto%20close%22%20piece%20of%20code%20throws%20up%20errors.%20How%20do%20I%20successfully%20combine%20the%20two%20codes%20to%20run%20consecutively%3F%20Sorry%20very%20new%20to%20coding%20do%20not%20have%20the%20knowledge%20to%20resolve%20this%20myself%2C%20I%20have%20read%20and%20tried%20multiple%20ideas%20on%20here%20but%20with%20no%20success.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20pasted%20the%20code%20I%20am%20using%20below%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%3CSTRONG%3E%3CU%3EWorksheet%201%20Code%20-%20is%20the%20code%20on%20worksheet%201%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22bbCodeBlock%20bbCodeBlock--screenLimited%20bbCodeBlock--code%22%3E%3CDIV%20class%3D%22bbCodeBlock-title%22%3ECode%3A%3C%2FDIV%3E%3CDIV%20class%3D%22bbCodeBlock-content%22%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%0A%0AIf%20Intersect(Target%2C%20Columns(12))%20Is%20Nothing%20Then%20Exit%20Sub%0AIf%20Target.Count%20%26gt%3B%201%20Then%20Exit%20Sub%0A%0A%0ASheet2.Unprotect%20%22trial1%22%0A%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0A%0AIf%20Target.Value%20%3D%20%22Yes%22%20Then%0A%20%20%20%20%20%20%20%20Target.EntireRow.Copy%20Sheet2.Range(%22A%22%20%26amp%3B%20Rows.Count).End(3)(2)%0A%20%20%20%20%20%20%20%20Target.EntireRow.Delete%0AEnd%20If%0A%0A%0ASheet2.Protect%20%22trial1%22%0A%0A%0AApplication.ScreenUpdating%20%3D%20True%0A%0A%0AEnd%20Sub%0A%0A%0ACloseTime%20As%20Date%0ASub%20TimeSetting()%0A%20%20%20%20CloseTime%20%3D%20Now%20%2B%20TimeValue(%2200%3A01%3A00%22)%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20Application.OnTime%20EarliestTime%3A%3DCloseTime%2C%20_%0A%20%20%20%20%20%20Procedure%3A%3D%22SavedAndClose%22%2C%20Schedule%3A%3DTrue%0AEnd%20Sub%0ASub%20TimeStop()%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20Application.OnTime%20EarliestTime%3A%3DCloseTime%2C%20_%0A%20%20%20%20%20%20Procedure%3A%3D%22SavedAndClose%22%2C%20Schedule%3A%3DFalse%0A%20End%20Sub%0ASub%20SavedAndClose()%0A%20%20%20%20ActiveWorkbook.Close%20Savechanges%3A%3DTrue%0AEnd%20Sub%3C%2FPRE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3E%3CU%3ETHISWORKBOOK%20CODE%20-%20is%20the%20code%20from%20the%20%22Thisworkbook%22%20module%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22bbCodeBlock%20bbCodeBlock--screenLimited%20bbCodeBlock--code%22%3E%3CDIV%20class%3D%22bbCodeBlock-title%22%3ECode%3A%3C%2FDIV%3E%3CDIV%20class%3D%22bbCodeBlock-content%22%3E%3CPRE%3EPrivate%20Sub%20Workbook_Open()%0AStartTimer%0AEnd%20Sub%0A%0A%0APrivate%20Sub%20Workbook_SheetChange(ByVal%20Sh%20As%20Object%2C%20ByVal%20Target%20As%20Range)%0AStartTimer%0AEnd%20Sub%3C%2FPRE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CSTRONG%3E%3CU%3ETHISWORKBOOK%20CODE%20-%20Module%20-%20is%20the%20code%20I%20have%20entered%20the%20module%20created%20fro%20%22Thisworkbook%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CDIV%20class%3D%22bbCodeBlock%20bbCodeBlock--screenLimited%20bbCodeBlock--code%22%3E%3CDIV%20class%3D%22bbCodeBlock-title%22%3ECode%3A%3C%2FDIV%3E%3CDIV%20class%3D%22bbCodeBlock-content%22%3E%3CPRE%3EConst%20idleTime%20%3D%2060%20'seconds%0ADim%20Start%0ASub%20StartTimer()%0AStart%20%3D%20Timer%0ADo%20While%20Timer%20%26lt%3B%20Start%20%2B%20idleTime%0ADoEvents%0ALoop%0AApplication.DisplayAlerts%20%3D%20False%0AActiveWorkbook.Close%20True%0AApplication.DisplayAlerts%20%3D%20True%0AEnd%20Sub%3C%2FPRE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CSPAN%3EAny%20help%20you%20can%20give%20is%20greatly%20appreciated%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESteve%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1085890%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1086918%22%20slang%3D%22en-US%22%3ERe%3A%20Attempting%20to%20get%20Office%20365%20Excel%20to%20work%20with%20two%20pieces%20of%20VBA%20code%20that%20seem%20to%20conflict%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1086918%22%20slang%3D%22en-US%22%3EGet%20rid%20of%20all%20code%20in%20Thisworkbook%20EXCEPT%20the%20Worksheet_Change%20routine%2C%20so%20delete%20everything%20starting%20with%20the%20line%3A%20CloseTime%20As%20Date%20up%20to%20the%20end.%3C%2FLINGO-BODY%3E
New Contributor

Hello All, I have two pieces of code.

The first is a "select and move" piece of code to take a line from one worksheet and transfer it to another worksheet dependent on cell content, it also unlocks and then locks the worksheet to perform this task.

The second piece of code I have copied from an online post which is a "save and close" piece of code that will close the spreadsheet after 1 minute of inactivity as I cannot use the first piece of code in "shared" mode.

Both codes works fine on their own, but when I try to combine the two the "auto close" piece of code throws up errors. How do I successfully combine the two codes to run consecutively? Sorry very new to coding do not have the knowledge to resolve this myself, I have read and tried multiple ideas on here but with no success.



I have pasted the code I am using below:

Worksheet 1 Code - is the code on worksheet 1

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Columns(12)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub


Sheet2.Unprotect "trial1"


Application.ScreenUpdating = False


If Target.Value = "Yes" Then
        Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
End If


Sheet2.Protect "trial1"


Application.ScreenUpdating = True


End Sub


CloseTime As Date
Sub TimeSetting()
    CloseTime = Now + TimeValue("00:01:00")
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
      Procedure:="SavedAndClose", Schedule:=True
End Sub
Sub TimeStop()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
      Procedure:="SavedAndClose", Schedule:=False
 End Sub
Sub SavedAndClose()
    ActiveWorkbook.Close Savechanges:=True
End Sub

THISWORKBOOK CODE - is the code from the "Thisworkbook" module

Code:
Private Sub Workbook_Open()
StartTimer
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
StartTimer
End Sub

THISWORKBOOK CODE - Module - is the code I have entered the module created fro "Thisworkbook"

Code:
Const idleTime = 60 'seconds
Dim Start
Sub StartTimer()
Start = Timer
Do While Timer < Start + idleTime
DoEvents
Loop
Application.DisplayAlerts = False
ActiveWorkbook.Close True
Application.DisplayAlerts = True
End Sub

Any help you can give is greatly appreciated?
Steve

1 Reply
Get rid of all code in Thisworkbook EXCEPT the Worksheet_Change routine, so delete everything starting with the line: CloseTime As Date up to the end.