Solved

Create Button Macro-- Move to End of Document

Posted on 2004-09-24
19
1,409 Views
Last Modified: 2008-01-09
I have a macro that creates a button, but I want it to put the button at the end of the document:

Sub AddCommandButton(ButtonName As String)
'***********************************************
'*Note:  For this to work, you must have a reference to Microsoft's VB for Apps Ext.
'*      1. In the VBA IDE, click on the 'Tools' menu item and select 'References...'
'*         from the drop-down list.
'*      2. In the 'Available References:' box of the 'References' window, scroll down
'*         to 'Microsoft Visual Basic for Applications Extensibility n.n'
'*      3. Click on the check box beside the name and then click OK near the top right
'*         of the dialog box.
'************************************************

Dim vbp As VBProject
Dim vbComp As VBComponent
Dim ILS As InlineShape

********'This doesn't seem to do it:
Selection.EndKey unit:=wdStory

Set ILS = ActiveDocument.InlineShapes.AddOLEControl("Forms.CommandButton.1")

Select Case ButtonName
    Case "GenDocs"
    ILS.OLEFormat.Object.Caption = "Generate Documents"
    ILS.OLEFormat.Object.Name = "GenerateDocs"
    ILS.OLEFormat.Object.AutoSize = True

    Set vbp = ActiveDocument.VBProject
    Set vbComp = vbp.VBComponents("ThisDocument")
    vbComp.CodeModule.AddFromString "Sub GenerateDocs_Click()" & vbCrLf & _
                                "   Call GenDocs " & vbCrLf & _
                                "End Sub"
   
    Case "UpdateColor"
    ILS.OLEFormat.Object.Caption = "Update Cell Colors"
    ILS.OLEFormat.Object.Name = "UpdateColor"
    ILS.OLEFormat.Object.AutoSize = True


    Set vbp = ActiveDocument.VBProject
    Set vbComp = vbp.VBComponents("ThisDocument")
    vbComp.CodeModule.AddFromString "Sub UpdateColor_Click()" & vbCrLf & _
                                "   UpdateCellColors" & vbCrLf & _
                                "End Sub"
   
    Case "NoColor"
    ILS.OLEFormat.Object.Caption = "Remove Cell Colors"
    ILS.OLEFormat.Object.Name = "NoColor"
    ILS.OLEFormat.Object.AutoSize = True

    Set vbp = ActiveDocument.VBProject
    Set vbComp = vbp.VBComponents("ThisDocument")
    vbComp.CodeModule.AddFromString "Sub NoColor_Click()" & vbCrLf & _
                                "   RemoveColor" & vbCrLf & _
                                "End Sub"
   
    End Select
   
End Sub
0
Comment
Question by:epuglise
  • 6
  • 6
  • 5
  • +2
19 Comments
 
LVL 24

Assisted Solution

by:R_Rajesh
R_Rajesh earned 100 total points
ID: 12147810
Hi epuglise,

try changing your code to this:

Set ILS = ActiveDocument.Range(ActiveDocument.Range.End - 1, ActiveDocument.Range.End - 1).InlineShapes.AddOLEControl("Forms.CommandButton.1")

Rajesh
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 12147856
you can also try making use of the "\EndOfDoc" bookmark

Set ILS = ActiveDocument.Bookmarks("\EndOfDoc").Range.InlineShapes.AddOLEControl("Forms.CommandButton.1")
0
 
LVL 44

Expert Comment

by:bruintje
ID: 12147857
does it help if you include the extend attribute like

  Selection.HomeKey Unit:=wdStory ' make sure you're at the start of the document
  Selection.EndKey Unit:=wdStory, Extend:=wdMove ' then move to the end
0
 

Author Comment

by:epuglise
ID: 12147885
Boy, it really doesn't like it if you're putting in more than one button.  CrrrrrrrrAAAAAASH!
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 12147958
Selection.EndKey Unit:=wdStory


Maybe you just need to reboot epuglise and try again :)

Truly...
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 12147987
Hi JO,

not sure why but it doesn't seem to work

Selection.EndKey unit:=wdStory

if you break right after the above line and look at the doc the cursor will be at the end of the doc. but the following line still creates the button at the top of the doc

Set ILS = ActiveDocument.InlineShapes.AddOLEControl("Forms.CommandButton.1")

0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 12148059
expression.AddOLEControl(ClassType, Range)

i guess since the range parameter is being left blank, its defaulting to 0 [activedocument.range(0,0)] Irrespective of where the cursor is.

it works if the range is mentioned
Set ILS = ActiveDocument.InlineShapes.AddOLEControl("Forms.CommandButton.1", ActiveDocument.Bookmarks("\EndOfDoc").Range)


0
 
LVL 37

Accepted Solution

by:
Joanne M. Orzech earned 200 total points
ID: 12148074
Thanks Rajesh - this is the line that works for me if I'm at the top of the document and run this - it puts the command button at the end of the document:

    Selection.EndKey Unit:=wdStory
    Selection.InlineShapes.AddOLEControl ClassType:="Forms.CommandButton.1"
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 12148097
Hey Rajesh -

BTW - how'd you like the write up in the EE newsletter?  I hope it was okay.  Well deserved... wish I could have said more.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:R_Rajesh
ID: 12148145
yes, changing activedocument.inlineshapes to selection.inlineshapes does the trick :)

