Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi,
I need some help with a macro to run before save on 2 sheets, please see the macro I have input but it is not working on save. macro is in sheet 8 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub and macro in sheet 3 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub I need both of these macros to run when I save on the separate sheets, can anyone help me on this matter Kind regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Santaviga.
A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook module, and there can only be one such macro in the workbook. Therefore, cancel the two procedures in the sheet modules and post the following code inyo the Thisworkbook module: '========= Option Explicit Private Sub Workbook_BeforeSave( _ ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim mySheets As Sheets Set mySheets = Me.Sheets(Array("Sheet8", "Sheet1")) For Each SH In mySheets SH.PageSetup.RightFooter = "Relief Shifts " _ & Format(Now, "dd-mmm-yy") Next SH End Sub '<<========= --- Regards. Norman "santaviga" wrote in message ... Hi, I need some help with a macro to run before save on 2 sheets, please see the macro I have input but it is not working on save. macro is in sheet 8 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub and macro in sheet 3 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub I need both of these macros to run when I save on the separate sheets, can anyone help me on this matter Kind regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Thanks for your response, I need the footers in both sheets to be different,
sheet 1 relief shifts and date Sheet 8 only the date. Any ideas "Norman Jones" wrote: Hi Santaviga. A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook module, and there can only be one such macro in the workbook. Therefore, cancel the two procedures in the sheet modules and post the following code inyo the Thisworkbook module: '========= Option Explicit Private Sub Workbook_BeforeSave( _ ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim mySheets As Sheets Set mySheets = Me.Sheets(Array("Sheet8", "Sheet1")) For Each SH In mySheets SH.PageSetup.RightFooter = "Relief Shifts " _ & Format(Now, "dd-mmm-yy") Next SH End Sub '<<========= --- Regards. Norman "santaviga" wrote in message ... Hi, I need some help with a macro to run before save on 2 sheets, please see the macro I have input but it is not working on save. macro is in sheet 8 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub and macro in sheet 3 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub I need both of these macros to run when I save on the separate sheets, can anyone help me on this matter Kind regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Tried the code but reporting abiguous error, I do have another before save
macro in the workbook also will this have anything to do with it? Mark "Norman Jones" wrote: Hi Santaviga. A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook module, and there can only be one such macro in the workbook. Therefore, cancel the two procedures in the sheet modules and post the following code inyo the Thisworkbook module: '========= Option Explicit Private Sub Workbook_BeforeSave( _ ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim mySheets As Sheets Set mySheets = Me.Sheets(Array("Sheet8", "Sheet1")) For Each SH In mySheets SH.PageSetup.RightFooter = "Relief Shifts " _ & Format(Now, "dd-mmm-yy") Next SH End Sub '<<========= --- Regards. Norman "santaviga" wrote in message ... Hi, I need some help with a macro to run before save on 2 sheets, please see the macro I have input but it is not working on save. macro is in sheet 8 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub and macro in sheet 3 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean) ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub I need both of these macros to run when I save on the separate sheets, can anyone help me on this matter Kind regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Santaviga.
============= Thanks for your response, I need the footers in both sheets to be different, sheet 1 relief shifts and date Sheet 8 only the date. ============= The footers were identical because that is what you showed, However, try: '========= Option Explicit Private Sub Workbook_BeforeSave( _ ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim SH2 As Object With Me Set SH = .Sheets("Sheet1") '<<==== CHANGE Set SH2 = .Sheets("Sheet3") '<<==== CHANGE End With SH.PageSetup.RightFooter = _ "Relief Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE SH2.PageSetup.RightFooter = _ "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE End Sub '<<========= ============= Tried the code but reporting abiguous error, I do have another before save macro in the workbook also will this have anything to do with it? ============= See my comment: A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook module, and there can only be one such macro in the workbook. You will need to amalgamate your existing Workbook_BeforeSave procedure with the above code. If you experience difficulty in amalgamating the two procedures, post the code for each in a response here. Incidentally, do you have a good reason for setting the footers before every save operation? --- Regards. Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi, I keep getting this ambigous name error with regards to private sub
workbook before save "Norman Jones" wrote: Hi Santaviga. ============= Thanks for your response, I need the footers in both sheets to be different, sheet 1 relief shifts and date Sheet 8 only the date. ============= The footers were identical because that is what you showed, However, try: '========= Option Explicit Private Sub Workbook_BeforeSave( _ ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim SH2 As Object With Me Set SH = .Sheets("Sheet1") '<<==== CHANGE Set SH2 = .Sheets("Sheet3") '<<==== CHANGE End With SH.PageSetup.RightFooter = _ "Relief Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE SH2.PageSetup.RightFooter = _ "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE End Sub '<<========= ============= Tried the code but reporting abiguous error, I do have another before save macro in the workbook also will this have anything to do with it? ============= See my comment: A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook module, and there can only be one such macro in the workbook. You will need to amalgamate your existing Workbook_BeforeSave procedure with the above code. If you experience difficulty in amalgamating the two procedures, post the code for each in a response here. Incidentally, do you have a good reason for setting the footers before every save operation? --- Regards. Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Here is the code I have input:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim SH2 As Object With Me Set SH = .Sheets("Sheet3") Set SH2 = .Sheets("Sheet8") End With SH.PageSetup.RightFooter = Format(Now, "dd-mmm-yy") SH2.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub "Norman Jones" wrote: Hi Santaviga. ============= Thanks for your response, I need the footers in both sheets to be different, sheet 1 relief shifts and date Sheet 8 only the date. ============= The footers were identical because that is what you showed, However, try: '========= Option Explicit Private Sub Workbook_BeforeSave( _ ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim SH2 As Object With Me Set SH = .Sheets("Sheet1") '<<==== CHANGE Set SH2 = .Sheets("Sheet3") '<<==== CHANGE End With SH.PageSetup.RightFooter = _ "Relief Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE SH2.PageSetup.RightFooter = _ "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE End Sub '<<========= ============= Tried the code but reporting abiguous error, I do have another before save macro in the workbook also will this have anything to do with it? ============= See my comment: A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook module, and there can only be one such macro in the workbook. You will need to amalgamate your existing Workbook_BeforeSave procedure with the above code. If you experience difficulty in amalgamating the two procedures, post the code for each in a response here. Incidentally, do you have a good reason for setting the footers before every save operation? --- Regards. Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Santaviga,
Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Santaviga,
Hi, I keep getting this ambigous name error with regards to private sub workbook before save As previously explained, only one Workbook_BeforeSave event procedure may reside in the Workbook's ThisWorkbook module. Your error suggests that you have more than one such procedure. In such case, as explained, it will be necessary either to delete one of the procedures or, more probably, amalgamate the code to produce a single routine. --- Regards. Norman |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Change:
"Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") I don't think so... notice the line continuation character (the trailing underbar character) on the preceding line... the & is there to concatenate the output from the Format command with that preceding continued line. Rick |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Have you got any ideas on the code Rick.
Thanks "Rick Rothstein (MVP - VB)" wrote: Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") I don't think so... notice the line continuation character (the trailing underbar character) on the preceding line... the & is there to concatenate the output from the Format command with that preceding continued line. Rick |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
This has been done but still error.
"Norman Jones" wrote: Hi Santaviga, Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Rick,
============= Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") I don't think so... notice the line continuation character (the trailing underbar character) on the preceding line... the & is there to concatenate the output from the Format command with that preceding continued line. ============= I was responding. belatedly, to the OP's request: Thanks for your response, I need the footers in both sheets to be different, sheet 1 relief shifts and date Sheet 8 only the date The instruction: Format(Now, "dd-mmm-yy") returns a date string of the type 30-Apr-08 - as requested. What do you disagree with precisely --- Regards. Norman |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Santaviga,
Try to copy / paste the code from the Thisworkbook module. --- Regards. Norman "santaviga" wrote in message ... This has been done but still error. "Norman Jones" wrote: Hi Santaviga, Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
What do you disagree with precisely
Your message said this... Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") The only difference I see is in what you posted as the change was the removal of the ampersand (the Format statements appear to be identical), so I assumed you were "correcting" what you thought was a "extra" ampersand on the line. My response was saying that the ampersand is necessary because of the line continuation character. Rick |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Santaviga,
Have you got any ideas on the code Rick. Without in any way wishing to pre-empt any reponse from Rick, the suggested code works for me without any problem --- Regards. Norman |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Rick,
If you backtrack in the thread, you will see that I had suggested: =========== [...] SH2.PageSetup.RightFooter = _ "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE End Sub ========== Noting the OP's desire only to display the date in that particular footer, I suggested: =========== Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") =========== The result of such a change, if implemented, would be to return the following instruction: SH2.PageSetup.RightFooter = _ Format(Now, "dd-mmm-yy") which would appear to present no syntactic problems and works for me. --- Regards. Norman |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Yes, I see what you meant now... I think I became fixated on the
"<<===CHANGE" comment and missed the bigger point of your message. Sorry for the distraction in this thread. Rick "Norman Jones" wrote in message ... Hi Rick, If you backtrack in the thread, you will see that I had suggested: =========== [...] SH2.PageSetup.RightFooter = _ "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE End Sub ========== Noting the OP's desire only to display the date in that particular footer, I suggested: =========== Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") =========== The result of such a change, if implemented, would be to return the following instruction: SH2.PageSetup.RightFooter = _ Format(Now, "dd-mmm-yy") which would appear to present no syntactic problems and works for me. --- Regards. Norman |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Norman,
I have copied and pasted the code, returning ambiguous error name in before save, as I said I have another before save macro running, will this affect the code? Regards "Norman Jones" wrote: Hi Santaviga, Try to copy / paste the code from the Thisworkbook module. --- Regards. Norman "santaviga" wrote in message ... This has been done but still error. "Norman Jones" wrote: Hi Santaviga, Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Hi Santaviga,
You misunderstand me! Please copy all the code from the Thisworkbook module and paste it into a reply in this thread. In this way, we can see all of your code and give you a definitive version to resolve your problem. --- Regards. Norman "santaviga" wrote in message ... Hi Norman, I have copied and pasted the code, returning ambiguous error name in before save, as I said I have another before save macro running, will this affect the code? Regards "Norman Jones" wrote: Hi Santaviga, Try to copy / paste the code from the Thisworkbook module. --- Regards. Norman "santaviga" wrote in message ... This has been done but still error. "Norman Jones" wrote: Hi Santaviga, Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Sorry for the misunderstanding, seemingly you cant have 2 before save events
or you will get an ambiguous error, any ideas on resloving. Below is all my code. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Call ProtectAllSheets End Sub Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="****" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="****" Next n Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim SH2 As Object With Me Set SH = .Sheets("Sheet3") Set SH2 = .Sheets("Sheet8") End With SH.PageSetup.RightFooter = Format(Now, "dd-mmm-yy") SH2.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub Many thanks "Norman Jones" wrote: Hi Santaviga, You misunderstand me! Please copy all the code from the Thisworkbook module and paste it into a reply in this thread. In this way, we can see all of your code and give you a definitive version to resolve your problem. --- Regards. Norman "santaviga" wrote in message ... Hi Norman, I have copied and pasted the code, returning ambiguous error name in before save, as I said I have another before save macro running, will this affect the code? Regards "Norman Jones" wrote: Hi Santaviga, Try to copy / paste the code from the Thisworkbook module. --- Regards. Norman "santaviga" wrote in message ... This has been done but still error. "Norman Jones" wrote: Hi Santaviga, Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Also on this the subscript is out of range.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim SH2 As Object With Me Set SH = .Sheets("Sheet3") Set SH2 = .Sheets("Sheet8") End With SH.PageSetup.RightFooter = Format(Now, "dd-mmm-yy") SH2.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub "santaviga" wrote: Sorry for the misunderstanding, seemingly you cant have 2 before save events or you will get an ambiguous error, any ideas on resloving. Below is all my code. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Call ProtectAllSheets End Sub Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Protect Password:="****" Next n Application.ScreenUpdating = True End Sub Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="****" Next n Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim SH As Object Dim SH2 As Object With Me Set SH = .Sheets("Sheet3") Set SH2 = .Sheets("Sheet8") End With SH.PageSetup.RightFooter = Format(Now, "dd-mmm-yy") SH2.PageSetup.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy") End Sub Many thanks "Norman Jones" wrote: Hi Santaviga, You misunderstand me! Please copy all the code from the Thisworkbook module and paste it into a reply in this thread. In this way, we can see all of your code and give you a definitive version to resolve your problem. --- Regards. Norman "santaviga" wrote in message ... Hi Norman, I have copied and pasted the code, returning ambiguous error name in before save, as I said I have another before save macro running, will this affect the code? Regards "Norman Jones" wrote: Hi Santaviga, Try to copy / paste the code from the Thisworkbook module. --- Regards. Norman "santaviga" wrote in message ... This has been done but still error. "Norman Jones" wrote: Hi Santaviga, Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro
Got it sorted thanks for your help.
Many thanks Mark "Norman Jones" wrote: Hi Santaviga, You misunderstand me! Please copy all the code from the Thisworkbook module and paste it into a reply in this thread. In this way, we can see all of your code and give you a definitive version to resolve your problem. --- Regards. Norman "santaviga" wrote in message ... Hi Norman, I have copied and pasted the code, returning ambiguous error name in before save, as I said I have another before save macro running, will this affect the code? Regards "Norman Jones" wrote: Hi Santaviga, Try to copy / paste the code from the Thisworkbook module. --- Regards. Norman "santaviga" wrote in message ... This has been done but still error. "Norman Jones" wrote: Hi Santaviga, Change: "Other Shifts " _ & Format(Now, "dd-mmm-yy") '<<==== CHANGE to Format(Now, "dd-mmm-yy") --- Regards. Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |