VBA 'force' users to use macros combined with visible properties

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

trcstuart

Thread Starter
Joined
May 12, 2014
Messages
14
Hi,

I'm a complete novice so please forgive my ignorance! Here's what I'm trying to do: I have a document that is used by 2 departments as part of 1 process. However there is Revenue information on several tabs that really should only be shared with one of the departments. Originally my solution was this:


Code:
Private Sub Workbook_BeforeClose
 
On Error Resume Next
 
  Worksheets("Schedule").Activate
 
  Sheets("Summary").Visible = xlSheetHidden
  Sheets("Revenue").Visible = xlSheetHidden
  Sheets("ComCosts").Visible = xlSheetHidden
  Sheets("FinanceSum").Visible = xlSheetHidden
 
  ActiveWorkbook.ProtectPassword:="Password", structure:=True, 
Windows:=False
End Sub

This did 3 things:
1. Activated the "Schedule" tab as the default tab
2. 'Hide' the sensitive tabs incase one of the users forgot to hide them before closing
3. Password protect the workbook so only the users with the password can unlock the sensitive tabs

The problem is that if the user decides to disable macros, my code is euchred! So I found this amazing code from www.excelguru.ca (if that's not the proper way to cite, please let me know and I will adjust!) that 'forces' users to turn macros on in order to see any of the sheets:

Code:
Option Explicit
Const WelcomePage = "Macros"
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Author       : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Ensure that the macro instruction sheet is saved as the only
'               visible worksheet in the workbook
    Dim ws As Worksheet
    Dim wsActive As Worksheet
    Dim vFilename As Variant
    Dim bSaved As Boolean
    'Turn off screen flashing
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    'Record active worksheet
    Set wsActive = ActiveSheet
    'Save workbook directly or prompt for saveas filename
    If SaveAsUI = True Then
        vFilename = Application.GetSaveAsFilename( _
                    fileFilter:="Excel Files (*.xlsm), *.xlsm")
        If CStr(vFilename) = "False" Then
            bSaved = False
        Else
            'Save the workbook using the supplied filename
            Call HideAllSheets
            On Error Resume Next
            ThisWorkbook.SaveAs vFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            Select Case Err.Number
                Case Is = 1004
                    'User opted not to overwrite
                Case Else
                    MsgBox "Unknown error, file not saved."
                    bSaved = False
                    GoTo ExitPoint
            End Select
            On Error GoTo 0
 
            'Add file to most recent files list
            Application.RecentFiles.Add vFilename
            Call ShowAllSheets
            bSaved = True
        End If
    Else
        'Save the workbook
        Call HideAllSheets
        ThisWorkbook.Save
        Call ShowAllSheets
        bSaved = True
    End If
ExitPoint:
    'Restore file to where user was
    wsActive.Activate
    'Restore screen updates
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
 
    'Set application states appropriately
    If bSaved Then
        ThisWorkbook.Saved = True
        Cancel = True
    Else
        Cancel = True
    End If
End Sub
Private Sub Workbook_Open()
'Author       : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Unhide all worksheets since macros are enabled
    Application.ScreenUpdating = False
    Call ShowSomeSheets
    Application.ScreenUpdating = True
    ThisWorkbook.Saved = True
 
End Sub
Private Sub HideAllSheets()
'Author       : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Hide all worksheets except the macro welcome page
    Dim ws As Worksheet
    Worksheets(WelcomePage).Visible = xlSheetVisible
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
    Next ws
    Worksheets(WelcomePage).Activate
End Sub
Private Sub ShowAllSheets()
'Author       : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Show all worksheets except the macro welcome page
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
    Next ws
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden
    ActiveWorkbook.Protect Password:="unlock", structure:=True, Windows:=False
End Sub
What I want to do now is combine the two! I've tried a bunch of different things but I can't seem to get it to work. Can anyone point me in the right direction?

Thanks in advance!

Tyler
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hi Tyler,
I just took a quick look. I'm off mow but you could 'force' this by creating another file that serves as master, if macro's are disabled no choices possible. else open the the intended file.
I've used this method and works.
I'll try and help later this weekend.
BTW which version of Excel are you using?
 

trcstuart

Thread Starter
Joined
May 12, 2014
Messages
14
Hi Tyler,
I just took a quick look. I'm off mow but you could 'force' this by creating another file that serves as master, if macro's are disabled no choices possible. else open the the intended file.
I've used this method and works.
I'll try and help later this weekend.
BTW which version of Excel are you using?

Hi Hans,

Thanks for your speedy reply! I'm using Excel 2007.
Any further comments you could provide would be fantastic! Thanks again!

Tyler
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
I've made some small changes to the file (it was all in Dutch) and this is what it does.
This file is run and if macro's are disabled the buttons won't work.

What you do is open the attached file and make sure the macros are enabled.
When you press CONTINUE it will ask you for a file name, just follow the on screen instructions and select the file you wish to open with it.
Once a file is selected the name of the file will be displayed in the opening page and this file (the one I am sending you) will be renamed to Open-<and the file name you selected>.xlsm and it will be placed in the same folder.
All you have to do is have the users always use the Open.... file.

Try it out, there is not much explanation in the vba code and it was written by me in 2008 so a lot of the technique I use now is much improved but I never came to change this file.

Let me know it this a little your way.
The OPENPAGE worksheet is protected without a password
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

No members online now.
Top