Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Deactivate macros but reactivate again once another macro is run
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
BER BER is offline
external usenet poster
  
Posts: 29
Default Deactivate macros but reactivate again once another macro is run
I am very new to Macros. I have a workbook first sheet sign in sign out of
work and I have a macro attached to each day sign in and sign out (button)
but once the button is pushed once I want to deactive it(macro) for that week
but I use another macro to copy the weeks entries to sheet 2 on Fridays and
clear cells to get ready for the next weeks entries; now when I use the macro
to do this I also want to reactivate the ones I have deactivated. In other
words once someone has signed in on Monday I do not want them to be able to
use that button again till the following Monday after that weeks entries have
been moved and sheet reset but then want my macros to work again.Macro code
below for first sheet and undermeath code for 2nd sheet. Thanks in advance.
Sub currenttime1()
' Time Macro
' Macro recorded 19/04/2010 by cooganb
Sheets("Weekly Time Sheet").Unprotect Password:="working925"
t = Time()
Set Displaytime = Sheets(1).Range("e12")
Displaytime.Cells(1, 1) = Str(t)
Sheets("Weekly Time Sheet").Protect Password:="working925"
End Sub

Sub PostandClear()
'
' PostandClear Macro
' Macro recorded 16/04/2010 by cooganb
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("AMALGAMATED").Select
Sheets("AMALGAMATED").Unprotect Password:="working925"
Range("A6").Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Weekly Time Sheet").Select
Sheets("Weekly Time Sheet").Unprotect Password:="working925"
Range("D24:AL24").Select
Selection.Copy
Sheets("AMALGAMATED").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("AMALGAMATED").Protect Password:="working925"
Sheets("Weekly Time Sheet").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("E12").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E13").Select
Selection.ClearContents
Range("E15").Select
Selection.ClearContents
Range("E16").Select
Selection.ClearContents
Range("E18").Select
Selection.ClearContents
Range("E19").Select
Selection.ClearContents
Range("E20").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("G13").Select
Selection.ClearContents
Range("G15").Select
Selection.ClearContents
Range("G16").Select
Selection.ClearContents
Range("G18").Select
Selection.ClearContents
Range("G19").Select
Selection.ClearContents
Range("G20").Select
Selection.ClearContents
Range("I12").Select
Selection.ClearContents
Range("I13").Select
Selection.ClearContents
Range("I15").Select
Selection.ClearContents
Range("I16").Select
Selection.ClearContents
Range("I18").Select
Selection.ClearContents
Range("I19").Select
Selection.ClearContents
Range("I20").Select
Selection.ClearContents
Range("K12").Select
Selection.ClearContents
Range("K13").Select
Selection.ClearContents
Range("K15").Select
Selection.ClearContents
Range("K16").Select
Selection.ClearContents
Range("K18").Select
Selection.ClearContents
Range("K19").Select
Selection.ClearContents
Range("K20").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("M13").Select
Selection.ClearContents
Range("M15").Select
Selection.ClearContents
Range("M16").Select
Selection.ClearContents
Range("M18").Select
Selection.ClearContents
Range("M19").Select
Selection.ClearContents
Range("M20").Select
Selection.ClearContents
Range("O12").Select
Selection.ClearContents
Range("O13").Select
Selection.ClearContents
Range("O15").Select
Selection.ClearContents
Range("O16").Select
Selection.ClearContents
Range("O18").Select
Selection.ClearContents
Range("O19").Select
Selection.ClearContents
Range("O20").Select
Selection.ClearContents
Range("Q12").Select
Selection.ClearContents
Range("Q13").Select
Selection.ClearContents
Range("Q15").Select
Selection.ClearContents
Range("Q16").Select
Selection.ClearContents
Range("Q18").Select
Selection.ClearContents
Range("Q19").Select
Selection.ClearContents
Range("Q20").Select
Selection.ClearContents
Range("G12").Select
Sheets("Weekly Time Sheet").Protect Password:="working925"
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,203
Default Deactivate macros but reactivate again once another macro is run
Just check for the day of the week at the beginning of the routines:

If Weekday(Now(),1) < 2 Then
'not Monday, quit
Exit Sub
End If
....rest of your login code

and

If Weekday(Now(),1)<6 Then
'Not Friday, quit
Exit Sub
End If
.... the rest of your posting code

By the way - you can delete all of those ActiveWindow.ScrollColumn = #
lines of code in the posting code to clean it up some.

"Ber" wrote:

