Force macro on plus a button



Could the codes presented in the tutorial in force macro on  be divided into two parts:

> "Reminder" to hide all other sheets and to suggest users to switch macro on and

> A button on the same page to hide "Reminder" but unhide all sheets, opening a specific sheet.

With my little knowledge, I made some changes but the codes do not go well with prevent renaming command. Even if the file is renamed, we can access it by clicking on close button two or three times and,  clicking on cancel, even if not renamed, shows error.

Thank you.



I just hope I'm not irritating the experts here with my frequent questions.
Chhabi Acharya (rep: 109) Jan 28, '18 at 4:48 pm
The frequency of your questions isn't irritaing at all. You are very welcome! However, you might pay more attention to the quality of your requests. In this case you say that you would like to split the macro in the tutorial, but what you describe is to both change its function and add a button with an additional functionality. If both your requests were fulfilled the resulting workbook wouldn't resemble the original anymore. I wonder what other parts of your project you didn't sufficiently think about if, in fact, you don't seem to have realised that you are talking about a different project.
In essense, dear sir, you should invest more of your time in understanding and planning your idea before you ask to invest someone to invest his.
Variatus (rep: 3368) Jan 28, '18 at 11:35 pm
Thank you very much for your suggestion but as a beginner, it is difficult for me to recognize parts of a project. Sometimes I think they are identical or intertwined. Before asking the question above, I tried to combine codes I got through searches did some homework merging them but could reach nowhere. So, it will be a chance for me to learn through your help.
Chhabi Acharya (rep: 109) Jan 29, '18 at 1:00 am
Publish the code you wrote. Point to the problem you face. You will get help which will enable you to keep learning.
Variatus (rep: 3368) Jan 29, '18 at 2:55 am
So it seems to me like either you want the macro to do exactly what it is already doing. I don't see much difference. Having a button on the Reminder worksheet that unhides all the other worksheets will only work when the user has enabled macros, and, if they enabled macros, then the other sheets would already be visible in that example.

To make a navigation worksheet that has buttons pointing to other sheets, just make a macro for each worksheet and use this code: Worksheets("Sheet2").Activate where Sheet2 is the name of the worksheet and then assign that macro to the correct button in the worksheet. That is the easy way to do that.
don (rep: 1745) Jan 29, '18 at 7:57 am
Add to Discussion


Selected Answer

VBA can prevent the user from changing the name of a workbook. However, if the user knows how to disable VBA he can do whatever he wants. VBA is enabled by default when Excel starts. Frankly, I wouldn't draw the user's attention to the fact that he can sabotage VBA just before I use VBA to restrict his actions. Therefore I find the initial command to "Enable Macros" counter-productive.

I have modified your code in the 'ThisWorkbook' module to make it more readable and improve the functionality. Please replace the code you have with this one.

Option Explicit
Private Sub Workbook_Open()
    ' 30 Jan 2018
    With Worksheets("1")
        .Visible = True
    End With
    'worksheets to hide on startup
    Sheets("2").Visible = xlSheetVeryHidden
    Sheets("3").Visible = xlSheetVeryHidden
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' 30 Jan 2018
    If SaveAsUI Then
        MsgBox "You may not change the name of this workbook." & vbCr & _
               "Please close the workbook retaining its original name.", _
               vbExclamation, "Illegal action"
        Cancel = True
    End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 31 Jan 2018
    With ActiveWorkbook
        If Not .Saved Then
            Application.DisplayAlerts = False
            .Close SaveChanges:=True
            Application.DisplayAlerts = True
        End If
    End With
    ' All of this is done when you open the workbook:
    'worksheets to show when macro is enabled
'    Sheets("1").Visible = True
'    Sheets("2").Visible = xlSheetVeryHidden
'    Sheets("3").Visible = xlSheetVeryHidden
'    ActiveWorkbook.Close       ' This proc was called because Excel is
                                ' already closing the workbook.
'    Application.Quit           ' Don't Quit an application using code
                                ' which you didn't create using code.
End Sub

One particular logic I like to highlight for you is the fact that, in Excel, you can't save the workbook by another name unless you use the 'SaveAs' command. The 'Workbook_BeforeSave' event procedure can detect that and cancel the Save action if it was initiated using 'SaveAs'.



Your suggestion is priceless! I'd never thought of that.
Could you tell me why the codes still show error when cancel is clicked at closing the file.
Chhabi Acharya (rep: 109) Jan 30, '18 at 7:32 pm
Haha. I have no idea. I modified the 'BeforeClose' procedure above to prevent the crash. The workbook will be saved and closed even if you press Cancel. However, I invite you to consider the disadvantage of forced saving. In a standard workbook Excel will ask you if you want to save and if you click "No" you can discard the changes. There is "double security" advocated at this link. My personal inclination is to trust the user to (a) pay attention and (b) know what he wants. Therefore I would delete the entire 'BeforeClose' procedure and not interfere with the way Excel manages this problem.
Variatus (rep: 3368) Jan 30, '18 at 8:24 pm
It seems that you should let your 'BeforeClose' procedure handle the entire process of saving and closing or not at all. The mixture of Excel's ideas and your own is what causes an issue. Here is more code on ther subject.
Variatus (rep: 3368) Jan 30, '18 at 8:28 pm
Thank you very much!
Chhabi Acharya (rep: 109) Jan 31, '18 at 1:32 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login