adding code to command button in word from excel VBA

[hornbecky83]

Hi-

I am creating a document in word with excel VBA. I figured out how to
add a command button to word from excel VBA, but I can not figure out
how to add code to that command button for when the command button is
clicked on. I do not want to use a userform. I know how to add code
to the button i create in an excel document, but I can't seem to add
code to a button in word, using excel VBA. Here is the code I have
created so far. I wrote messages in the code to tell you what i was
doing and which part i am having problems with. I have been working on
this for several days and would appreciate any help you can give me.
THank you!


Dim Wdoc As Word.document
Dim Wapp As Word.Application
Dim rng As Word.Range
Dim shp As Word.InlineShape

'this created a word document
Set Wapp = CreateObject("Word.Application")
Wapp.Visible = True
Wapp.Documents.Add
Set Wdoc = Wapp.ActiveDocument

Set rng = Wdoc.Paragraphs.Last.Range

'this created my command button and named it
Set shp =
rng.document.InlineShapes.AddOLEControl(ClassType:="Forms.Commandbutton.1",
_
Range:=Wdoc.Paragraphs.Last.Range)

shp.OLEFormat.Object.Caption = "Add Orbit"
shp.OLEFormat.Object.Name = "OrbitButton"

'this is the part that doesn't work. I am trying to add code for when
it is clicked on. This is one of
'the methods i tried.
With
rng.document.VBProject.VBComponents(ActiveDocument.CodeName).CodeModule
.InsertLines .CreateEventProc("Click",
shp.OLEFormat.Object.Name) + 1, _
"Msgbox ""You Clicked The Button"" "
End With


'i also tried changing the previous code to this and it still did not
work.

With
thisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
.InsertLines .CreateEventProc("Click",
shp.OLEFormat.Object.Name) + 1, _
"Msgbox ""You Clicked The Button"" "
End With

 

[Rodrigo Ferreira]

Can be a button on toolbar?
Try the code below (or something like this). This code I use in Excel to
create a button on tool bar. Maybe if you add your "Wdoc" before
"CommandBars."... (sorry but i don'r know how to use OLEControl)


Const strNameCommandBar As String = "Test"
Const strNameCommandBarButton As String = "Test"

Sub CreateCustomButton()

Dim myNewBar As CommandBar
Dim customButton As CommandBarButton

Dim x As CommandBar, LastBar As CommandBar
Dim LastRow As Integer

On Error Resume Next
CommandBars(strNameCommandBar).Delete

'Put the button after the last button on toolbar
For Each x In Application.CommandBars
If x.Visible = True And x.Position = msoBarTop Then
If x.RowIndex > LastRow Then
Set LastBar = x
LastRow = x.RowIndex
End If
End If
Next x

Set myNewBar = CommandBars.Add(Name:=strNameCommandBar,
Position:=msoBarTop, Temporary:=True)
'Set myNewBar = CommandBars.Add(Name:=strNameCommandBar,
Position:=msoBarFloating, Temporary:=True)
With myNewBar
.Enabled = True
.Left = LastBar.Left + LastBar.Width + 1
.RowIndex = LastRow
.Visible = True
End With

Set customButton = myNewBar.Controls.Add(Type:=msoControlButton, ID:=23)
customButton.Caption = strNameCommandBarButton
customButton.FaceId = 92
customButton.TooltipText = "Corrige Links de funções de migração"
customButton.Style = msoButtonIconAndWrapCaption 'msoButtonCaption
customButton.OnAction = "MyMacro"

On Error GoTo 0
End Sub

Sub DeleteCustomButton()
On Error Resume Next 'In case it has already gone.
Application.CommandBars(strNameCommandBar).Controls(strNameCommandBarButton).Delete
Application.CommandBars(strNameCommandBar).Delete
On Error GoTo 0
End Sub

Sub MyMacro()
MsgBox "My Macro"
End Sub

 

[hornbecky83]

Thank you all for your help. I did have to change my security setting.
I also had to add a few more things, such as activating the word
application. However, now i am having a problem with adding more than
one button to the document and the naming I give it. I created a for
loop, so when j is equal to a number greater than 1, vba will run
through my code several times. But after the first time I run through
my code the object.name = "orbitbutton" doesn't work. I get ambiguos
name and automation error. I tried adding a j to the object name, so
the code is object.name = "orbitbutton" & j and everytime i run through
the code, it will name the button: orbitbutton1, orbitbutton2...
However, this still won't run after the first run. I get the same
error, ambiguos name and automation error. Below is the code I have
come up with. Hope you can help me! Sarah

Dim Wdoc As Word.document
Dim Wapp As Word.Application
Dim rng As Word.Range
Dim shp As Word.InlineShape
Dim scode as String

'this created a word document
Set Wapp = CreateObject("Word.Application")
Wapp.Visible = True
Wapp.Documents.Add
Set Wdoc = Wapp.ActiveDocument

Set rng = Wdoc.Paragraphs.Last.Range

'this started my loop
For j = 1 To y

'this created my command button and named it

Set shp =
rng.document.InlineShapes.AddOLEControl(ClassType:="Forms.Commandbutton.1",
_
Range:=Wdoc.Paragraphs.Last.Range)

shp.OLEFormat.Object.Caption = "Add Orbit"
shp.OLEFormat.Object.Name = "OrbitButton" & j


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

Application.ActivateMicrosoftApp xlMicrosoftWord


Wdoc.VBProject.VBComponents("ThisDocument").CodeModule.AddFromString
scode
Next j