How to combine two pieces of code on one spreadsheet

[Steve_f]

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

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

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"

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

 

[sijpie]

Hi Steve,

please use code tags around your code (see example below in blue/red). That makes it easier to read the code. Some people will not even answer your post if you don't use the tags.

What are you trying to achieve when you say you want to combine the two macro's?

1. Do you want to close the sheet after you have made your change in the workbook?
2. Do you want to reset the timer after you have made a change?
3. Something else?

 

[Fluff]

@Steve_f
I have updated you op, in future please use the test board for testing, not the main forum.
Cheers

 

[Steve_f]

I am now forced to use the latest version of office 365 which does not allow teh sharing of the workbook in the old fashion. So this is now more urgent is anybody able to help me please? Thanks in advance Steve

 

[Yongle]

I am now forced to use the latest version of office 365 which does not allow teh sharing of the workbook in the old fashion. So this is now more urgent is anybody able to help me please? Thanks in advance Steve

Perhaps you did not see the questions in post#2 ....

What are you trying to achieve when you say you want to combine the two macro's?
1. Do you want to close the sheet after you have made your change in the workbook?
2. Do you want to reset the timer after you have made a change?
3. Something else?

 

[Steve_f]

Hi Yongle, I am using the spreadsheet to track work I have one macro sorting the work and moving it to another spreadsheet. The second macro is to work around the fact that in the new office 365 excel you cannot share a live version of the document, so the macro with save and close after 5 minutes as I am sharign this spreadsheet with 125 people.
Steve

 

[sijpie]

Have you seen this way of sharing workbooks?

How to Work With Multiple Users on an Excel 2016 Datasheet?

See how you can work with multiple users on an Excel 2016 datasheet. By default, an Excel worksheet can be opened by one user at the same time, but Excel files are often used as small and easy databases to enter and share information.

www.presentationpoint.com

 

[Steve_f]

Hi [U]sijpie,[/U] yes thank you I had already found that. I would still ike to pursue the "Auto save and close" optuin though and the spreadsheet is open to over 100 people and so do like to leave it open unfortunately. I am fairly knew and VBA so learning as I go along, it is frustrating but fun

 

[sijpie]

You are trying to setup a macro that will save/close the workbook after a specified time. The problem with Excel VBA macros is that they cannot run in the background. They run, and everything else will have to wait till they are finished. This has been an issue for many people who would have liked to do something similar to you. Wait for a specified time and then do something. Unfortunately this is not possible. Yes, you can set up a macro to wait. and that is what it will do. But then you can't work in the workbook in the meantime.

You could theoretically write a loop that waits and intercepts keystrokes, resets the timer when a keystroke is recorded and processed, and closes if nothing is done in x minutes. But then you would have to write a complete interface on what to do for each keypress. And what about mouse actions?

If you had a working system with the old style shared workbook, then it will really be a lot simpler to reestablish the legacy sharing as per the link.