Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.office.developer.vba
|
|||
|
|||
adding code to command button in word from excel VBA
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.Co deName).CodeModule .InsertLines .CreateEventProc("Click", shp.OLEFormat.Object.Name) + 1, _ "Msgbox ""You Clicked The Button"" " End With |
#2
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.office.developer.vba
|
|||
|
|||
adding code to command button in word from excel VBA
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).Control s(strNameCommandBarButton).Delete Application.CommandBars(strNameCommandBar).Delete On Error GoTo 0 End Sub Sub MyMacro() MsgBox "My Macro" End Sub -- Rodrigo Ferreira Brazil "hornbecky83" escreveu na mensagem ups.com... 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.Co deName).CodeModule .InsertLines .CreateEventProc("Click", shp.OLEFormat.Object.Name) + 1, _ "Msgbox ""You Clicked The Button"" " End With |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.word.vba.general,microsoft.public.excel
|
|||
|
|||
adding code to command button in word from excel VBA
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").CodeMo dule.AddFromString scode Next j Alok wrote: The first version of your code works fine. You did not say what error you were getting but the only error I got while using your code was one related to increased level of security in Excel 2003 and Word 2003. I went into Word, Tools/Macro/Security/Trusted Sources and selected 'Trust Access to Visual Basic Project'. Once I did this the code created the button as desired and invoked the code. Alok "hornbecky83" wrote: 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.Co deName).CodeModule .InsertLines .CreateEventProc("Click", shp.OLEFormat.Object.Name) + 1, _ "Msgbox ""You Clicked The Button"" " End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
command button a word document in excel | Excel Worksheet Functions | |||
Adding Code to Command Button Added At Run Time | Excel Programming | |||
Adding Code to Command Button at Run Time | Excel Programming | |||
adding a command button to an excel cell? | Excel Discussion (Misc queries) | |||
Adding Code to an Existing Command Button with VBA | Excel Programming |