Is it possible to make a macro-enabled template file that creates read-only workbooks?

gijimbo

Board Regular
Joined
Feb 3, 2010
Messages
130
As the subject says, I'd like to create a macro-enabled template that stems read-only workbooks. The workbooks should/will generally never be saved as they're just used as calculators. I started with a macro-enabled workbook but everyone in our team has expressed desire to have multiple instances of the "calculator" workbook open at the same time. I thought I could solve this by making it a macro-enabled template but then each stemmed workbook always asks if you want to save it when closed which can be a pretty big annoyance as much as we use the calculator.

Here's the code I've been using for the original workbook but with the template I'm getting a 1004 error: "method 'changefileaccess' of object '_workbook' failed". It makes sense to me that you couldn't change the file access if the file hasn't been saved yet but now I don't have a way to stop the save requests. Any ideas?

VBA Code:
Private Sub Workbook_Open()

    ActiveWorkbook.EnableAutoRecover = False
   
    If Range("UserName").Value = "" Then
        frmGetUser.Show
    ElseIf Range("UserName").Value = "Admin" Then
        'ThisWorkbook.ChangeFileAccess Mode:=xlReadWrite
        MsgBox ("Administrative mode active")
    Else
        Application.DisplayAlerts = False
        ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
        Application.DisplayAlerts = True
    End If

End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
What if you went along these lines in your workbook class. You will need to be more creative if other changes occur that do not fire this event.

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ThisWorkbook.Saved = True
End Sub
 
Upvote 0
Thanks for your reply. Yeah, that's something I could try. It'd be nice if there was a little more permanent solution than telling Excel the workbook has been saved every time something changes though.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top