Force Enable Macros for user (Small issue)

[IAIPICI]

Hey I have found some code on VBA Express which I really like. It is the smoothest running code to perform is task that I have come across, however I have a small problem that is annyoning me.

For orignal post: http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

If I close the the workbook and changes have been made it will, ofcourse promt me to save work. However, when this happens it automaticlly switches to the sheet I have created to be displayed when macros are disabled. I only what this to happen at the worksheet open stage if the macros are disabled. Having the screen change before people close it may confuse them.

Below is code submitted by johnske on VBA express

Option Explicit 
 
Private Sub Workbook_Open() 
     
    With Application 
         'disable the ESC key
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 
         
        Call UnhideSheets 
         
        .ScreenUpdating = True 
         're-enable ESC key
        .EnableCancelKey = xlInterrupt 
    End With 
     
End Sub 
 '
Private Sub UnhideSheets() 
     '
    Dim Sheet As Object 
     '
    For Each Sheet In Sheets 
        If Not Sheet.Name = "Prompt" Then 
            Sheet.Visible = xlSheetVisible 
        End If 
    Next 
     '
    Sheets("Prompt").Visible = xlSheetVeryHidden 
     '
    Application.Goto Worksheets(1).[A1], True '< Optional
     '
    Set Sheet = Nothing 
    ActiveWorkbook.Saved = True 
     
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    With Application 
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 
         
        Call HideSheets 
         
        .ScreenUpdating = True 
        .EnableCancelKey = xlInterrupt 
    End With 
End Sub 
 
Private Sub HideSheets() 
     '
    Dim Sheet As Object '< Includes worksheets and chartsheets
     '
    With Sheets("Prompt") 
         '
         'the hiding of the sheets constitutes a change that generates
         'an automatic "Save?" prompt, so IF the book has already
         'been saved prior to this point, the next line and the lines
         'relating to .[A100] below bypass the "Save?" dialog...
        If ThisWorkbook.Saved = True Then .[A100] = "Saved" 
         '
        .Visible = xlSheetVisible 
         '
        For Each Sheet In Sheets 
            If Not Sheet.Name = "Prompt" Then 
                Sheet.Visible = xlSheetVeryHidden 
            End If 
        Next 
         '
        If .[A100] = "Saved" Then 
            .[A100].ClearContents 
            ThisWorkbook.Save 
        End If 
         '
        Set Sheet = Nothing 
    End With 
     '
End Sub

Does anyone know if there is a way around this... thanks.

 

[Domenic]

Try...

http://xl-central.com/force-users-to-enable-macros-in-a-workbook.html

 

[IAIPICI]

Try...

http://xl-central.com/force-users-to-enable-macros-in-a-workbook.html

Thanks very much Domenic! You're a champion, it works a charm.

 

[Domenic]

You're very welcome! And thanks for the feedback!

 

[IAIPICI]

You're very welcome! And thanks for the feedback!

Domenic wondering if you or anyone else could help me again with this. There are a few little things I would like to add to the code. I am a complete novice when it comes to VB and have tried to do it myself with no success.

• When the workbook opens with macros enabled, I would like each sheet to align back to the top of the page. I have managed to get this to work with the following.

        Sub ShiftView()
        Sheets("February").Select
        Range("D5").Select
        Sheets("March").Select
        Range("D5").Select
        Sheets("April").Select
        Range("D5").Select
        Sheets("May").Select
        Range("D5").Select
        Sheets("June").Select
        Range("D5").Select
        Sheets("July").Select
        Range("D5").Select
        Sheets("August").Select
        Range("D5").Select
        Sheets("September").Select
        Range("D5").Select
        Sheets("October").Select
        Range("D5").Select
        Sheets("November").Select
        Range("D5").Select
        Sheets("December").Select
        Range("D5").Select
        Sheets("Editing").Select
        Range("A27").Select
        Sheets("January").Select
        Range("D5").Select        
                                                       
End Sub

• The above works fine and I have "called" it in the Workbook open sub which seems ok. But the main issue is I can't seem to get the spreadsheet to save whichever the last sheet was that was opened for it to be re-opened next time.
• Say for example: The "December" Sheet was opened when the workbook was last saved. I would like it to do the above procedure first and then open "December" Sheet.

Sorry to bug you with this too....

 

[Domenic]

Here are a couple of suggestions...

[Option 1]

Amend the code so that when saving, before it saves the workbook, it enters the name of the active sheet in an out of the way cell in one of your existing worksheets or in a new worksheet that would remain hidden at all times. Then, when the workbook is opened and after your ShiftView routine, it can activate the sheet referenced in your designated cell.

[Option 2]

In your Workbook_Open event, after your ShiftView routine, insert a line of code that activates a sheet in your workbook. Actually, probably two lines of code. One line to assign a uniquely named variable with a sheet name, and the other to activate the sheet. Then, when saving the workbook, before it actually saves it, the code would replace the string assigned to your variable in the Workbook_Open event with the name of the active sheet.

Note, however, this option would require access to the VBA project. Under the Macro Settings in the Trust Center, you would need to select the option to 'Trust Access to the VBA Project Object Model'. Then VBA can be used to manipulate and modify components in a VBA project.

If you decide to use one of the above options and you need help, post back.

 

[Domenic]

I just thought I'd let you know that I've updated the code on my webpage so that the active sheet doesn't change when saving the workbook without closing it. So if you can live with the fact that the workbook will always open at the same position, try my updated code. The changes to my code were made in the CustomSave routine.

 

[IAIPICI]

I just thought I'd let you know that I've updated the code on my webpage so that the active sheet doesn't change when saving the workbook without closing it. So if you can live with the fact that the workbook will always open at the same position, try my updated code. The changes to my code were made in the CustomSave routine.

I managed to fix that issue by looking at another thread, I added new dim for current sheet. Set it as CurSht = ActiveSheet in the Customer Save and activated it after the IF statement. That works well thanks.

But I still have the current problem. If I was to use the first option you have suggested above, would you be able to give me an example? thanks.

 

[IAIPICI]

I worked it out!!! For this individual spreadsheet the below code works fine. I just got it to select sheet depending on current month.

Sub ShtSelect()
Select Case Month(Now())
Case "1"
Sheets("January").Select
Case "2"
Sheets("Feburary").Select
Case "3"
Sheets("March").Select
Case "4"
Sheets("April").Select
Case "5"
Sheets("May").Select
Case "6"
Sheets("June").Select
Case "7"
Sheets("July").Select
Case "8"
Sheets("August").Select
Case "9"
Sheets("Septemeber").Select
Case "10"
Sheets("October").Select
Case "11"
Sheets("Novemeber").Select
Case "12"
Sheets("December").Select
End Select
End Sub

I call the ShiftView sub followed by the ShtSelect sub in the Workbook_Open sub and it always opens the correct sheet.

Thankyou once again Domenic for your assistance. This Thread has certainly now been resolved.

 

[shg]

Another way:

Sub ShtSelect()
    Worksheets(Format(Now, "mmmm")).Select
End Sub