VBA When Opening File

[Andrew_UK]

Hi All,

I'm semi experienced with VBA codes but have never written one to run upon the document opening. I tried following this guide Run a Macro Automatically on Opening Excel Workbook - VBA Examples but couldn't get it to do what I want it to do.

1. Essentially I want users to have the option to be able to refresh the data (this will then save the document as part of the refresh, so that the next person won't need to).
2. However I don't want people to be able to manually save the document!! I've had experience with people messing around with these things before.

So obviously I can't just set to read only because then they can't save as part of the refresh. My idea was to write a Macro that runs when the document is opened; this needs to disable save and save as (hopefully with a polite message). I tried following this guide but couldn't get it working excel vba - How to Disable Save and Save As using VBA - Stack Overflow

Then the refresh macro I already have as this

Sub Refresh()ActiveWorkbook.Model.Refresh
Range("B2").Value = Now()
ActiveWorkbook.RefreshAll
ThisWorkbook.Save
MsgBox ("Refresh Complete")
End Sub

It just needs a line of code adding in to enable saving before saving and disable saving afterwards...

But how the heck do I get the saving when opening disabled? Is there some extra trick which I'm missing with getting a macro to run upon opening? Is there some extra trick I'm missing with getting controls disabled?

Thanks In Advance!! Andrew

 

[Jerry Sullivan]

Hi Andrew,

To disable the save through methods other than your RefreshThenSave macro, use the Workbook_BeforeSave event as noted in the thread from StackOverflow.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
 'your polite message
    MsgBox "To save the workbook, click the "Refresh & Save" button!"

End Sub

Your macro can save the workbook by temporarily disabling events...

Sub Refresh()
 Application.EnableEvents=False
 ActiveWorkbook.Model.Refresh
 Range("B2").Value = Now()
 ActiveWorkbook.RefreshAll
 ThisWorkbook.Save
 MsgBox ("Refresh Complete")
 Application.EnableEvents=True
End Sub

Note that this approach relies on the user enabling macros. Code is sometimes used to require the enabling of macros before any sheets can be unhidden. I'd avoid adding that complexity unless it's really necessary to protect against that.

 

[Andrew_UK]

Many thanks for your help Jerry.

I'm still struggling with getting the first code working though. The thread talked about it needing to be implemented in design mode and I can see an icon called design mode in the task bar represented by a set square, rule and pencil - I'm assuming this is it?

Regardless of whether I try saving in Design mode or not it seems not change things. The document ignores the macro and lets the user save with any changes. Is there an additional tip I'm missing to getting design mode to work?

 

[Jerry Sullivan]

The "Workbook_BeforeSave" code should be saved to the ThisWorkBook code module.
The "Refresh" code should be saved to a Standard code Module (like Module1)

You can avoid using the Design mode technique and simply run your Refresh macro to refresh and save the file with your code changes.

 

[Andrew_UK]

The "Workbook_BeforeSave" code should be saved to the ThisWorkBook code module.
The "Refresh" code should be saved to a Standard code Module (like Module1)

You can avoid using the Design mode technique and simply run your Refresh macro to refresh and save the file with your code changes.

Hi Jerry,

Thanks again for your help. Unfortunately I was still unable to get the BeforeSave section working. Is there a step I'm missing?

I replicated it in this simpler version and only made it so that the first macro writes the date into cell B2, it works, but the before save Macro doesn't. Here's the simple version which I dropped onto a file sharing site;

<a href=http://www.filedropper.com/vbatest1><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >upload files free</a></div>

If you could point out what step I'm missing and how to do that then I would really appreciate it.

Best Regards,

Andrew

 

[crash4test]

As mentioned by Jerry, the beforesave marco should saved in the ThisWorkbook section, not in a separate Module, in your example Module2.

 

[Andrew_UK]

As mentioned by Jerry, the beforesave marco should saved in the ThisWorkbook section, not in a separate Module, in your example Module2.

Ahhh brilliant. Now I get what you mean. I kept trying to select "in this workbook" on all the dropdown menus etc.

Your last post prompted me to try all the options on the "this workbook" section of the developer window, right click, view code, paste - bingo!! I never even knew you could do that.

You have both been so incredibly helpful and have taught me a lot through that Thank you!!