Excel Custom Menu

Posted: Sunday, 18 September 2011 in Blackberry

Hereis way to create custom menu.
Thiswill create “Blog” menu and Sub Menu “Link” under Blog menu.
1.            Create a new workbook
2.            Open Visual Basic Editor by pressing Alt+F11
3.            Create new module form Insert-Module
4.            Write this code in that module
‘———————————————————————
SubAuto_Open()

DimMenuBlog As CommandBar
DimMenuLink As CommandBarControl

OnError Resume Next

‘IfBlog menu is nothing
IfApplication.CommandBars(“Worksheet Menu Bar”). _
Controls(“Blog”)Is Nothing Then

‘CreatBlog menu
SetMenuBlog = Application. _
CommandBars(“WorksheetMenu Bar”)
SetcbcCutomMainMenu = MenuBlog.Controls. _
Add(Type:=msoControlPopup)
cbcCutomMainMenu.Caption= “Blog”


‘CreatMenu Link
SetMenuLink = cbcCutomMainMenu.Controls. _
Add(Type:=msoControlButton)
‘MenuName = Link
MenuLink.Caption= “Link”
‘IconCode = 327
MenuLink.FaceId= 327
‘Macrowill run
MenuLink.OnAction= “GoMenuLink”
EndIf
EndSub

SubGoMenuLink()
‘goto sheet2 if menu link clicked
Sheets(“Sheet2”).Select
EndSub

Subauto_close()
‘RemoveBlog menu
Application.CommandBars(“WorksheetMenu Bar”). _
Controls(“Blog”).Delete
EndSub
‘———————————————————————
5.            Save and close your workbook and open again
6.            You will see custom menu (Blog) with sub menu (link) in theright of Help Menu
7.            Here is the sample: http://dl.dropbox.com/u/40405769/Menu.xls

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s