Add A Command Button To The Menu Bar

[Ron de Bruin]

A little teamwork

I love this group

 

[David]

Ron de Bruin wrote

This is a normal behavior for a item on the menu bar.
Normally you always use Sub menu's under a item on the Menu bar.

Use a sub menu which call the macro to avoid this

Precisely what I was trying to avoid. I want a single click solution. The
Subject: of this thread led me in this direction.

 

[David]

Dave Peterson wrote

A minor modification to Ron's routine:

Basically the solution I came up with, but the Menu Bar height issue
remains with your modification. Here a button is taller than a menu item.

 

[Ron de Bruin]

Hi David

You are right that it is a bit taller.
But why is that a problem for you?

 

[David]

Dave Peterson wrote

On Error GoTo 0

Probably not good practice, but I have never run into a problem by leaving
out this line despite seeing most knowledgeable programmers here using it.

 

[David]

Ron de Bruin wrote

You are right that it is a bit taller.
But why is that a problem for you?

Not a functional problem. Merely cosmetic and visual, as the entire sheet
below the Menu Bar shifts down slightly on the screen when the button is
added and up slightly when the button is removed.

 

[JE McGimpsey]

You can see why it's bad practice by running try1:

Public Sub try1()
try2
If Err Then
Debug.Print "Try 2 Error: " & Err.Description
Else
Debug.Print"Try 2 was OK"
End If
try3
If Err Then
Debug.Print"Try 3 Error: " & Err.Description
Else
MsgDebug.PrintBox "Try 3 was OK"
End If
End Sub

Public Sub try2()
Dim a
On Error Resume Next
a = 1 / 0
On Error GoTo 0
End Sub

Public Sub try3()
Dim a
On Error Resume Next
a = 1 / 0
End Sub

While it's true that leaving out On Error GoTo 0 doesn't throw a
run-time error, it does leave the error flags set.

 

[David]

JE McGimpsey wrote

You can see why it's bad practice by running try1:

Good visual representation. Guess I'm off to modify my subs. What are the
effects of leaving error flags set?

 

[JE McGimpsey]

if you never check errors, probably none.

If you use error checking in your routines, like

'Do something
If Err <> 0 Then
'Do something else

then not clearing your flags may give cause you to perform the error
handling routine unnecessarily.

In small, self-contained environments, not including On Error GoTo 0
probably won't get you into trouble.

Where it's important is in situations where you re-use code (i.e., it
may not be important this time, but future code may fail) or where you
may add code to the Sub that will fail if the On Error Resume Next is
still set. If nothing else, it's a visual cue, when I go to add code,
that my environment is not the default.

FWIW, while this is my opinion only, it also reflects a certain
discipline in one's approach to programming. I trust code that resets
properties even if it's not required in *this* particular situation,
much more than code that appears to take shortcuts. To me it indicates a
habit of thoroughness, as well as courtesy to those who have to maintain
the code.

 

[David]

JE McGimpsey wrote

if you never check errors, probably none.

If you use error checking in your routines, like

'Do something
If Err <> 0 Then
'Do something else

then not clearing your flags may give cause you to perform the error
handling routine unnecessarily.

In small, self-contained environments, not including On Error GoTo 0
probably won't get you into trouble.

Where it's important is in situations where you re-use code (i.e., it
may not be important this time, but future code may fail) or where you
may add code to the Sub that will fail if the On Error Resume Next is
still set. If nothing else, it's a visual cue, when I go to add code,
that my environment is not the default.

FWIW, while this is my opinion only, it also reflects a certain
discipline in one's approach to programming. I trust code that resets
properties even if it's not required in *this* particular situation,
much more than code that appears to take shortcuts. To me it indicates
a habit of thoroughness, as well as courtesy to those who have to
maintain the code.

I appreciate the lesson.

 

[David]

Ron de Bruin wrote

Hi David

You are right that it is a bit taller.
But why is that a problem for you?

Eureka!

Stumbled onto the following solution:

Set MyCommandBar = Application.CommandBars(1)
Set Item = MyCommandBar.Controls.Add
Item.Caption = "New Month"
Item.Style = msoControlPopup '<-- this kept desired height
Item.OnAction = "New_Month"