I'm trying to reconfigure an XL95 workbook to work under XL97 (and
hopefully later versions.) The workbook has over 150 custom macros
accessed through a custom menu bar. I'm trying to copy the custom menu
bar headings and macro references into a spread sheet so as to
facilitate their insertion into a new menu bar.

Bob Phillips suggested that I use VB macro code like that below. If I
copy the code into a VB module, the macro name "nextlevel" does not
appear in the list of macros, and so I'm uncertain as to how to get the
macro to run. IS IT SUPPOSED TO APPEAR IN THAT LIST?

I'm also unfamiliar with the syntax for the name of the macro. All
macros that I have created never had anything within the parenthesis.
WHERE MIGHT I LEARN MORE ABOUT TEXT PLACED WITHIN ( ) ?

Thanks ... WindsurferLA


Sub nextlevel(ctlParent As CommandBarControl)
Dim ctl As CommandBarControl
iLevel = iLevel + 1
For Each ctl In ctlParent.Controls
iRow = iRow + 1
Cells(iRow, "A").Value = iLevel
Cells(iRow, "B").Value = ctl.Caption
Cells(iRow, "C").Value = ctl.OnAction
Cells(iRow, "D").Value = IIf(ctl.BeginGroup, "TRUE", "")
If ctl.Type = msoControlPopup Then
Cells(iRow, "E").Value = ""
Else
Cells(iRow, "E").Value = ctl.FaceId
End If
If ctl.Type = msoControlPopup Then
nextlevel ctl
End If
Next ctl
iLevel = iLevel - 1
End Sub

I had preceded that code with the code below which includes the macro that
you call.

The stuff in brackets are arguments, a way of passing variable information
to a routine, so that it works on variable information. It cuts down on
replicating code that essentially does the same thing. In this instance, we
go through all the controls in a menu, and if it is a popup, a sub-routine
is called with that control to list its children.

Private iLevel As Long
Private iRow As Long


Sub ReverseMenu()
Const kMenu As String = "Windsurfer"
Dim oCtl As CommandBarControl


On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Menu Maker").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add.Name = "Menu Maker"
Range("A1").Value = "Level"
Range("B1").Value = "Caption"
Range("C1").Value = "Position/Macro"
Range("D1").Value = "Divider"
Range("E1").Value = "Face Id"


iLevel = 1
iRow = 2
With Application.CommandBars("Worksheet Menu Bar")
Range("A2").Value = iLevel
Range("B2").Value = kMenu
Range("C2").Value = ""
Range("D2").Value = IIf(.Controls(kMenu).BeginGroup, "TRUE", "")
Range("E2").Value = ""
nextlevel .Controls(kMenu)
End With


End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"windsurferLA" wrote in message
...
I'm trying to reconfigure an XL95 workbook to work under XL97 (and
hopefully later versions.) The workbook has over 150 custom macros
accessed through a custom menu bar. I'm trying to copy the custom menu
bar headings and macro references into a spread sheet so as to
facilitate their insertion into a new menu bar.

Bob Phillips suggested that I use VB macro code like that below. If I
copy the code into a VB module, the macro name "nextlevel" does not
appear in the list of macros, and so I'm uncertain as to how to get the
macro to run. IS IT SUPPOSED TO APPEAR IN THAT LIST?

I'm also unfamiliar with the syntax for the name of the macro. All
macros that I have created never had anything within the parenthesis.
WHERE MIGHT I LEARN MORE ABOUT TEXT PLACED WITHIN ( ) ?

Thanks ... WindsurferLA


Sub nextlevel(ctlParent As CommandBarControl)
Dim ctl As CommandBarControl
iLevel = iLevel + 1
For Each ctl In ctlParent.Controls
iRow = iRow + 1
Cells(iRow, "A").Value = iLevel
Cells(iRow, "B").Value = ctl.Caption
Cells(iRow, "C").Value = ctl.OnAction
Cells(iRow, "D").Value = IIf(ctl.BeginGroup, "TRUE", "")
If ctl.Type = msoControlPopup Then
Cells(iRow, "E").Value = ""
Else
Cells(iRow, "E").Value = ctl.FaceId
End If
If ctl.Type = msoControlPopup Then
nextlevel ctl
End If
Next ctl
iLevel = iLevel - 1
End Sub

Bob Phillips wrote:
I had preceded that code with the code below which includes the macro that
you call.

The stuff in brackets are arguments, a way of passing variable information
to a routine, so that it works on variable information. It cuts down on
replicating code that essentially does the same thing. In this instance, we
go through all the controls in a menu, and if it is a popup, a sub-routine
is called with that control to list its children.

Private iLevel As Long
Private iRow As Long


Sub ReverseMenu()
Const kMenu As String = "Windsurfer"
Dim oCtl As CommandBarControl


On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Menu Maker").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add.Name = "Menu Maker"
Range("A1").Value = "Level"
Range("B1").Value = "Caption"
Range("C1").Value = "Position/Macro"
Range("D1").Value = "Divider"
Range("E1").Value = "Face Id"


iLevel = 1
iRow = 2
With Application.CommandBars("Worksheet Menu Bar")
Range("A2").Value = iLevel
Range("B2").Value = kMenu
Range("C2").Value = ""
Range("D2").Value = IIf(.Controls(kMenu).BeginGroup, "TRUE", "")
Range("E2").Value = ""
nextlevel .Controls(kMenu)
End With


End Sub



You'll have to excuse me, but my knowledge of VB does not include some
of the variables and syntax you have used, and thus I'm unable to
determine why the program you suggested hangs up. Any help would be
appreciated. Its adding new areas to my visual basic knowledge.


First I changed the line

Const kMenu As String = "windsurfer"
to
Const kMenu As String = "ActionList"

substituting the exact form of the existing optional menu item. I'm
uncertain if I should have enterred "ActionList" or "Action&List", the
latter is how the name was entered so as to underline the letter "L".

However, the program returns the same USER DEFINED TYPE NOT DEFINED
error message.

AT
Sub nextlevel(ctlParent As CommandBarControl)
SAYS - USER DEFINED TYPE NOT DEFINED

Is the problem that I'm running XL97 and these commands are only defined
in the newer software?

Thanks again for all the help. Do hope that others who read these posts
gain knowledge as well. I know that I've learn lots over the years just
by reading through the various posts.