CODEWITHSUNDEEP
vbaexcel
Andreas
Andreas

Reputation: 23958

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

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.

Upvotes: 0

Views: 344

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

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

Upvotes: 1

Andreas
Andreas

Reputation: 23958

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.

Upvotes: 2

Related Questions