Excel VBA run twice?

T

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
 
D

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.)
 
T

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.
 
D

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.
 
T

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
 
D

Dave Peterson

I don't have a guess.

tony said:
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
 
T

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.
 
D

Dave Peterson

Ok.

Remove XSPAM from my address.

tony said:
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.
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top