Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help required...................!
Dear Friends,
I am trying to insert a dropdown menu on the standard toolbar of excel, problem is how to call the macro containing the dropdown menu when ever the excel is opened and to remove the inserted dropdown menu whenever the excel is closed. Is this Possible.............? If Possible please help.........! Thanks in advance Ragards Thyagaraj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help required...................!
Here is an example:
'------------------------------------------------- Option Explicit Dim oMenu As CommandBarPopup Sub Setmenu() Set oMenu = CommandBars("Worksheet Menu Bar").Controls.Add( _ Type:=msoControlPopup, _ befo=10, _ temporary:=True) With oMenu .Caption = "&Sort" With .Controls.Add(Type:=msoControlButton) .Tag = 1 .Caption = "by &Region" .OnAction = "DoSort" End With With .Controls.Add(Type:=msoControlButton) .Tag = 2 .Caption = "by &District" .OnAction = "DoSort" End With With .Controls.Add(Type:=msoControlButton) .Tag = 3 .Caption = "by &Volume" .OnAction = "DoSort" End With End With End Sub Sub ResetMenu() oMenu.Delete End Sub Sub dosort() Select Case CommandBars.ActionControl.Tag Case 1: SortRegion Case 2: SortDistrict Case 3: SortVolume End Select End Sub Sub SortRegion() End Sub Sub SortDistrict() End Sub Sub SortVolume() End Sub '------------------------------------------------------------ HTH -- AP "Thyagaraj" a écrit dans le message de news: ... Dear Friends, I am trying to insert a dropdown menu on the standard toolbar of excel, problem is how to call the macro containing the dropdown menu when ever the excel is opened and to remove the inserted dropdown menu whenever the excel is closed. Is this Possible.............? If Possible please help.........! Thanks in advance Ragards Thyagaraj |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help required...................!
Ardus Petus wrote: Here is an example: '------------------------------------------------- Option Explicit Dim oMenu As CommandBarPopup Sub Setmenu() Set oMenu = CommandBars("Worksheet Menu Bar").Controls.Add( _ Type:=msoControlPopup, _ befo=10, _ temporary:=True) With oMenu .Caption = "&Sort" With .Controls.Add(Type:=msoControlButton) .Tag = 1 .Caption = "by &Region" .OnAction = "DoSort" End With With .Controls.Add(Type:=msoControlButton) .Tag = 2 .Caption = "by &District" .OnAction = "DoSort" End With With .Controls.Add(Type:=msoControlButton) .Tag = 3 .Caption = "by &Volume" .OnAction = "DoSort" End With End With End Sub Sub ResetMenu() oMenu.Delete End Sub Sub dosort() Select Case CommandBars.ActionControl.Tag Case 1: SortRegion Case 2: SortDistrict Case 3: SortVolume End Select End Sub Sub SortRegion() End Sub Sub SortDistrict() End Sub Sub SortVolume() End Sub '------------------------------------------------------------ HTH -- AP "Thyagaraj" a écrit dans le message de news: ... Dear Friends, I am trying to insert a dropdown menu on the standard toolbar of excel, problem is how to call the macro containing the dropdown menu when ever the excel is opened and to remove the inserted dropdown menu whenever the excel is closed. Is this Possible.............? If Possible please help.........! Thanks in advance Ragards Thyagaraj Dear Ardus Petus, This is fine but how to call the SetMenu() when excel is opened and how to call Reset() when excel is closed. Regards Thyagaraj |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help required...................!
In a general module:
Sub Auto_open() call Setmenu end sub sub Auto_Close() call resetmenu end sub Thyagaraj wrote: Ardus Petus wrote: Here is an example: '------------------------------------------------- Option Explicit Dim oMenu As CommandBarPopup Sub Setmenu() Set oMenu = CommandBars("Worksheet Menu Bar").Controls.Add( _ Type:=msoControlPopup, _ befo=10, _ temporary:=True) With oMenu .Caption = "&Sort" With .Controls.Add(Type:=msoControlButton) .Tag = 1 .Caption = "by &Region" .OnAction = "DoSort" End With With .Controls.Add(Type:=msoControlButton) .Tag = 2 .Caption = "by &District" .OnAction = "DoSort" End With With .Controls.Add(Type:=msoControlButton) .Tag = 3 .Caption = "by &Volume" .OnAction = "DoSort" End With End With End Sub Sub ResetMenu() oMenu.Delete End Sub Sub dosort() Select Case CommandBars.ActionControl.Tag Case 1: SortRegion Case 2: SortDistrict Case 3: SortVolume End Select End Sub Sub SortRegion() End Sub Sub SortDistrict() End Sub Sub SortVolume() End Sub '------------------------------------------------------------ HTH -- AP "Thyagaraj" a écrit dans le message de news: ... Dear Friends, I am trying to insert a dropdown menu on the standard toolbar of excel, problem is how to call the macro containing the dropdown menu when ever the excel is opened and to remove the inserted dropdown menu whenever the excel is closed. Is this Possible.............? If Possible please help.........! Thanks in advance Ragards Thyagaraj Dear Ardus Petus, This is fine but how to call the SetMenu() when excel is opened and how to call Reset() when excel is closed. Regards Thyagaraj -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help required...................!
Enter the following in to ThisWorkbook Private Sub Workbook_Open() Call Setmenu End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call ResetMenu End Sub Lee -- roundabout ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message2423610.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Required | Excel Worksheet Functions | |||
Help required | New Users to Excel | |||
Bit of help required. | Excel Programming | |||
Help required...... | Excel Discussion (Misc queries) | |||
Help required in VBA | Excel Programming |