I am very new to Macros. I have a workbook first sheet sign in sign out of
work and I have a macro attached to each day sign in and sign out (button)
but once the button is pushed once I want to deactive it(macro) for that week
but I use another macro to copy the weeks entries to sheet 2 on Fridays and
clear cells to get ready for the next weeks entries; now when I use the macro
to do this I also want to reactivate the ones I have deactivated. In other
words once someone has signed in on Monday I do not want them to be able to
use that button again till the following Monday after that weeks entries have
been moved and sheet reset but then want my macros to work again.Macro code
below for first sheet and undermeath code for 2nd sheet. Thanks in advance.
Sub currenttime1()
' Time Macro
' Macro recorded 19/04/2010 by cooganb
Sheets("Weekly Time Sheet").Unprotect Password:="working925"
t = Time()
Set Displaytime = Sheets(1).Range("e12")
Displaytime.Cells(1, 1) = Str(t)
Sheets("Weekly Time Sheet").Protect Password:="working925"
End Sub

Sub PostandClear()
'
' PostandClear Macro
' Macro recorded 16/04/2010 by cooganb
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("AMALGAMATED").Select
Sheets("AMALGAMATED").Unprotect Password:="working925"
Range("A6").Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Weekly Time Sheet").Select
Sheets("Weekly Time Sheet").Unprotect Password:="working925"
Range("D24:AL24").Select
Selection.Copy
Sheets("AMALGAMATED").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("AMALGAMATED").Protect Password:="working925"
Sheets("Weekly Time Sheet").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("E12").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E13").Select
Selection.ClearContents
Range("E15").Select
Selection.ClearContents
Range("E16").Select
Selection.ClearContents
Range("E18").Select
Selection.ClearContents
Range("E19").Select
Selection.ClearContents
Range("E20").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("G13").Select
Selection.ClearContents
Range("G15").Select
Selection.ClearContents
Range("G16").Select
Selection.ClearContents
Range("G18").Select
Selection.ClearContents
Range("G19").Select
Selection.ClearContents
Range("G20").Select
Selection.ClearContents
Range("I12").Select
Selection.ClearContents
Range("I13").Select
Selection.ClearContents
Range("I15").Select
Selection.ClearContents
Range("I16").Select
Selection.ClearContents
Range("I18").Select
Selection.ClearContents
Range("I19").Select
Selection.ClearContents
Range("I20").Select
Selection.ClearContents
Range("K12").Select
Selection.ClearContents
Range("K13").Select
Selection.ClearContents
Range("K15").Select
Selection.ClearContents
Range("K16").Select
Selection.ClearContents
Range("K18").Select
Selection.ClearContents
Range("K19").Select
Selection.ClearContents
Range("K20").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("M13").Select
Selection.ClearContents
Range("M15").Select
Selection.ClearContents
Range("M16").Select
Selection.ClearContents
Range("M18").Select
Selection.ClearContents
Range("M19").Select
Selection.ClearContents
Range("M20").Select
Selection.ClearContents
Range("O12").Select
Selection.ClearContents
Range("O13").Select
Selection.ClearContents
Range("O15").Select
Selection.ClearContents
Range("O16").Select
Selection.ClearContents
Range("O18").Select
Selection.ClearContents
Range("O19").Select
Selection.ClearContents
Range("O20").Select
Selection.ClearContents
Range("Q12").Select
Selection.ClearContents
Range("Q13").Select
Selection.ClearContents
Range("Q15").Select
Selection.ClearContents
Range("Q16").Select
Selection.ClearContents
Range("Q18").Select
Selection.ClearContents
Range("Q19").Select
Selection.ClearContents
Range("Q20").Select
Selection.ClearContents
Range("G12").Select
Sheets("Weekly Time Sheet").Protect Password:="working925"
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
BER BER is offline
external usenet poster
  
Posts: 29
Default Deactivate macros but reactivate again once another macro is r
Thank you so much this code certainly worked today Monday and will test for
the rest of the week, you are a genius thanks a mill will report after Friday.

"JLatham" wrote:

Just check for the day of the week at the beginning of the routines:

If Weekday(Now(),1) < 2 Then
'not Monday, quit
Exit Sub
End If
...rest of your login code

and

If Weekday(Now(),1)<6 Then
'Not Friday, quit
Exit Sub
End If
... the rest of your posting code

By the way - you can delete all of those ActiveWindow.ScrollColumn = #
lines of code in the posting code to clean it up some.

