Menu Item Action Changes with "User" accessing file
 

An Excel97 file macro behaves differently depending on which "User" has
opened a file stored in a directory under "shared documents."

In Excel97 running under WinXP-pro with SR2 installed, the AutoOpen
macro installs a custom item in the menu bar by activating the macro
shown at the bottom of this message. A AutoClose macro removes the item
from the menu bar before closing the file.

The Custom Menu Item has the name "Action $List", so that it appears
with the "L" underlined (but without the $) on the menu bar. The
Excel97 file is stored in a directory in the "Shared Documents" folder,
so it can be accessed by multiple "Users."

* When accessed by "User" having administrative priviledges, the key
board short cut Alt-L merely highlight the "Action List" button on the
menu bar; one must press "return" to see the submenu list.

* When accessed by "User" having limited priviledges, the key board
short cut Alt-L results in the full submenu list being shown immediately.

Prior to restoring the operating system using Norton Ghost, when the
Excel sheet was accessed by the "user" having administrative priviledges
(UserA, the key board short cut Alt-L would lead to the full submenu
list being shown. After the operating system was restored using Norton
Ghost, the file was moved from the Admin.User's (userA) my-documents
directory to the shared-documents directory.

Built in key-board short cuts such at Alt-F work correctly and for
example, open all the submenus under "File" independent of which user
has opened the spread sheet.

I SUSPECT that there is a difference between how each user has
customized Excel or set the Excel application options, but I can't find
any meaningful difference. I don't believe problem is linked to one user
having administrative priviledges (User A) and the other having limited
priviledges (User L). It might be a result of the directories of UserA
having several Excel files with macros while the directories of UserL
only has one Excel file with macros, however, the problem is encountered
when all of the other Excel files of UserA are closed. Moreover, UserA
did not have the problem prior to restoring the operating system even
though his directories contained other Excel files with macros.

CAN ANYONE PROVIDE SOME HINTS AS TO WHAT MIGHT BE THE PROBLEM?

tnx - WindsurferLA


- - - - - - - - - - - -
MACRO CODE


Option Explicit

Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, nextlevel, PositionOrMacro, Caption, Divider, FaceId

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
'''''''''''''''''''''''''''''''''''''''''''''''''' ''

' Make sure the menus aren't duplicated
Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
nextlevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Befo=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If nextlevel = 3 Then
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId < "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem =
MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId < "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Menu Item Action Changes with "User" accessing file
 

This post is merely to alert other who might encounter similar problem.
Problem was inadvertently cured by a process not really understood.

Somehow the special custom menu item had became a checked menu listed on
the View option list. When it was unchecked, the problem described below
went away, and the custom menu again acted as same independent of which
user was using the Excel file stored in the "shared documents" directory.

windsurferLA wrote:
An Excel97 file macro behaves differently depending on which "User" has
opened a file stored in a directory under "shared documents."

In Excel97 running under WinXP-pro with SR2 installed, the AutoOpen
macro installs a custom item in the menu bar by activating the macro
shown at the bottom of this message. A AutoClose macro removes the item
from the menu bar before closing the file.

The Custom Menu Item has the name "Action $List", so that it appears
with the "L" underlined (but without the $) on the menu bar. The
Excel97 file is stored in a directory in the "Shared Documents" folder,
so it can be accessed by multiple "Users."

* When accessed by "User" having administrative priviledges, the key
board short cut Alt-L merely highlight the "Action List" button on the
menu bar; one must press "return" to see the submenu list.

* When accessed by "User" having limited priviledges, the key board
short cut Alt-L results in the full submenu list being shown immediately.

Prior to restoring the operating system using Norton Ghost, when the
Excel sheet was accessed by the "user" having administrative priviledges
(UserA, the key board short cut Alt-L would lead to the full submenu
list being shown. After the operating system was restored using Norton
Ghost, the file was moved from the Admin.User's (userA) my-documents
directory to the shared-documents directory.

Built in key-board short cuts such at Alt-F work correctly and for
example, open all the submenus under "File" independent of which user
has opened the spread sheet.

I SUSPECT that there is a difference between how each user has
customized Excel or set the Excel application options, but I can't find
any meaningful difference. I don't believe problem is linked to one user
having administrative priviledges (User A) and the other having limited
priviledges (User L). It might be a result of the directories of UserA
having several Excel files with macros while the directories of UserL
only has one Excel file with macros, however, the problem is encountered
when all of the other Excel files of UserA are closed. Moreover, UserA
did not have the problem prior to restoring the operating system even
though his directories contained other Excel files with macros.

CAN ANYONE PROVIDE SOME HINTS AS TO WHAT MIGHT BE THE PROBLEM?

tnx - WindsurferLA


- - - - - - - - - - - -
MACRO CODE


Option Explicit

Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, nextlevel, PositionOrMacro, Caption, Divider, FaceId

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
'''''''''''''''''''''''''''''''''''''''''''''''''' ''

' Make sure the menus aren't duplicated
Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
nextlevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Befo=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If nextlevel = 3 Then
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem =
MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId < "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem =
MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId < "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub