Hi,

I'm using Office 2007 on Windows XP; I have coded a dictator program that
incorporates similar code to the following (I say similar because I have
tweaked it somewhat and it is sort of complex to describe concisely here):

Private Function DicatorMode(argTrueFalse As Boolean)
'ExecuteExcel4Macro portion by Jim Rech; other code Nick hodge;
With Application
.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & argTrueFalse & ")"
.CommandBars("Status Bar").Visible = argTrueFalse
If argTrueFalse = False Then .Caption = "User Mode" Else .Caption = ""
.ShowWindowsInTaskbar = argTrueFalse
End With
ActiveWindow.View = xlNormalView
ActiveWindow.WindowState = xlMaximized
End Function

The problem is, when this code runs, if the user opens a new XL file, in the
same instance, it is also in dictator mode, but then my shortcut menus are
unavailable, thus locking the user in a "stray" file with no way out.

Is there a way to alter the code to prevent this from happening?

Try this in a workbook that should be in Dictator mode

Private Sub Workbook_Activate()
DicatorMode False
End Sub

Private Sub Workbook_Deactivate()
DicatorMode True
End Sub

If your code is in an addin use withevents at application or workbook level
to trap when a dictator wb is active or not.

FWIW the value of argTrueFalse does not seem intuitive in relation to the
name of the function, whatever.

Regards,
Peter T

"xp" <(E-Mail Removed)> wrote in message
news:F01BDBBD-ECFD-4D5E-8435-(E-Mail Removed)...
> Hi,
>
> I'm using Office 2007 on Windows XP; I have coded a dictator program that
> incorporates similar code to the following (I say similar because I have
> tweaked it somewhat and it is sort of complex to describe concisely here):
>
> Private Function DicatorMode(argTrueFalse As Boolean)
> 'ExecuteExcel4Macro portion by Jim Rech; other code Nick hodge;
> With Application
> .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & argTrueFalse & ")"
> .CommandBars("Status Bar").Visible = argTrueFalse
> If argTrueFalse = False Then .Caption = "User Mode" Else .Caption = ""
> .ShowWindowsInTaskbar = argTrueFalse
> End With
> ActiveWindow.View = xlNormalView
> ActiveWindow.WindowState = xlMaximized
> End Function
>
> The problem is, when this code runs, if the user opens a new XL file, in
> the
> same instance, it is also in dictator mode, but then my shortcut menus are
> unavailable, thus locking the user in a "stray" file with no way out.
>
> Is there a way to alter the code to prevent this from happening?

An alternative is to open the dictator in a new instance of Excel, and
prevent other workbooks from opening in this instance.

Prevent other workbooks from opening by setting the Ignore Other
Applications setting to True:
Application.IgnoreRemoteRequests = True

Open the dictator in its own instance: not so easy. There are a couple
different ways.
1. Have users double click a desktop shortcut (icon) to start it. Shortcut
runs a small VB6 exe that creates a new Excel object then loads the dictator
workbook. This has the benefit of bypassing the macro warning.
2. Routine in dictator's Workbook_Open code that detects whether other
windows are open and visible, and if so it opens a new instance and reopens
itself in that instance. I recall that there was a trick to this one, and
unfortunately I can't find the project now where I did this.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Try this in a workbook that should be in Dictator mode
>
> Private Sub Workbook_Activate()
> DicatorMode False
> End Sub
>
> Private Sub Workbook_Deactivate()
> DicatorMode True
> End Sub
>
> If your code is in an addin use withevents at application or workbook
> level to trap when a dictator wb is active or not.
>
> FWIW the value of argTrueFalse does not seem intuitive in relation to the
> name of the function, whatever.
>
> Regards,
> Peter T
>
> "xp" <(E-Mail Removed)> wrote in message
> news:F01BDBBD-ECFD-4D5E-8435-(E-Mail Removed)...
>> Hi,
>>
>> I'm using Office 2007 on Windows XP; I have coded a dictator program that
>> incorporates similar code to the following (I say similar because I have
>> tweaked it somewhat and it is sort of complex to describe concisely
>> here):
>>
>> Private Function DicatorMode(argTrueFalse As Boolean)
>> 'ExecuteExcel4Macro portion by Jim Rech; other code Nick hodge;
>> With Application
>> .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & argTrueFalse & ")"
>> .CommandBars("Status Bar").Visible = argTrueFalse
>> If argTrueFalse = False Then .Caption = "User Mode" Else .Caption = ""
>> .ShowWindowsInTaskbar = argTrueFalse
>> End With
>> ActiveWindow.View = xlNormalView
>> ActiveWindow.WindowState = xlMaximized
>> End Function
>>
>> The problem is, when this code runs, if the user opens a new XL file, in
>> the
>> same instance, it is also in dictator mode, but then my shortcut menus
>> are
>> unavailable, thus locking the user in a "stray" file with no way out.
>>
>> Is there a way to alter the code to prevent this from happening?
>
>

"Jon Peltier" <(E-Mail Removed)> wrote in message

<snip>
> 2. Routine in dictator's Workbook_Open code that detects whether other
> windows are open and visible, and if so it opens a new instance and
> reopens itself in that instance. I recall that there was a trick to this
> one, and unfortunately I can't find the project now where I did this.

Not sure if there's any particular trick, simply re-open itself in a new
automated instance, then close itself in the current instance. It would end
up being read-only, which may or may not be a problem. I posted a quick
example here.

http://tinyurl.com/dxgzbb

Regards,
Peter T