Need workbook w/ VBA to open in new & separate application window

[Guest]

I need to be able to have workbook A and workbook B open. I will open
workbook A first by double clicking the workbook A.xls which opens Excel.
Workbook A contains no macros. Then I need to be able to open workbook B
which contains macros that remove all toolbars, menus. When I do this now,
the toolbars are removed in both workbooks. Is there a way to open up
Workbook B in a new application where the Macros will not effect both or all
open workbooks. I realize I can open 2 instances of Excel & navigate to each
workbook A & B, but his method is apparently too cumbersome for some other
users. So, double click workbook A one App opens, double click workbook B a
second App opens, macros remove toolbars in workbook B window only. I did
change one of the settings so each workbook opens in a new window but
apparently they are still in one instance of Excel, I would like each double
click to open a new instance of Excel loading it's default settings. Thank
you for your help with this one.

 

[Peter T]

I don't follow if you do or do not want each wb to open in new Excel
instances, look at Ignore Remote Requests in Tools > Options > General
(don't be confused with the Show Windows in taskbar setting which gives the
impression of multiple instances).

Toolbars in new instances will be as saved in the toolbar file (*.xlb) when
the last of all instances was closed.

I think what you want is for a customized toolbar setting whenever a certain
wb is active. Simply set or restore these in the ThisWorkbook Activate &
Deactivate (& BeforeClose) events respectively of the given wb.

Regards,
Peter T

 

[Guest]

Peter,

Thank you, I did the ignore other application in the Tools > Options >
General and this is what I was looking for. My one application that removes
the tool bars does restore them on close, but when it removed them it removed
them from all open applications / windows. Your tip worked and I thank you
very much!

Mike

 

[Guest]

Peter,

I spoke too soon. It worked while Excel was open after making the change
below. When I closed Excel and tried to open 2 workbooks, one with no macros
(all tool bars in tact), then the 2nd workbook which removes toolbars,
neither workbook would open until I unchecked the ignore other applications
box. Any ideas?

Mike

 

[NickHK]

Mike,
I think you need to follow Peter's last paragraph.
The customised tool bar only shows when that particular WB is active.
Otherwise, Excel's normal tool bar is visible.
In which case, move you toolbar code from the Workbook_Open/BeforeClose to
Workbook_Activate/Deactivate events respectively.

NickHK

 

[Peter T]

Hi Mike,

I've no idea why with Ignore Remote Requests checked you cannot open any
workbooks, or is it just a particular pair of workbooks that don't open.

However I would echo Nicks comments about calling set & restore toolbar code
(in a normal module) from the Workbook events as mentioned.

Regards,
Peter T

 

[Guest]

Here's the code I currently have in the workbook, and again my goal is to
have these toolbar settings effect this workbook only so that when I open
another workbook blank or otherwise it has the default toolbars in place:
Private Sub Workbook_BeforeClose
Application.DisplayFormulaBar = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Edit").Enabled = True
Application.CommandBars("Tools").Enabled = True
Application.CommandBars("Format").Enabled = True
Application.CommandBars("Data").Enabled = True
Application.CommandBars("Insert").Enabled = True
Application.CommandBars("View").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFullScreen = False
Application.DisplayFormulaBar = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Edit").Enabled = True
Application.CommandBars("Tools").Enabled = True
Application.CommandBars("Format").Enabled = True
Application.CommandBars("Data").Enabled = True
Application.CommandBars("Insert").Enabled = True
Application.CommandBars("View").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Enabled = True
'Restore heading and tabs and gridlines
With ActiveWindow
.DisplayWorkbookTabs = True
.DisplayHeadings = True
.DisplayGridlines = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True

Private Sub Workbook_Open()
'Disable macro menu
Application.CommandBars("Worksheet Menu Bar").Visible = True
Application.CommandBars("Worksheet Menu Bar").Enabled = False

