Only allow binary format

[tracycamp]

I am trying to set up a file so that the user can only save it as a binary file format. Obviously if I save it as a binary file and the user saves the file, it will still be binary but I can't figure out a way to force it to binary if the user does save as. I've tried a couple of methods using file format of 50 on beforesave and aftersave events but it either doesn't work or crashes Excel.

Thanks,

Tracy

 

[mole999]

Maybe > https://www.rondebruin.nl/win/s5/win001.htm

 

[tracycamp]

Maybe > https://www.rondebruin.nl/win/s5/win001.htm

I previously tried a modified version of that, but it creates a copy of the file in a specified format of the file. The user can still use saveas and save the file to any version they select. I need to be able disable saving to file types other than binary.

 

[Tetra201]

Maybe just disable Save As? Like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = True Then
        Cancel = True
        MsgBox "   You cannot use 'Save As' with this workbook.", vbCritical
    End If
End Sub

 

[tracycamp]

Thanks! I think I can work with that. I would still like the user to be able to save as, but they will just have to copy and paste the file and then rename. Interestingly, I've disabled save in the past but when I previously tried to disable saveas, it didn't work. Not sure what the difference is between this code and what I used before, but this works.

 

[mole999]

if they open with macros disabled then no macro can run as planned

 

[tracycamp]

if they open with macros disabled then no macro can run as planned

In case its ever useful for you, I thought I'd say (I think) I've handled that scenario by protecting the VBA with a password and having code that executes on close which hides the sheets with xlVeryHidden. If they then open with macros disabled, the code that runs on open which unhides the sheets doesn't execute and they can't manually unhide the sheets since they are veryhidden. They could then save to a different format but the veryhidden property on the sheets would still be there and they couldn't view anything. I wanted to save as binary file so that they can't get around this by opening in Google sheets or OpenOffice. I've also password protected the file as an additional safeguard.

The project I'm working on is to create a method for distributing Excel files so that they are only available for a specified time period and after that time elapses, a macro deletes the file's content. I'm also trying to work out a way so that the file's password changes after the time period expires.

If you can think of any holes in my armor, I'd love to hear your ideas. Right now, I think the only option would be to screen capture the info or just write it down, but there's no way around that. They might be able to query the data from the file, but I think I can prevent that too.

 

[mole999]

if you had a workbook open event that deletes sheets after a set date, then performs a save, and have that the only macro in the workbook, get most of your macros to work from the sheets themselves, which will take the majority of code out. Obviously have very good back ups with locked routines that won't be actioned. Once its gone you don't evenneed to worry about the password change