How to combine two pieces of code on one spreadsheet

Steve_f

New Member
Joined
May 28, 2014
Messages
31
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
 
Last edited by a moderator:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,559
Office Version
  1. 365
Platform
  1. Windows
@Steve_f
I have updated you op, in future please use the test board for testing, not the main forum.
Cheers
 
Last edited:

Steve_f

New Member
Joined
May 28, 2014
Messages
31
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 28, 2014
Messages
31
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734

ADVERTISEMENT

Have you seen this way of sharing workbooks?
 

Steve_f

New Member
Joined
May 28, 2014
Messages
31
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,048
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top