Run Excel macro and suppress toolbars on opening file 1

[achau]

How can I create an Excel macro that will run automatically on opening the file as non-read-only? (I don't want to run it if it's open as read only).

Also is there a way to suppress the toolbars when the file is open, or does that depend on the user's setting?

Thanks in advance for help.

Al

 

[dsi]

This shows how to get the current toolbar settings, modify as needed, and restore the settings. This will run if the workbook is not ReadOnly.

Option Explicit

Public sCmdBarName() As String
Public bCmdBarSetting() As Boolean

Sub Auto_Open()
    Dim cmdBar As CommandBar
    Dim iIdx As Integer
    
    If ThisWorkbook.ReadOnly = False Then
        'Get current settings
        iIdx = 1
        For Each cmdBar In Application.CommandBars
            ReDim Preserve sCmdBarName(iIdx)
            ReDim Preserve bCmdBarSetting(iIdx)
            sCmdBarName(iIdx) = cmdBar.Name
            bCmdBarSetting(iIdx) = cmdBar.Visible
            iIdx = iIdx + 1
        Next
        'Modify as necessary (Show PivotTable toolbar)
        Application.CommandBars("PivotTable").Visible = True
    End If
End Sub
Sub Auto_Close()
    Dim iIdx As Integer
    If ThisWorkbook.ReadOnly = False Then
        For iIdx = LBound(sCmdBarName) To UBound(sCmdBarName)
            If sCmdBarName <> &quot;&quot; Then
                Application.CommandBars(cmdbarname(iIdx)).Visible = bCmdBarSetting(iIdx)
            End If
        Next
    End If
End Sub

 

[achau]

dsi,

Thanks for the code. I have tried the auto_open part (with the modifications) and it worked great!!

However, when I try to close the workbook, I get an error message about the type mismatch in the line:
If sCmdBarName <> &quot;&quot; Then.....

Al.

 

[dsi]

Oops! I guess I should have tested it. You need the array index:

If sCmdBarName(iIdx) <> &quot;&quot;

 

[achau]

dsi,

Thanks..... But now the next line doesn't work :-( (Sorry but I am not good at VB codes). Looks like it's something to do with cmdbarname(iIdx) not defined.

Thanks once again.

Al

 

[dsi]

That should be sCmdBarName(iIdx).

 

[achau]

Still a problem with that line of code. I get an error message:

Run-time error '-2147467259 (80004005)':
Method 'Visible' of object 'CommandBar' failed.


Presumably the codes in Auto_Close are used to restore the user's setting of the toolbars to their original states.

 

[dsi]

For some reason, it would not set the visibility of the Queary toolbar. Let's change the code a bit. Now, it will store all of the current settings. Then, you can turn on specific toolbars. In the Auto_Close, just turn off the toolbars you toggled on. Then, the code will only turn on the toolbars that were previously on. Just make sure to undo anything you did and the code will turn on what was turned on.

Option Explicit

Public sCmdBarName() As String
Public bCmdBarSetting() As Boolean

Sub XAuto_Open()
    Dim cmdBar As CommandBar
    Dim iIdx As Integer
    If ThisWorkbook.ReadOnly = False Then
        'Get current settings
        iIdx = 1
        For Each cmdBar In Application.CommandBars
            ReDim Preserve sCmdBarName(iIdx)
            ReDim Preserve bCmdBarSetting(iIdx)
            sCmdBarName(iIdx) = cmdBar.Name
            bCmdBarSetting(iIdx) = cmdBar.Visible
            iIdx = iIdx + 1
        Next
        'Turn On Selected Toolbars
        Application.CommandBars("PivotTable").Visible = True
    End If
End Sub
Sub XAuto_Close()
    Dim iIdx As Integer
    If ThisWorkbook.ReadOnly = False Then
        'Turn Off Selected Toolbars
        Application.CommandBars("PivotTable").Visible = False
        'Show previous toolbars
        For iIdx = LBound(sCmdBarName) To UBound(sCmdBarName)
            If bCmdBarSetting(iIdx) = True Then
                Application.CommandBars(sCmdBarName(iIdx)).Visible = True
            End If
        Next
    End If
End Sub

Next time I'll make sure to test it first. Sorry!

 

[achau]

Dsi, Many thanks for your help! It works beautifully now!
Al