Solved: Word 2003 - Button that runs VBA code

[AyeSee]

I have found other posts on this forum, but none of them have seemingly been truly resovled. I want to insert a button that runs a macro I have created. The Microsoft website gives this code:


Sub Test()

'Add a command button to a new document
Dim doc As Word.Document
Dim shp As Word.InlineShape
Set doc = Documents.Add

Set shp = doc.Content.InlineShapes.AddOLEControl(ClassType:="Forms.CommandButton.1")
shp.OLEFormat.Object.Caption = "Click Here"

'Add a procedure for the click event of the inlineshape
'**Note: The click event resides in the This Document module
Dim sCode As String
sCode = "Private Sub " & shp.OLEFormat.Object.Name & "_Click()" & vbCrLf & _
" MsgBox ""You Clicked the CommandButton""" & vbCrLf & _
"End Sub"
doc.VBProject.VBComponents("ThisDocument").CodeModule.AddFromString sCode

End Sub


The problem with this code is that it opens a new document and it returns a message box. What I need it to do is add to the current document and I need it to run a macro I have created. I do not understand how to tailor this code to do that...

[fumei]

You have not clearly stated the situation.

The easiest is to use the Control toolbar and insert a commandbutton, right click it, and write your code.

OR..........

insert the commandbutton and right click it and use a Call statement to call an existing procedure.

Why do you need to insert it programmatically? But if you do, do you understand what sCode is in the example you posted??

It is the procedure that will be executed by clicking the button just programmatically inserted.

So...........
sCode = "Private Sub " & shp.OLEFormat.Object.Name & "_Click()" & vbCrLf & _
"Call ExistingProcedure" & vbCrLf & _
"End Sub"

As for: The problem with this code is that it opens a new document and it returns a message box. "

Yes....of course it does. That is precisely what the code is telling it to do.

Set doc = Documents.Add

this makes a new document.

sCode = "Private Sub " & shp.OLEFormat.Object.Name & "_Click()" & vbCrLf & _
" MsgBox ""You Clicked the CommandButton""" & vbCrLf & _
"End Sub"

This is the code for the procedure attached to the button. It displays a messagebox.

[AyeSee]

Ok, I officially won my IDIOT status.

lol I think I spent 15 minutes looking at that toolbar, and could not spot the command button...

I was wondering why something so simple was not available... Is there a way to hide this embarassing post? lol

Basically that is what i wanted to do. Add a command button and have it run what I want. No need for code.

Thanks Gerry for all the help you have been giving me, you must dislike me at this point.... :P

[fumei]

"Thanks Gerry for all the help you have been giving me, you must dislike me at this point.... "

If you keep going with an open mind and actually learn...nope, absolutely not. I like helping people learn, but the work of doing so is up to you. So persist, and don't worry about and dislikes. Not there.

So do you get it. Use the Controls toolbar (View > Toolbars) to insert a commandbutton. Right click it View Code. Then you can:

1. write the procedure into the _Click event
2. use Call to point to an existing procedure
3. copy an existing procedure instructions and paste those into the _Click event.

NOTE!!!! If you do #3, I strongly suggest you delete the old procedure. It is generally a bad practice to have two different procedures that do the same thing.

[fumei]

"Add a command button and have it run what I want. No need for code."

You mean no need for code to create/insert the commandbutton. The button itself has a need for code, otherwise it is rather a waste of a control.

<grin>

[fumei]

BTW: I just have to add that I am not a big fan of commandbuttons within a document. I prefer keyboard shortcuts, or menu items, or toolbar icons to execute procedures. I just think buttons on the page look kind of goofy.

[AyeSee]

I totally agree with everything you just said.

I inserted this code inside the _Click event:


Private Sub CommandButton1_Click()
Normal.NewMacros.FinishFusion
End Sub


As for my reason for choosing a button over a shortcut, it is very simple. As we talked in the other thread, my approach can be considered sloppy because a lot of errors can be done because of the user. To limit this, in the template I am using for the mail merge, I have blacked out everything, added a big obvious button so that the user is strongly encouraged to press the button.

As a result to the clicking of the button, the button launches a macro that cleans everything up and gives the final output.

I am happy to know that you like helping me out on this. I am learning tremendously.

[AyeSee]

You win Gerry.

I finally finished the code for the template. And then I go to test everything and the button turned out to be a pretty bad solution.

In fact, I have a pretty big problem. The macro does not transpose from the template to the mailmerged document.

Nor does the code inside the button. That means that the user would have to do that every time, unless I ask that the user put the macro in his Nomral.doc before using the tool... But there is starting to be a pileup of ifs and whens for the user...

Is this normal? Why isnt the code in the template following over?

I think I'm gonna call this a day lol, been here for 11 hours...

[fumei]

"The macro does not transpose from the template to the mailmerged document."

No, it would not. Code in the template remains in the template. Code for an ActiveX button (at least as it is coded now) needs to be in the ThisDocument module of the document, not the template.

I am not sure of the total situation, so it is hard to suggest things. Please describe the situation precisely. BTW: I am gone for the next month so after today i will not have Internet access so can not help.

It is possible to write the Document_New (the event thatcreates the new document from a template) so that it imports a procedure into the new document.

[AyeSee]

I have decided to avoid the problem for now by changing the use of a button by the creation of a shortcut in the toolbar. I will have to set this up on each computer, but given there are only about 10 computers, demanding an initial setup is acceptable if I document appropriately.

Thanks for the help Gerry, I really appreciated it. You helped learn a lot. I hope you have yourself an excellent vacation.

[fumei]

I suggest you look into the use of a global template.

[AyeSee]

Thanks, I shall definetly look into it.

I started reading this article:
http://support.microsoft.com/kb/292037

What do you think of it, do you think it will lead me in the right direction?

[fumei]

"will lead me in the right direction"

YES!

It also points the way to export/import code into documents, which may be what you want to do.