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.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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)

"Todd Huttenstine" wrote in message
...
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)
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"
wrote in message
...
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


.