Excel only when VBA is enabled it should be possible to save the file, possible?

Question

I have a file that I made with three levels of permissions.
You get prompted to input a password on a userform and you get either admin rights, read/write or only read permissions.
That works fine, unless you don't enable macros.

If you don't enable the macro prompting the userform is not shown and the rights are not set thus you have full access.

I have seen you can use a splash screen but for that to work you need to save the workbook as far as I understand?
The basic concept of them is to beforeclose hide sheets and unhide splash screen, then save the workbook. But what if the user made a mistake and what to close without save and reopen later? The file gets closed with all sheets visible?

So I thought maybe I can use the "explorer -> right click -> properties -> Read-only" attribute as an extra layer of protection?

I found these functions.

ActiveWorkbook.ChangeFileAccess Mode:=xlreadonly
ActiveWorkbook.ChangeFileAccess Mode:=xlreadwrite

But I tried the readonly line and it did set read only, file could not be saved.
Then I closed the workbook without saving and opened again. But it seems the attribute was not set in the properties of the file since it was not tickmarked and when I opened the file I could save it.

Is there any other way to solve this?
I want to either "force" VBA on the user or make sure he/she can't save the file if it's opened without VBA.

The only way to force user to enable macros is to save your file as an Add-in and load it into Add-ins collection. — Maciej Los
– Maciej Los, Commented May 23, 2018 at 6:05
If the file is an add-in each user will have their own copy of the file in their add-in folder. And that won't work. The idea is to share the same file. — Andreas
– Andreas, Commented May 23, 2018 at 6:08
You can share data (not a file) between users by using Add-in ;) I've been doing it before successfuly. — Maciej Los
– Maciej Los, Commented May 23, 2018 at 6:09
I did think about that prior but the file is pretty large. it has about 20 sheets with cell colors everywhere and about 6 MB of data. It will very heavy to store the data externaly in a different file. But yes I have also done something similar earlier. — Andreas
– Andreas, Commented May 23, 2018 at 6:15
This (20 sheets, colored cells, etc.) changes nothing. I'd suggest to separate Bussines Logic Layer and Data Access Layer. This is the power of OOP. — Maciej Los
– Maciej Los, Commented May 23, 2018 at 6:21

Andreas · Accepted Answer · 2018-05-23 06:37:42Z

2

I found a solution that seems to work.

You can in Workbook_BeforeClose use this line to make the file read-only in file properties.

SetAttr Application.ActiveWorkbook.FullName, vbReadonly

This will set the tickmark in the properties and Excel will notice the file is writeprotected.

Then in Workbook_Open() (or as in my case when rights has been established)

SetAttr Application.ActiveWorkbook.FullName, vbReadwrite
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite

The first line removes the tickmark in file properties, but Excel still "remembers" the file as read-only.
The second line will tell Excel to make it ReadWrite and the file works as normal again.

answered May 23, 2018 at 6:37

Andreas

24k7 gold badges34 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chronocidal · Accepted Answer · 2018-05-23 10:21:35Z

This is a far more complicated method than Adreas', but doesn't feature the same risk of mildy tech-savvy users just right-clicking the file and popping into the "Properties" panel.

Create 2 new worksheets. One of them is set to xlVeryHidden - for the examples below, I have called it hsSheetStatus. The other, with a nice large notice telling your ~~hapless minion~~ colleague to enable Macros, will be changing visibility. I have called this hsEnableNotice.

Then I have a Macro to show hsEnableNotice hide all of the other sheets (storing their visibility and the ActiveSheet on hsSheetStatus), and a second Macro to do the reverse (restore visibility/ActiveSheet from hsSheetStatus) and set these to run on Workbook_BeforeSave, Workbook_AfterSave and Workbook_Open:

Option Explicit

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    UnlockAndShow
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    LockAndHide
End Sub

Private Sub Workbook_Open()
    UnlockAndShow
End Sub

Private Sub LockAndHide()
    Dim lSheet As Long, ActiveName As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ActiveName = ThisWorkbook.ActiveSheet.Name
    hsEnableNotice.Visible = xlSheetVisible
    hsEnableNotice.Activate

    hsSheetStatus.Range(hsSheetStatus.Cells(1, 1), hsSheetStatus.Cells(hsSheetStatus.Rows.Count, 1)).EntireRow.Delete

    For lSheet = 1 To ThisWorkbook.Sheets.Count 'By using Sheets instead of Worksheets, we include Charts etc
        hsSheetStatus.Cells(lSheet, 1).Value = ThisWorkbook.Sheets(lSheet).Name
        hsSheetStatus.Cells(lSheet, 2).Value = ThisWorkbook.Sheets(lSheet).Visible
        If ThisWorkbook.Sheets(lSheet).Name = ActiveName Then hsSheetStatus.Cells(lSheet, 3).Value = 1
        If ThisWorkbook.Sheets(lSheet).Name <> hsEnableNotice.Name Then ThisWorkbook.Sheets(lSheet).Visible = xlSheetVeryHidden
    Next lSheet

    ThisWorkbook.Protect Password:="ThisIsMyPassword.ThereAreManyLikeIt,ButThisOneIsMine.", Structure:=True, Windows:=False
    Application.EnableEvents = True
End Sub

Private Sub UnlockAndShow()
    Dim WasSaved As Boolean, lSheet As Long, lMax As Long
    WasSaved = ThisWorkbook.Saved
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    lMax = hsSheetStatus.Cells(hsSheetStatus.Rows.Count, 1).End(xlUp).Row

    ThisWorkbook.Unprotect Password:="ThisIsMyPassword.ThereAreManyLikeIt,ButThisOneIsMine."
    For lSheet = 1 To lMax
        ThisWorkbook.Sheets(hsSheetStatus.Cells(lSheet, 1).Value).Visible = hsSheetStatus.Cells(lSheet, 2).Value
        If hsSheetStatus.Cells(lSheet, 3).Value = 1 Then ThisWorkbook.Sheets(hsSheetStatus.Cells(lSheet, 1).Value).Activate
    Next lSheet

    hsSheetStatus.Visible = xlSheetVeryHidden
    hsEnableNotice.Visible = xlSheetVeryHidden

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ThisWorkbook.Saved = WasSaved
End Sub

I'll have a look at it but I think I earlier found a weakness on this type of method. Will give it a try as soon as I have time. Probably not today just to give you a heads up.
It does work. I was wrong. You used the before save and after save. I didn't think about that. I was thinking of before close, and that won't work. In the end I used a combination of the two methods. I show a splashscreen but the workbook is not completly locked down, there are still some sheets visible and the workbook is readonly in properties. I know you can get around it. But then again if someone who is not supposed to save the file does. I'll have a little chat with that person.

Collectives™ on Stack Overflow

Excel only when VBA is enabled it should be possible to save the file, possible?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related