Set ILS = ActiveDocument.InlineShapes.AddOLEControl("Forms.CommandButton.1")

Set ILS = Selection.InlineShapes.AddOLEControl("Forms.CommandButton.1")
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 12148169
it was great, thanks :) (all my friends are tired of hearing me brag about it ;)
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 12148593
LOL - good for you Rajesh!  You have every reason to be proud.
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 12148743
So Elizabeth - try this code:

Sub AddCommandButton(ButtonName As String)
'***********************************************
'*Note:  For this to work, you must have a reference to Microsoft's VB for Apps Ext.
'*      1. In the VBA IDE, click on the 'Tools' menu item and select 'References...'
'*         from the drop-down list.
'*      2. In the 'Available References:' box of the 'References' window, scroll down
'*         to 'Microsoft Visual Basic for Applications Extensibility n.n'
'*      3. Click on the check box beside the name and then click OK near the top right
'*         of the dialog box.
'************************************************

Dim vbp As VBProject
Dim vbComp As VBComponent
Dim ILS As InlineShape

Selection.EndKey unit:=wdStory
Set ILS = Selection.InlineShapes.AddOLEControl("Forms.CommandButton.1")

Select Case ButtonName
    Case "GenDocs"
    ILS.OLEFormat.Object.Caption = "Generate Documents"
    ILS.OLEFormat.Object.Name = "GenerateDocs"
    ILS.OLEFormat.Object.AutoSize = True

    Set vbp = ActiveDocument.VBProject
    Set vbComp = vbp.VBComponents("ThisDocument")
    vbComp.CodeModule.AddFromString "Sub GenerateDocs_Click()" & vbCrLf & _
                                "   Call GenDocs " & vbCrLf & _
                                "End Sub"
   
    Case "UpdateColor"
    ILS.OLEFormat.Object.Caption = "Update Cell Colors"
    ILS.OLEFormat.Object.Name = "UpdateColor"
    ILS.OLEFormat.Object.AutoSize = True


    Set vbp = ActiveDocument.VBProject
    Set vbComp = vbp.VBComponents("ThisDocument")
    vbComp.CodeModule.AddFromString "Sub UpdateColor_Click()" & vbCrLf & _
                                "   UpdateCellColors" & vbCrLf & _
                                "End Sub"
   
    Case "NoColor"
    ILS.OLEFormat.Object.Caption = "Remove Cell Colors"
    ILS.OLEFormat.Object.Name = "NoColor"
    ILS.OLEFormat.Object.AutoSize = True

    Set vbp = ActiveDocument.VBProject
    Set vbComp = vbp.VBComponents("ThisDocument")
    vbComp.CodeModule.AddFromString "Sub NoColor_Click()" & vbCrLf & _
                                "   RemoveColor" & vbCrLf & _
                                "End Sub"
   
    End Select
   
End Sub
0
 

Author Comment

by:epuglise
ID: 12156841
y'all-- this worked great!  I can run the macro, let it execute and then execute it again for each of the three types of buttons; however, when I call this button-creating macro from another macro, e.g.,

Call AddCommandButton("GenDocs")
Call AddCommandButton("UpdateCellColor")
Call AddCommandButton("NoColor")

Word crashes every time.  How can I put a pause in there so whatever it is processing can complete before the next command button call is made?  Or is there a better approach?

thx!

e
0
 

Author Comment

by:epuglise
ID: 12157035
By the way, just a little tidbit.  If you ever want to use a macro to remove the button, I found that the find-and-replace on "^g" (graphic) works (as long as you don't have a ton of graphics and/or you set a range for the find and replace).  I don't know how to actually code to remove the button (I could never figure out how to "goto" the button object, and once you're in design mode, the macro halts)... but I thought this little find-and-replace technique was a nifty little workaround.

e
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 200 total points
ID: 12158143

Sub AddGenDocsButton()
    Call AddCommandButton("GenDocs")
    Application.OnTime DateAdd("s", 1, Now), "AddUpDateCellColorButton"
End Sub

Sub AddUpDateCellColorButton()
    Call AddCommandButton("UpdateCellColor")
    Application.OnTime DateAdd("s", 1, Now), "AddNoColorButton"
End Sub

Sub AddNoColorButton()
    Call AddCommandButton("NoColor")
End Sub



0
 

Author Comment

by:epuglise
ID: 12254705
Graham:  I've been away w/ mono (!! glad computers don't catch our viruses, aren't you!)... and I missed this post in my emails.  I am anxious to try it!!  I'll let you know how it goes...  I'm wrestling with templates now...

e
0
 

Author Comment

by:epuglise
ID: 12267359
Wow!  I appreciate all the help.  I gave points to R 'cause his solution works.
I gave Jo 200 'cause her solution works and it's the one I used (less typing, I think?  Looks more obvious to someone maintaining the code what is happening??)

These two actually answered the question I had

And 200 to Graham 'cause he answered the "bonus" question of multiple buttons.

Thanks everyone for helping me with my multi-part problem!

e
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 12270890
Thanks epuglise!  Glad it's working for you.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Like many others, we try and discourage users from printing documents unnecessarily and instead send or share them electronically. However, this doesn't always work and documents are still printed. With this simple solution, if the user tries to …
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now