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.CodeName).CodeModule
            .InsertLines .CreateEventProc("Click",
shp.OLEFormat.Object.Name) + 1, _
                "Msgbox ""You Clicked The Button"" "
        End With

0
1/3/2007 6:03:57 PM
excel 39880 articles. 0 followers. Follow

2 Replies
184 Views

Similar Articles

[PageSpeed] 58

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


-- 

Rodrigo Ferreira
Brazil


"hornbecky83" <hornbecky83@gmail.com> escreveu na mensagem 
news:1167847437.397330.191430@42g2000cwt.googlegroups.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.CodeName).CodeModule
>            .InsertLines .CreateEventProc("Click",
> shp.OLEFormat.Object.Name) + 1, _
>                "Msgbox ""You Clicked The Button"" "
>        End With
> 


0
cabelo (15)
1/3/2007 7:32:01 PM
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

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.CodeName).CodeModule
> >             .InsertLines .CreateEventProc("Click",
> > shp.OLEFormat.Object.Name) + 1, _
> >                 "Msgbox ""You Clicked The Button"" "
> >         End With
> > 
> >

0
1/3/2007 9:45:23 PM
Reply:

Similar Artilces:

Probelm with Code !!!!!!!!!!!!!!!!!
"The specified dimension is not valid for the current chart type" error. I can't get rid of this eror even with 2 "On Error" Statements at the bottome of the code where I suspect the problem to be. Private Sub cmdgetBO_Data_Click() Dim f, ws As Worksheet, Msg Dim BO_Datafile_Name As String, BOReport_lastColumn As String Dim BOReport_lastRow As Integer, BOPos As Integer Dim BOReportWS As Worksheet Dim rngBOReport As Range, c As Range With Application .ScreenUpdating = True .Calculation = xlCalculationManual .DisplayAlerts = False En...

Anybody got the sample code handy?
These archived articles describe some things I'd like to experiment with: <http://msdn2.microsoft.com/en-us/library/ms997526.aspx> <http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarvc/html/msdn_custctl.asp> MSDN search doesn't find the sample app(s) anywhere. Somebody got them handy? "BobF" <rNfOrSePeAzMe@charter.net> ha scritto nel messaggio news:u0xW9VJHIHA.5208@TK2MSFTNGP04.phx.gbl... > <http://msdn2.microsoft.com/en-us/library/ms997526.aspx> > MSDN search doesn't find the sample app(s) anywhere. Somebody...

Stationery missing when sending email via code
Hi In outlook I have set stationery and signatures under 'Mail Format' tab. The problem is when I send email from another application via code such as Set OutlookApp = New Outlook.Application Set EM = OutlookApp.CreateItem(olMailItem) The email ignores the stationery. What is the problem and how can I make the stationery work even when mail is sent via code? Many Thanks Regards See Eric's sample at = http://blogs.officezealot.com/legault/archive/2005/07/21/7581.aspx --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Us...

Adding hyperlinks with parameters
Hey everybody I have a batch file which needs to be run from an excel sheet with a parameter, like this: c:\1.bat myParameter But when i try to run the Hyperlink, the Excel shows an error message saying it can't open the specified file (probably because the is a space between the file and the parameter) is there any known way to run the link from the excel? On Mar 2, 12:45=A0pm, Pasha <pavel_v...@mailto.mod.gov.il> wrote: > Hey everybody > I have a batch file which needs to be run from an excel sheet with a > parameter, like this: > c:\1.bat myParameter > But when i ...

Adding Blank Rows after the last Detail of the Report
Hi All, I've used this code from Dwayne: Private Sub Report_Page() Dim intNumLines As Integer Dim intLineNumber As Integer Dim intTopMargin As Integer Dim ctl As Control Dim intLineHeight As Integer intNumLines = 12 intTopMargin = Me.Section(3).Height intLineHeight = Me.Section(0).Height For Each ctl In Me.Section(0).Controls For intLineNumber = 0 To intNumLines - 1 Me.Line (ctl.Left, intTopMargin + _ (intLineNumber * intLineHeight)) - _ Step(ctl.Width, intLineHeight), , B Next Next End Sub My report needs to print blank rows (at least...

How to insert an Excel landscape document into a portrait Word doc
I have a Word document in portrait style and need to insert an Excel Spreadsheet which is in landscape. Is that possible? Please clarify. Landscape and Portrait are simply print orientation in whichever document is being printed. Are you inserting a complete workbook into Word? Are you inserting a range from a worksheet into Word? Maybe you need two separate print jobs? Gord Dibben MS Excel MVP On Tue, 11 May 2010 15:57:01 -0700, Heidi <Heidi@discussions.microsoft.com> wrote: >I have a Word document in portrait style and need to insert an Excel >Spr...

