• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1428
  • Last Modified:

Create Button Macro-- Move to End of Document

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
epuglise
Asked:
epuglise
  • 6
  • 6
  • 5
  • +2
3 Solutions
 
R_RajeshCommented:
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
 
R_RajeshCommented:
you can also try making use of the "\EndOfDoc" bookmark

Set ILS = ActiveDocument.Bookmarks("\EndOfDoc").Range.InlineShapes.AddOLEControl("Forms.CommandButton.1")
0
 
bruintjeCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
epugliseAuthor Commented:
Boy, it really doesn't like it if you're putting in more than one button.  CrrrrrrrrAAAAAASH!
0
 
Joanne M. OrzechManager, Document Services CenterCommented:
Selection.EndKey Unit:=wdStory


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

Truly...
0
 
R_RajeshCommented:
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
 
R_RajeshCommented:
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
 
Joanne M. OrzechManager, Document Services CenterCommented:
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
 
Joanne M. OrzechManager, Document Services CenterCommented:
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
 
R_RajeshCommented:
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
 
R_RajeshCommented:
it was great, thanks :) (all my friends are tired of hearing me brag about it ;)
0
 
Joanne M. OrzechManager, Document Services CenterCommented:
LOL - good for you Rajesh!  You have every reason to be proud.
0
 
Joanne M. OrzechManager, Document Services CenterCommented:
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
 
epugliseAuthor Commented:
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
 
epugliseAuthor Commented:
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
 
GrahamSkanRetiredCommented:

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
 
epugliseAuthor Commented:
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
 
epugliseAuthor Commented:
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
 
Joanne M. OrzechManager, Document Services CenterCommented:
Thanks epuglise!  Glad it's working for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 6
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now