Multiple Button Creation

[Todd Huttenstine]

hey guys

I have a code that creates new menu items with different
buttons in them. below is my code:

Set newMenu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Temporary:=False)

Dim newMenu As CommandBarPopup
Dim ctrlPopUp As CommandBarControl
Dim ctrlButton As CommandBarControl


newMenu.Caption = "Import Data"
Set ctrlPopUp = newMenu.Controls.Add
(Type:=msoControlPopup, ID:=1)
ctrlPopUp.Caption = "Please Select..."
Set ctrlButton = ctrlPopUp.Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButton.Caption = "Import Data..."
ctrlButton.Style = msoButtonCaption
ctrlButton.OnAction = Application.StartupPath
& "\ImportData.xls" & "!module1.importdata"

What this code does is Create a new menu item on the
toolbar called "Import Data". When you click it it drops
down and shows 1 new sub-menu with another arrow. This
sub-menu is called "Please Select". Then when you hold
your currsor over the arrow it shows 1 new menu item
called "Import Data...". The code then assigns the button
click (onaction) ImportData.xls" & "!module1.importdata".
So when a user clicks this button it runs the code in
module1.importdata module.


What I would like to do is Create a group of submenus
under the Please Select. So when a user holds his currsor
over Please Select menu arrow, it show him 3 other menu
items with arrows. Lets call the first one "Daily
Audits", the 2nd menu arrow "Weekly Renewals", and then
the 3rd, "Follow-Ups". How do I do this?

Thanks
Todd Huttenstine

 

[Bob Phillips]

Hi Todd,

Here you go

Dim newMenu As CommandBarPopup
Dim ctrlPopUp As CommandBarControl
Dim ctrlButton As CommandBarControl

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Import Data").Delete
On Error GoTo 0

Set newMenu = CommandBars("Worksheet Menu Bar").Controls.Add( _
Type:=msoControlPopup, Temporary:=False)

With newMenu
.Caption = "Import Data"
Set ctrlPopUp = .Controls.Add(Type:=msoControlPopup, ID:=1)
With ctrlPopUp
.Caption = "Please Select..."
Set ctrlButton = .Controls.Add(Type:=msoControlButton, ID:=1)
With ctrlButton
.Caption = "Import Data..."
.Style = msoButtonCaption
.OnAction = Application.StartupPath & "\ImportData.xls" &
"!module1.importdata"
End With
Set ctrlButton = .Controls.Add(Type:=msoControlButton, ID:=1)
With ctrlButton
.Caption = "Daily Audits"
.Style = msoButtonCaption
.OnAction = "myMacro1"
End With
Set ctrlButton = .Controls.Add(Type:=msoControlButton, ID:=1)
With ctrlButton
.Caption = "Weekly Renewals"
.Style = msoButtonCaption
.OnAction = "myMacro2"
End With
Set ctrlButton = .Controls.Add(Type:=msoControlButton, ID:=1)
With ctrlButton
.Caption = "Follow-Ups"
.Style = msoButtonCaption
.OnAction = "myMacro3"
End With
End With
End With


--

HTH

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

 

[Todd Huttenstine]

Hey Bob

Thank you. I almost feel bad for asking now that I
figured it out. I was sitting here working on it and came
up with the following... Mines less consistent than your
because I didnt put everything in the with statement. Let
me ask you, is there a way I could have done this without
having to use a with statement?

'Creates Ops Report Menu Item
Set newMenu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Temporary:=False)
newMenu.Caption = "Opstest Reports"
Set ctrlPopUp = newMenu.Controls.Add
(Type:=msoControlPopup, ID:=1)
ctrlPopUp.Caption = "Run a Report..."

'Creates the Daily Audits Sub-Menu System
With ctrlPopUp
Set newMenu1 = .Controls.Add(Type:=msoControlPopup,
Temporary:=False)
newMenu1.Caption = "Daily Audits"
End With

With newMenu1
Set ctrlButton = .Controls.Add(Type:=msoControlButton,
ID:=1)
ctrlButton.Caption = "1 Years"
ctrlButton.Style = msoButtonCaption
End With
ctrlButton.OnAction = Application.StartupPath
& "\ImportData.xls" & "!OneYear.Oneyear"