Sorting and the word The
Hiya Does anyone know a way of sorting a list (of CD's or films) where the word The wouldn't be included, so The Beatles would be under B, not T ? I don't want to have to delete all the The's, so it will still say The Beatles ? TIA Dave Dave, In a new column, enter the formula =IF(LEFT(A1,4) = "the ",MID(A1,5,LEN(A1))&", The",A1) and fill down as far as you need to go. Sort on this column instead of the primary column. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dave" <dwb...

outlook vcard, ad integration user properties
my boss is trying to get hix fax# in his vcard. he is using his address from the global address list I have modified his profile in active directory users and computers: I go into AD users and computers, double click on his user, go to the telephones tab, and enter his fax #. but when I did this his fax # is still not being pulled up in his outlook vcard. Does anyone know where outlook is trying to get this field from / how i can get this field into his vcard? thx ...

Excel Sort
I'm having trouble getting data sorted for an entire year's worth of data. If I highlight the entire year, it won't sort. A box pops up saying "this operation requires the merged cells to be identically sized." What is happening? Don't use merged cells in the sort. I strongly recommend never to use merged cells, you can basically get the same layout in most cases and down the road you will avoid headaches -- Regards, Peo Sjoblom "EJ" <anonymous@discussions.microsoft.com> wrote in message news:54ae01c3e505$8378b8f0$a401280a@phx.gbl... >...

Is there a way to insert photos into excel at a specific size?
I need to insert multiple photos into an excel spreadsheet and would like to know if there is a fast way to resize the photos to the same size. Can it be inserted at a predesignated size to prevent from having to size each individual photo? Maybe you could start with this: http://groups.google.com/groups?threadm=3E1F6985.69EF880%40msn.com (watch out for line wrap in the first response--it's discussed in the later posts.) srvwannabe wrote: > > I need to insert multiple photos into an excel spreadsheet and would like to > know if there is a fast way to resize the photos to t...

A2007 Export to Excel
I have the following code....which works perfectly in A2003, and works if Im opening a A2003 in A2007...but if I try to run it in a pure A2007 database it craps out. Here is the code: Dim xlApp As Excel.Application Dim xlSheet As Excel.Worksheet Dim xlWorkbook As Excel.Workbook Dim objRST As Recordset Dim strQueryName As String Dim strSheetName As String strQueryName = "ExcelStatus" strSheetName = Me.Status Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlWorkbook = xlApp.Workbooks.Add Dim db As DAO.Database Dim qdf As DAO...

ADDING A NUMBER
how do i add a number to a range of cells? I need to add 11.27 to a number of different cells each with a different number in them Put 11.27 in an empty cell, format it the same way as the numbers you want to add to, copy it, then select the numbers you want to add to and do edit>paste special and select add. -- Regards, Peo Sjoblom "Helpme" <Helpme@discussions.microsoft.com> wrote in message news:6D51344A-78E6-4377-B292-FC6C16FF6BA2@microsoft.com... > how do i add a number to a range of cells? I need to add 11.27 to a > number > of different cells e...

adding new records
I am totally new to CRM, so I hope this is really easy question: This concerns permissions.. I have a "sales" group, and with one of those people i need to be able to let them add new records on behalf of other "sales" people. Is there like a standard permission setting for it? (obviously every "sales" person can add new records if they select them selfs as the owner) thanks, Michal. Hi, By default the person who creates the record will be the owner. After record creation the person could reassign the record (manually) to another user. If all rec...

Linking to a Password Protected Document in Excel 2000
I have a document that is password protected. I also have a file that references the protected files for many of the its formulas. Each time I open the file and update the links I am prompted multiple times (once for each cell reference to the protected file) for the password. Is there any setting so that a user is only prompted once for the password to a reference file that is protected? I know that one option is just to copy the tabs from my referencing file into the protected file, but it would be nice to keep the documents separate. Thanks, Andrew One option might be to disable th...

When adding attachments...
....in Outlook the following errors comes up: "Out of memory or system resources, please close some programs and try again" Quotas are fine. I have deinstalled/reinstalled Office, cleaned 'temp' areas, and many other tidbits. Any other ideas? Thanks. This might help as i had this problem with Outlook 98. Is the file you are trying to copy to attach nested in sub folders? if so try and copy the attachment to a root drive like c:\ Ignore previous email. wrong spellin if the attachment is nested in sub folders(this means you have to open different folders to get to ...

print 2 or 4 charts per page in excel 2000
Hi, I have created a number of one page charts, each of which are located as seperate sheets in one excel workbook. I would now like to print 2 or 4 charts per page. However, I am unable to do so. Do you know of automated way which would allow this to be done. Your help would be appreciated. Regards, Ashish Ashish - The easiest way to do this would be to embed your charts in one or more worksheets: right click on the chart, choose Location, then pick As Object and select a sheet from the list. Then size and position them so two or four charts fit into a printed page of the workshee...

Hyperlinking to documents in Excel
I have extracted a tab delimited .txt list of all the documents on ou server at work. I would like to import this list into an Exce Spreadsheet (easy enough) and then hyperlink back to the documents o the server. Is there a way to hyperlink automatically? I have severa thousand documents and hyperlinking one at a time is not ver realistic. I have the option of pulling the list with either th relative path (just the document name and extension) or with th absolute file path (give the complete path of every document). I anyone has any suggestions I'm all ears. Thank you. Dale Co -- M...

Finally, a SIMPLE solution for Excel file Consolidation
After reading about everyone's search for a consolidation program for Excel sheets and replying to individual posts I decided to create a new topic to let everyone know about a BRAND NEW consolidation software on the market. After 10 years of production a new standard in the way Excel files are consolidated is finally here!! www.quartermain.com The following 4 features make up the heart and soul of Consolidate! Consolidation is Automatic -------------------------- * Whole Spreadsheets merged across files * No linking formulas, macros, add-ins, or programming * Stand alone application, ...

Picture Disappears When Added To Contact In Outlook 2003
Can someone help please. When I try to attach a picture to a contact in outlook 2003 immediately I double click the picture to be attached it disappears out of the outlook contact along with the placement holder in the form. Only when I right click the mouse and go remove picture do I get the placement holder back. I have looked at all the help menus but they offer no clues. Thanks TS ...

grouping in Excel Pivot table
currently i am using pivot table to calculate the grand total for ageing day in my report. The ageing day are 1, 2, 3 to 6 days and > 6 days that i want to group together. In pivot table, it can only able to calculate the grand total like total ageing for 1 day, 2 days, 3 days.... and go on. Is there any way that pivot table will do what i want or you guys can suggest any other method? I am aware of group function in pivot table but it cannot suit my needs. Thanks Lim Heng Sin All sorts of data can be analysed by adding another column to the base data and using the new field in ...

Adding ActiveX to dialog with wrapper
When I add an ActiveX object to a dialog using the dialog editor - right click - insert activeX object, the help on the resulting activex selection tool states that a wrapper will not be added by using this method. If you need a wrapper, it suggests using the class view. This is using Visual Studio 2005. I'm having some problems with this on an existing project. To make sure I understand the process, I created a new MFC project, added a dialog, created a class for that dialog and added a Flexgrid object using the right click method in the dialog. I then added a variable for the flexgrid...

UPC Universal Product Code
I find it interesting that there is no predefined location for UPC codes in Inventory Control. Of course, you could use either the general or short description. But this would only work if there were a one to one relationship (One UPC to ONE Inventory Control SKU). In my case, we have multiple UPC’s for the same SKU. To get around this we could use the manufacturer part number form and table. But unfortunately we want to map both UPC’s and manufacturer part numbers. I would think that your customers would be screaming for this functionality. All that it would take is for develop...

OLAP Cube for Excel 97?
Hi all, There's an option of getting data source from OLAP cube in Office 2000 or later application but not in 97 while creating pivot table. May I know if there's add-in download available that support this function for Excel 97? Thank you Hi I know a company called Applix used to do and addin - (many moons ago) might be worth looking them up. ttfn benm >-----Original Message----- >Hi all, > > There's an option of getting data source from OLAP cube in Office 2000 or >later application but not in 97 while creating pivot table. May I know if >...

Adding alternate columns
I work on a schedule where I record ticket count and ticket sales for each show, listed chronologically. Column A = week ending date Column B = # Tickets sold for April 5 show Column C = Ticket Revenue for April 5 show Column D = # Tickets sold for April 9 show Column E = Ticket Revenue for April 9 show Column F = # Tickets sold for April 12 show Column G = Ticket Revenue for April 12 show Column H = Total # Tickets sold for all shows Column I = Total Ticket Revenue for all shows In the Totals columns, the Total Tickets formula is "+B4+D4+F4", and the Total Revenue f...

Word 2008 Drawing toolbar
It seems to me that the drawing facilities are much less powerful than with the last version of MS Word for Mac. When drawing a line all I can get is a thick blue line. In fact everything is blue. What am I missing please. Thanks, BrianKR There's no question that the new capabilities are 'different' -- whether you choose to categorize them as 'better' or 'worse' is a matter of personal interpretation :-) The new tools are based on a completely redesigned graphics engine & the current version of Mac Office offers a somewhat limited implementation of the ...