VBA Help required...................!

[Thyagaraj]

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

 

[Ardus Petus]

Here is an example:

'-------------------------------------------------
Option Explicit

Dim oMenu As CommandBarPopup

Sub Setmenu()
Set oMenu = CommandBars("Worksheet Menu Bar").Controls.Add( _
Type:=msoControlPopup, _
before:=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

 

[Thyagaraj]

Here is an example:

'-------------------------------------------------
Option Explicit

Dim oMenu As CommandBarPopup

Sub Setmenu()
Set oMenu = CommandBars("Worksheet Menu Bar").Controls.Add( _
Type:=msoControlPopup, _
before:=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

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]

In a general module:

Sub Auto_open()
call Setmenu
end sub
sub Auto_Close()
call resetmenu
end sub

 

[roundabout]

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