With newMenu1
Set ctrlButton1 = .Controls.Add(Type:=msoControlButton,
ID:=1)
ctrlButton1.Caption = "2 Years"
ctrlButton1.Style = msoButtonCaption
End With
ctrlButton1.OnAction = Application.StartupPath
& "\ImportData.xls" & "!TwoYear.TwoYear"

-----Original Message-----
Hi Todd,

Here you go

Dim newMenu As CommandBarPopup
Dim ctrlPopUp As CommandBarControl
Dim ctrlButton As CommandBarControl

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Import Data").Delete
On Error GoTo 0

Set newMenu = CommandBars("Worksheet Menu Bar").Controls.Add( _
Type:=msoControlPopup, Temporary:=False)

With newMenu
.Caption = "Import Data"
Set ctrlPopUp = .Controls.Add (Type:=msoControlPopup, ID:=1)
With ctrlPopUp
.Caption = "Please Select..."
Set ctrlButton = .Controls.Add

(Type:=msoControlButton, ID:=1)

With ctrlButton
.Caption = "Import Data..."
.Style = msoButtonCaption
.OnAction = Application.StartupPath & "\ImportData.xls" &
"!module1.importdata"
End With
Set ctrlButton = .Controls.Add

(Type:=msoControlButton, ID:=1)

With ctrlButton
.Caption = "Daily Audits"
.Style = msoButtonCaption
.OnAction = "myMacro1"
End With
Set ctrlButton = .Controls.Add

(Type:=msoControlButton, ID:=1)

With ctrlButton
.Caption = "Weekly Renewals"
.Style = msoButtonCaption
.OnAction = "myMacro2"
End With
Set ctrlButton = .Controls.Add

(Type:=msoControlButton, ID:=1)

 

[Bob Phillips]

Hi Todd,

Of course. All that the with does is to create a reference to an object so
that you can implicitly refer to it's properties thereafter. You could add
the object reference to each property and forego the With, but with is
better annotation, and more efficient.

--

HTH

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

 

[Tom Ogilvy]

Method 1
Dim rng as Range
set Rng = WorkBooks("Book1.xls").Worksheets("Sheet1").Range("A1")
rng.Value = 21
rng.Interior.ColorIndex = 6
rng.Font.Name = "Arial
.Fond.Size = 12

Method 2
With WorkBooks("Book1.xls").Worksheets("Sheet1").Range("A1")
.Value = 21
.Interior.ColorIndex = 6
.Font.Name = "Arial"
.Font.Size = 12
End With

Method 3
WorkBooks("Book1.xls").Worksheets("Sheet1") _
.Range("A1").Value = 21
WorkBooks("Book1.xls").Worksheets("Sheet1") _
.Range("A1").Interior.ColorIndex = 6
WorkBooks("Book1.xls").Worksheets("Sheet1") _
.Range("A1").Font.Name = "Arial"
WorkBooks("Book1.xls").Worksheets("Sheet1") _
.Range("A1").Font.Size = 12


From everything I have read, as far as speed of execution, Method 1 and
Method 2 are equal and both are more efficient than Method 3.

 

[Bob Phillips]

which is what I said.

--

HTH

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

 

[Tom Ogilvy]

Not as clearly

--
Regards,
Tom Ogilvy

which is what I said.

--

HTH

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

object

 

[Bob Phillips]

in your opinion.

--

HTH

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

 

[Jake Marx]

Hi Tom,

From everything I have read, as far as speed of execution, Method 1
and Method 2 are equal and both are more efficient than Method 3.

I know this is an older thread, but I found something interesting when I was
testing the three methods set forth by Tom. I found that method 2 (using a
With) is quite a bit faster than method 3 (no With, no object reference),
but that method 1 (setting a reference to the object and utilizing the
object variable) is even faster than method 2 when the object variable is
explicitly typed (eg, declared as MSForms.TextBox as opposed to Object).
However, when method 1 uses As Object (late binding the textbox), I found
that it is the slowest of all other methods.

This is interesting to me because I was under the impression that a With
statement and setting a reference were pretty much equivalent behind the
scenes. But that does not seem to be the case, at least with ActiveX
controls on a Worksheet.

FWIW, using a With in conjunction with method 1 didn't speed things up at
all.

So I guess I'll use object variables when I'm accessing several methods of
an ActiveX control from a Worksheet. Has anyone done any testing to
corroborate these results? Has anyone tested this with native Excel objects
or with ActiveX controls on UserForms?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]