Excel VBA run twice?

[Tony WONG]

i have a small program to send emails by outlook in excel.
not sure what's go wrong, the VBA run twice when "the button" is clicked.
however if i run VBA in the editor, it is normal to run once.
therefore, i think it is wrong with "the button" created by "Workbook_Open"
when i open the excel.

any assistance on it? Thanks a lot.


****************************
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("SendMail").Delete
End Sub

Private Sub Workbook_Open()
Dim mybutton1 As CommandBarButton
Set mybutton1 = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With mybutton1
.Caption = "SendMail"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = "Sheet1.Sendmail()"
End With
End Sub

 

[Dave Peterson]

The code that creates the button looks ok to me.

You sure it's not the code in Sheet1!SendMail?

(As an aside, I would have thought that that procedure would fit better in a
General module--not behind Sheet1.)

 

[tony wong]

Thanks a lot for your reply.

i am sure it is the code i am working on.
1. there is only such code in the excel
2. when i remove it, the button does not work

i have tried to move it to a module, the result is the same.

very strange, when it is assigned to a button in the worksheet (not in the
menu bar), then it is ok (run once)

very frustrated about it. Thanks a lot for your help.

 

[Dave Peterson]

If you're not clicking on that button too quickly, then I'd guess it was in the
code for your SendMail routine. The code to create the button worked fine for
me.

 

[tony wong]

the VBA run twice even i change codes to single line "msgbox(now())" only

very strange.

at last, i create the a excel file and copy the code, it is ok now.

but i am still keen to know what's going wrong in the old excel file.
Thanks.

tony

 

[Dave Peterson]

I don't have a guess.

the VBA run twice even i change codes to single line "msgbox(now())" only

very strange.

at last, i create the a excel file and copy the code, it is ok now.

but i am still keen to know what's going wrong in the old excel file.
Thanks.

tony

 

[tony wong]

Hi Dave

may i make a request? maybe it is too much.

do you mind to take a look on the excel file? it is just 29K size. no more
code than i mentioned before. i just want to know what's make it run twice
which may assist me in future VBA programming. Thanks.

 

[Dave Peterson]

Ok.

Remove XSPAM from my address.

Hi Dave

may i make a request? maybe it is too much.

do you mind to take a look on the excel file? it is just 29K size. no more
code than i mentioned before. i just want to know what's make it run twice
which may assist me in future VBA programming. Thanks.

 

[Dave Peterson]

In a private email, I suggested this:

Try changing this line:
from:
..OnAction = "Sheet1.Sendmail()"
to:
..OnAction = "Sheet1.Sendmail"

Drop the ()'s.

I would still move the subroutine to a general module and I would stay away from
naming the procedure SendMail.

VBA has its own SendMail function--and even though excel wasn't confused by it,
I might be confused later.