Application.CommandBars("Tools").Enabled = False
Application.CommandBars("Edit").Enabled = False
Application.CommandBars("Format").Enabled = False
Application.CommandBars("Data").Enabled = False
Application.CommandBars("Insert").Enabled = False
Application.CommandBars("View").Enabled = False
'Remove heading and tabs and gridlines
Application.DisplayFormulaBar = False
With ActiveWindow
.DisplayWorkbookTabs = False
.DisplayHeadings = False
.DisplayGridlines = False
.DisplayZeros = True
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False

Is it the application.commandbars that should read something other than
application, or do I still need to move this code someplace else? Thanks for
your help on this.
Mike

 

[Peter T]

The ActiveWindow settings are mixture of worksheet and worrkbook specific
settings which you can save with your workbook.

I don't like code that messes with my toolbars as typically something goes
wrong and they don't get reset. Hopefully this is relatively safe but not
completely, original settings are stored on Sheet1 in your wb which you can
rename & hide:

' Thisworkbook module

Private Sub Workbook_Activate()
myToolbars False
End Sub

Private Sub Workbook_Deactivate()
myToolbars True
End Sub

' Normal Module

Sub myToolbars(bReset As Boolean)
Dim n As Long, ub As Long
Dim vBars, vBarsOrig
Dim cBar As CommandBar, cbCtr As CommandBarControl
Dim rng As Range

vBars = Array("dummy", "Formatting", "Standard", "Drawing") ' others ?

ub = UBound(vBars)
ReDim vBarsOrig(1 To ub, 1 To 2)

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("B1:C" & ub)

If bReset Then
vBarsOrig = rng.Value
Else
ReDim vBarsOrig(1 To ub, 1 To 1)
End If

Set cBar = Application.CommandBars("Worksheet Menu Bar")

For Each cbCtr In cBar.Controls
If cbCtr.ID <> 30002 Then ' File menu
cbCtr.Visible = bReset
End If
Application.DisplayFormulaBar = bReset
Next

For n = 1 To UBound(vBars)

Set cBar = Application.CommandBars(vBars(n))
With cBar
If bReset Then
.Enabled = Not vBarsOrig(n, 1)
Else
vBarsOrig(n, 1) = Not .Enabled
.Enabled = False
End If
End With
Next

If Not bReset Then
rng.Value = vBarsOrig
End If

End Sub

Sub ResetToolbars()
myToolbars True
End Sub

Would need to Ctrl-tab to other workbooks.

Regards,
Peter T

 

[Guest]

Peter,

I am so close to getting it to do what I want. With the code you provided
it takes away the command bars but didn't restore them. Standard &
Formatting didn't return. The old code I used removed all the toolbars when
I opened my Spreadsheet, the problem came in when I need the toolbars for
another open workbook, hence my original question and your response. The
reason I need the toolbars disabled in the one workbook is there are several
password protected components that I need safeguarded and additionally to
protect formulas I disabled right-click menus, Ctl+C, Ctrl+X, and so on so
that one cell isn't moved to another throwing off a formula calculation,
anyway to my point. I currently have a password protected access that when
entered correctly, unlocked the workbook, restored the toolbars so that I
could enter Visual Basic and make modifications as need or other tweeks, so I
need it protected so the end user can only utilize what I want them to but
with my password enable everything so I can make changes. Thank you again
for all your help on this. If would need some of my old code posted so you
can see what I had, please let me know.

Mike

 

[Peter T]

With the code you provided

it takes away the command bars but didn't restore them. Standard &
Formatting didn't return.

I just tried the code again and it seems to work fine for me, ie disables
the named toolbars and re-enables according to saved settings.

Put the cursor in the Workbook_Activate event, press F5 to run myToolbars
False. The original Not-Enabled settings for the toolbars should be saved on
Sheet1. The reason for saving the Not-enabled settings is if for any reason
the values are removed, cells will be read as Not False, ie True.

With the toolbars disabled put the cursor in the Workbook_Deactivate and run
' myToolbars True'. This should pick up the saved Not-Enabled values and
apply to the original toolbars enabled properties.

If this is all working try and verify in particular if the
Workbook_Deactivate is working as anticipated, both when another workbook is
activated and when the file is closed.

Regards,
Peter T