Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > NewMenu
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
jrh jrh is offline
external usenet poster
  
Posts: 14
Default NewMenu
I used the code from the book Power Programming by Walkenbach to make a new menu upon opening a certain workbook. However, I get the following error message "Run Time Error '91'. Object variable or With block variable not set" and it highlights the line of code that is "Set HelpMenu = CommandBars(1).FindControl(ID:=30010)"

Can you help? Here is the code

Sub CreateMenu(
Dim NewMenu As CommandBarPopu

' Delete the menu if it already exist
Call DeleteMen

' Find the Help Men
Set HelpMenu = CommandBars(1).FindControl(ID:=30010

If HelpMenu Is Nothing The
' Add the menu to the en
Set NewMenu = CommandBars(1).Controls.Add
(Type:=msoControlPopup,
temporary:=True
Els
' Add the menu before Hel
Set NewMenu = CommandBars(1).Controls.Add
(Type:=msoControlPopup,
Befo=HelpMenu.Index,
temporary:=True
End I

' Add a caption for the men
NewMenu.Caption = "&Budgeting

' FIRST MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlButton
With MenuIte
.Caption = "&Data Entry...
.FaceId = 16
.OnAction = "Macro1
End Wit

' SECOND MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlButton
With MenuIte
.Caption = "&Generate Reports...
.FaceId = 59
.OnAction = "Macro2
End Wit

' THIRD MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlPopup
With MenuIte
.Caption = "View &Charts
.BeginGroup = Tru
End Wit

' FIRST SUBMENU ITE
Set SubMenuItem = MenuItem.Controls.Add
(Type:=msoControlButton
With SubMenuIte
.Caption = "Monthly &Variance
.FaceId = 42
.OnAction = "Macro3
End Wit

' SECOND SUBMENU ITE
Set SubMenuItem = MenuItem.Controls.Add
(Type:=msoControlButton
With SubMenuIte
.Caption = "Year-To-Date &Summary
.FaceId = 42
.OnAction = "Macro4
End Wit

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default NewMenu
You are on a worksheet?

The menu bar does have a Help men u?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jrh" wrote in message
...
I used the code from the book Power Programming by Walkenbach to make a
new menu upon opening a certain workbook. However, I get the following
error message "Run Time Error '91'. Object variable or With block variable
not set" and it highlights the line of code that is "Set HelpMenu =
CommandBars(1).FindControl(ID:=30010)".

Can you help? Here is the code:

Sub CreateMenu()
Dim NewMenu As CommandBarPopup

' Delete the menu if it already exists
Call DeleteMenu

' Find the Help Menu
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)

If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Befo=HelpMenu.Index, _
temporary:=True)
End If

' Add a caption for the menu
NewMenu.Caption = "&Budgeting"

' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Data Entry..."
.FaceId = 162
.OnAction = "Macro1"
End With

' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Generate Reports..."
.FaceId = 590
.OnAction = "Macro2"
End With

' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "View &Charts"
.BeginGroup = True
End With

' FIRST SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Monthly &Variance"
.FaceId = 420
.OnAction = "Macro3"
End With

' SECOND SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Year-To-Date &Summary"
.FaceId = 422
.OnAction = "Macro4"
End With

End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
jrh jrh is offline
external usenet poster
  
Posts: 14
Default NewMenu
ye

----- Bob Phillips wrote: ----

You are on a worksheet

The menu bar does have a Help men u

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

"jrh" wrote in messag
..
I used the code from the book Power Programming by Walkenbach to make
new menu upon opening a certain workbook. However, I get the followin
error message "Run Time Error '91'. Object variable or With block variabl
not set" and it highlights the line of code that is "Set HelpMenu
CommandBars(1).FindControl(ID:=30010)"
Can you help? Here is the code
Sub CreateMenu(
Dim NewMenu As CommandBarPopu
' Delete the menu if it already exist
Call DeleteMen
' Find the Help Men
Set HelpMenu = CommandBars(1).FindControl(ID:=30010
If HelpMenu Is Nothing The
' Add the menu to the en
Set NewMenu = CommandBars(1).Controls.Add
(Type:=msoControlPopup,
temporary:=True
Els
' Add the menu before Hel
Set NewMenu = CommandBars(1).Controls.Add
(Type:=msoControlPopup,
Befo=HelpMenu.Index,
temporary:=True
End I
' Add a caption for the men
NewMenu.Caption = "&Budgeting
' FIRST MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlButton
With MenuIte
.Caption = "&Data Entry...
.FaceId = 16
.OnAction = "Macro1
End Wit
' SECOND MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlButton
With MenuIte
.Caption = "&Generate Reports...
.FaceId = 59
.OnAction = "Macro2
End Wit
' THIRD MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlPopup
With MenuIte
.Caption = "View &Charts
.BeginGroup = Tru
End Wit
' FIRST SUBMENU ITE
Set SubMenuItem = MenuItem.Controls.Add
(Type:=msoControlButton
With SubMenuIte
.Caption = "Monthly &Variance
.FaceId = 42
.OnAction = "Macro3
End Wit
' SECOND SUBMENU ITE
Set SubMenuItem = MenuItem.Controls.Add
(Type:=msoControlButton
With SubMenuIte
.Caption = "Year-To-Date &Summary
.FaceId = 42
.OnAction = "Macro4
End Wit
End Su



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default NewMenu
struggling then, it worked okay for me.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jrh" wrote in message
...
yes

----- Bob Phillips wrote: -----

You are on a worksheet?

The menu bar does have a Help men u?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jrh" wrote in message
...
I used the code from the book Power Programming by Walkenbach to
make a
new menu upon opening a certain workbook. However, I get the
following
error message "Run Time Error '91'. Object variable or With block
variable
not set" and it highlights the line of code that is "Set HelpMenu
=
CommandBars(1).FindControl(ID:=30010)".
Can you help? Here is the code:
Sub CreateMenu()
Dim NewMenu As CommandBarPopup
' Delete the menu if it already exists
Call DeleteMenu
' Find the Help Menu
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Befo=HelpMenu.Index, _
temporary:=True)
End If
' Add a caption for the menu
NewMenu.Caption = "&Budgeting"
' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Data Entry..."
.FaceId = 162
.OnAction = "Macro1"
End With
' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Generate Reports..."
.FaceId = 590
.OnAction = "Macro2"
End With
' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "View &Charts"
.BeginGroup = True
End With
' FIRST SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Monthly &Variance"
.FaceId = 420
.OnAction = "Macro3"
End With
' SECOND SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Year-To-Date &Summary"
.FaceId = 422
.OnAction = "Macro4"
End With
End Sub





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default NewMenu
jrh,

A possibility...
The code should be in a general ( regular, everyday ) module.
It should not be in a class module such as ThisWorkbook or one of the sheet modules. Calling command bars in a class module
requires the "Application" prefix: "Application.CommandBars"

Regards,
Jim Cone
San Francisco, CA

"jrh" wrote in message ...
I used the code from the book Power Programming by Walkenbach to make a new menu upon opening a certain workbook. However, I get
the following error message "Run Time Error '91'. Object variable or With block variable not set" and it highlights the line of
code that is "Set HelpMenu = CommandBars(1).FindControl(ID:=30010)".

Can you help? Here is the code:
Sub CreateMenu()
Dim NewMenu As CommandBarPopup
' Delete the menu if it already exists
Call DeleteMenu
' Find the Help Menu
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
If HelpMenu Is Nothing Then
' Add the menu to the end
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)
Else
' Add the menu before Help
Set NewMenu = CommandBars(1).Controls.Add _
(Type:=msoControlPopup, _
Befo=HelpMenu.Index, _
temporary:=True)
End If
' Add a caption for the menu
NewMenu.Caption = "&Budgeting"
' FIRST MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Data Entry..."
.FaceId = 162
.OnAction = "Macro1"
End With
' SECOND MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlButton)
With MenuItem
.Caption = "&Generate Reports..."
.FaceId = 590
.OnAction = "Macro2"
End With
' THIRD MENU ITEM
Set MenuItem = NewMenu.Controls.Add _
(Type:=msoControlPopup)
With MenuItem
.Caption = "View &Charts"
.BeginGroup = True
End With
' FIRST SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Monthly &Variance"
.FaceId = 420
.OnAction = "Macro3"
End With
' SECOND SUBMENU ITEM
Set SubMenuItem = MenuItem.Controls.Add _
(Type:=msoControlButton)
With SubMenuItem
.Caption = "Year-To-Date &Summary"
.FaceId = 422
.OnAction = "Macro4"
End With
End Sub

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
jrh jrh is offline
external usenet poster
  
Posts: 14
Default NewMenu
that was the problem. thanks

----- Jim Cone wrote: ----

jrh

A possibility..
The code should be in a general ( regular, everyday ) module
It should not be in a class module such as ThisWorkbook or one of the sheet modules. Calling command bars in a class modul
requires the "Application" prefix: "Application.CommandBars

Regards
Jim Con
San Francisco, C

"jrh" wrote in message ..
I used the code from the book Power Programming by Walkenbach to make a new menu upon opening a certain workbook. However, I ge
the following error message "Run Time Error '91'. Object variable or With block variable not set" and it highlights the line o
code that is "Set HelpMenu = CommandBars(1).FindControl(ID:=30010)"
Can you help? Here is the code
Sub CreateMenu(
Dim NewMenu As CommandBarPopu
' Delete the menu if it already exist
Call DeleteMen
' Find the Help Men
Set HelpMenu = CommandBars(1).FindControl(ID:=30010
If HelpMenu Is Nothing The
' Add the menu to the en
Set NewMenu = CommandBars(1).Controls.Add
(Type:=msoControlPopup,
temporary:=True
Els
' Add the menu before Hel
Set NewMenu = CommandBars(1).Controls.Add
(Type:=msoControlPopup,
Befo=HelpMenu.Index,
temporary:=True
End I
' Add a caption for the men
NewMenu.Caption = "&Budgeting
' FIRST MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlButton
With MenuIte
.Caption = "&Data Entry...
.FaceId = 16
.OnAction = "Macro1
End Wit
' SECOND MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlButton
With MenuIte
.Caption = "&Generate Reports...
.FaceId = 59
.OnAction = "Macro2
End Wit
' THIRD MENU ITE
Set MenuItem = NewMenu.Controls.Add
(Type:=msoControlPopup
With MenuIte
.Caption = "View &Charts
.BeginGroup = Tru
End Wit
' FIRST SUBMENU ITE
Set SubMenuItem = MenuItem.Controls.Add
(Type:=msoControlButton
With SubMenuIte
.Caption = "Monthly &Variance
.FaceId = 42
.OnAction = "Macro3
End Wit
' SECOND SUBMENU ITE
Set SubMenuItem = MenuItem.Controls.Add
(Type:=msoControlButton
With SubMenuIte
.Caption = "Year-To-Date &Summary
.FaceId = 42
.OnAction = "Macro4
End Wit
End Su


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT +1. The time now is 01:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement