Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 02-26-2020, 11:47 AM
chiefofpool chiefofpool is offline
Member
 
Join Date: Mar 2008
Posts: 305
Default Tricky Excel question

Hi,

I'm trying to create a macro in Excel (Office 365), which will prompt when saving a workbook and will write the pathfile at the bottom of the sheet. This particular code seems to work

Sub Workbook_BeforeSave(ByVal SaveasUI As Boolean, cancel As Boolean)

Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftFooter = "&Z&F"
End With
Application.PrintCommunication = False

End Sub


However, I'm trying to have this macro available to all of my workbooks. I have tried copying the code in the Personal.xlsb section of the Visual Basic interface, but it doesn't seem to work.

Any ideas on how to make this work?

Thanks!
Reply With Quote
  #2  
Old 02-26-2020, 11:59 AM
BruteForce's Avatar
BruteForce BruteForce is offline
Member
SOA AAA
 
Join Date: Apr 2013
Studying for More Money
Favorite beer: Wurzel Bier
Posts: 11,812
Default

I know you can edit the default Excel template that opens when you open a blank Excel workbook. Maybe you could have the macro be in there by default?

Otherwise I haven't heard of a way to do this besides pasting in the macro into each workbook, which would mean you can't have any ".xlsx" workbooks.
__________________
ASA

Quote:
Originally Posted by Actuary321 View Post
I would really hate to bring Pokémon to a gun fight.
Reply With Quote
  #3  
Old 02-26-2020, 12:00 PM
BruteForce's Avatar
BruteForce BruteForce is offline
Member
SOA AAA
 
Join Date: Apr 2013
Studying for More Money
Favorite beer: Wurzel Bier
Posts: 11,812
Default

You could create a personal macro to put the footer in there and just run that for every workbook you open. Have it on your quick access toolbar and then it's just a click of a button each time you open a workbook.
__________________
ASA

Quote:
Originally Posted by Actuary321 View Post
I would really hate to bring Pokémon to a gun fight.
Reply With Quote
  #4  
Old 02-26-2020, 12:22 PM
Abused Student's Avatar
Abused Student Abused Student is offline
Member
SOA
 
Join Date: Feb 2007
Location: The Eighth Circle of Hell
Favorite beer: Cold and lots of it
Posts: 42,315
Default

I had a similar experience and will have to think for a bit. I had a slick one that disabled the F1 key opening the help and had it is a personal workbook but it wouldn't always take effect right off the bat until I changed something. Let me think.
__________________
GAME ON!!!!!!! Let your ness show. Join the D&D fun. Started but applications still accepted


Officially assigned the role of Dictator, 9/30/09. Bow to my whims.
Reply With Quote
  #5  
Old 02-26-2020, 12:27 PM
BruteForce's Avatar
BruteForce BruteForce is offline
Member
SOA AAA
 
Join Date: Apr 2013
Studying for More Money
Favorite beer: Wurzel Bier
Posts: 11,812
Default

Quote:
Originally Posted by Abused Student View Post
I had a similar experience and will have to think for a bit. I had a slick one that disabled the F1 key opening the help and had it is a personal workbook but it wouldn't always take effect right off the bat until I changed something. Let me think.
I would be interested in that! I hate when I hit the F1 key. I've considered just taking off my keyboard altogether.
__________________
ASA

Quote:
Originally Posted by Actuary321 View Post
I would really hate to bring Pokémon to a gun fight.
Reply With Quote
  #6  
Old 02-26-2020, 03:19 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 39,089
Default

I believe the problem is the Before Save is tied to the workbook and not the application

You need something like
Application.WorkbookBeforeSave (Wb, SaveAsUI, Cancel)

where Wb would be the workbook you're saving

https://docs.microsoft.com/en-us/off...bookbeforesave
Reply With Quote
  #7  
Old 02-26-2020, 03:21 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 39,089
Default

also- putting this in your personal macros will only do this when you save the file. If someone else saves the file, it won't trigger.
Reply With Quote
  #8  
Old 02-26-2020, 03:24 PM
Abused Student's Avatar
Abused Student Abused Student is offline
Member
SOA
 
Join Date: Feb 2007
Location: The Eighth Circle of Hell
Favorite beer: Cold and lots of it
Posts: 42,315
Default

Ok, I think what I found out is I had it in a module and it doesn't automatically look there. I had to put it in "Thisworkbook" for my personal workbook and then it worked... most of the time at least.
__________________
GAME ON!!!!!!! Let your ness show. Join the D&D fun. Started but applications still accepted


Officially assigned the role of Dictator, 9/30/09. Bow to my whims.
Reply With Quote
  #9  
Old 02-26-2020, 03:25 PM
Abused Student's Avatar
Abused Student Abused Student is offline
Member
SOA
 
Join Date: Feb 2007
Location: The Eighth Circle of Hell
Favorite beer: Cold and lots of it
Posts: 42,315
Default

Quote:
Originally Posted by BruteForce View Post
I would be interested in that! I hate when I hit the F1 key. I've considered just taking off my keyboard altogether.
Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
End Sub


That is what I used. I might have had to run it first for it to work but then it didn't matter what workbook you are in. Drove me nuts because it took a couple minutes to open the help.
__________________
GAME ON!!!!!!! Let your ness show. Join the D&D fun. Started but applications still accepted


Officially assigned the role of Dictator, 9/30/09. Bow to my whims.
Reply With Quote
  #10  
Old 02-26-2020, 03:49 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 39,089
Default

I did this (abbreviated)
Sub auto_open()


With Application
.OnKey "{F1}", "Z_EndProgram"
end with

end sub

where:
Sub Z_EndProgram()
End
End Sub
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 05:08 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.19627 seconds with 11 queries