"Ber" wrote:

I am very new to Macros. I have a workbook first sheet sign in sign out of
work and I have a macro attached to each day sign in and sign out (button)
but once the button is pushed once I want to deactive it(macro) for that week
but I use another macro to copy the weeks entries to sheet 2 on Fridays and
clear cells to get ready for the next weeks entries; now when I use the macro
to do this I also want to reactivate the ones I have deactivated. In other
words once someone has signed in on Monday I do not want them to be able to
use that button again till the following Monday after that weeks entries have
been moved and sheet reset but then want my macros to work again.Macro code
below for first sheet and undermeath code for 2nd sheet. Thanks in advance.
Sub currenttime1()
' Time Macro
' Macro recorded 19/04/2010 by cooganb
Sheets("Weekly Time Sheet").Unprotect Password:="working925"
t = Time()
Set Displaytime = Sheets(1).Range("e12")
Displaytime.Cells(1, 1) = Str(t)
Sheets("Weekly Time Sheet").Protect Password:="working925"
End Sub

Sub PostandClear()
'
' PostandClear Macro
' Macro recorded 16/04/2010 by cooganb
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("AMALGAMATED").Select
Sheets("AMALGAMATED").Unprotect Password:="working925"
Range("A6").Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Weekly Time Sheet").Select
Sheets("Weekly Time Sheet").Unprotect Password:="working925"
Range("D24:AL24").Select
Selection.Copy
Sheets("AMALGAMATED").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("AMALGAMATED").Protect Password:="working925"
Sheets("Weekly Time Sheet").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("E12").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E13").Select
Selection.ClearContents
Range("E15").Select
Selection.ClearContents
Range("E16").Select
Selection.ClearContents
Range("E18").Select
Selection.ClearContents
Range("E19").Select
Selection.ClearContents
Range("E20").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("G13").Select
Selection.ClearContents
Range("G15").Select
Selection.ClearContents
Range("G16").Select
Selection.ClearContents
Range("G18").Select
Selection.ClearContents
Range("G19").Select
Selection.ClearContents
Range("G20").Select
Selection.ClearContents
Range("I12").Select
Selection.ClearContents
Range("I13").Select
Selection.ClearContents
Range("I15").Select
Selection.ClearContents
Range("I16").Select
Selection.ClearContents
Range("I18").Select
Selection.ClearContents
Range("I19").Select
Selection.ClearContents
Range("I20").Select
Selection.ClearContents
Range("K12").Select
Selection.ClearContents
Range("K13").Select
Selection.ClearContents
Range("K15").Select
Selection.ClearContents
Range("K16").Select
Selection.ClearContents
Range("K18").Select
Selection.ClearContents
Range("K19").Select
Selection.ClearContents
Range("K20").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("M13").Select
Selection.ClearContents
Range("M15").Select
Selection.ClearContents
Range("M16").Select
Selection.ClearContents
Range("M18").Select
Selection.ClearContents
Range("M19").Select
Selection.ClearContents
Range("M20").Select
Selection.ClearContents
Range("O12").Select
Selection.ClearContents
Range("O13").Select
Selection.ClearContents
Range("O15").Select
Selection.ClearContents
Range("O16").Select
Selection.ClearContents
Range("O18").Select
Selection.ClearContents
Range("O19").Select
Selection.ClearContents
Range("O20").Select
Selection.ClearContents
Range("Q12").Select
Selection.ClearContents
Range("Q13").Select
Selection.ClearContents
Range("Q15").Select
Selection.ClearContents
Range("Q16").Select
Selection.ClearContents
Range("Q18").Select
Selection.ClearContents
Range("Q19").Select
Selection.ClearContents
Range("Q20").Select
Selection.ClearContents
Range("G12").Select
Sheets("Weekly Time Sheet").Protect Password:="working925"
End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deactivate a macro when used and reactivate again Ber Excel Discussion (Misc queries) 1 April 23rd 10 04:35 PM
Deactivate / Reactivate the Paste Function in excel missk Excel Programming 1 January 16th 08 08:02 AM
Deactivate automatic macros on import CLR Excel Programming 3 June 11th 07 07:17 PM
"Shared Workbook" seems to deactivate features like macros Steve Excel Discussion (Misc queries) 3 May 1st 07 05:11 PM
Deactivate then Reactivate UpdateLinks TOMB Excel Programming 2 February 24th 06 10:20 PM


All times are GMT +1. The time now is 05:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement