Consulting

Results 1 to 14 of 14

Thread: Copying Bookmarks content and pasting to UserForm

  1. #1

    Copying Bookmarks content and pasting to UserForm

    Hi all.

    So I know you usually use a form to get input data to paste to a document, but I need to do the inverse. I have a Word document, restricted editing, with a few text form fields. Of these, 4 are bookmarked. I need to print these 4 fields instead of the entire document to create a kind of stub. Meaning I can hand the stub to someone and at a later date, if they give me the stub, I can refer back to the original document.

    Is there a way, once I press an ActiveX Button, to use VBA to copy the contents of the bookmarked field and copy it to a UserForm and then print said UserForm? The button is so that I can select which documents I need to print out the stub.

    Any help greatly appreciated.

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,118
    Location
    Lets say you have a dotx template file in the same folder as your main document named "Stub.dotx" In that template you have four bookmarks "A, B, C and D." In your main form you also have four bookmarks "A, B, C and D."

    Using this code you can print a stub document containing the data in the main form bookmarks:

    Option Explicit
    Private Sub UserForm_Initialize()
      txtA.Text = ActiveDocument.Bookmarks("A").Range.Text
      txtB.Text = ActiveDocument.Bookmarks("B").Range.Text
      txtC.Text = ActiveDocument.Bookmarks("C").Range.Text
      txtD.Text = ActiveDocument.Bookmarks("D").Range.Text
    lbl_Exit:
      Exit Sub
    End Sub
    
    Private Sub CommandButton1_Click()
    Dim oDoc As Document
      Set oDoc = Documents.Add(ThisDocument.Path & "\Stub.dotx")
      With oDoc
        .Bookmarks("A").Range.Text = txtA.Text
        .Bookmarks("B").Range.Text = txtB.Text
        .Bookmarks("C").Range.Text = txtC.Text
        .Bookmarks("D").Range.Text = txtD.Text
        .PrintOut
        .Close wdDoNotSaveChanges
      End With
      Unload Me
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    Quote Originally Posted by gmaxey View Post
    Lets say you have a dotx template file in the same folder as your main document named "Stub.dotx" In that template you have four bookmarks "A, B, C and D." In your main form you also have four bookmarks "A, B, C and D."

    Using this code you can print a stub document containing the data in the main form bookmarks:

    Option Explicit
    Private Sub UserForm_Initialize()
      txtA.Text = ActiveDocument.Bookmarks("A").Range.Text
      txtB.Text = ActiveDocument.Bookmarks("B").Range.Text
      txtC.Text = ActiveDocument.Bookmarks("C").Range.Text
      txtD.Text = ActiveDocument.Bookmarks("D").Range.Text
    lbl_Exit:
      Exit Sub
    End Sub
    
    Private Sub CommandButton1_Click()
    Dim oDoc As Document
      Set oDoc = Documents.Add(ThisDocument.Path & "\Stub.dotx")
      With oDoc
        .Bookmarks("A").Range.Text = txtA.Text
        .Bookmarks("B").Range.Text = txtB.Text
        .Bookmarks("C").Range.Text = txtC.Text
        .Bookmarks("D").Range.Text = txtD.Text
        .PrintOut
        .Close wdDoNotSaveChanges
      End With
      Unload Me
    End Sub
    Thanks for taking the time to reply. I have tried the code but get a Variable not defined.
    2020-02-13 17_58_17-Microsoft Visual Basic for Applications - AMB-007 Ficha controlo Rev06 [runn.jpg

    My VBA skills are close to non existent and only as good as my Google prowess (which is also pretty abismal). I tried defining the variables in both subs by doing
    Dim txtA As Variable
    which of course didn't work.

  4. #4
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,118
    Location
    You need to add a userform to the main document. In that userform add four text fields and title them txtA, txtB, txtC and txtD. You also need a command button titled CommnadButton1

    The code I sent you earlier goes in the UserForm module.

    In your main document add a macro in a standard module:

    Sub ScratchMacro()
    'A basic Word macro coded by Greg Maxey
    Dim oFrm As UserForm1
      Set oFrm = New UserForm1
      oFrm.Show
      Set oFrm = Nothing
    lbl_Exit:
      Exit Sub
    End Sub
    Add a button on your QAT to call ScratchMacro
    Greg

    Visit my website: http://gregmaxey.com

  5. #5
    Quote Originally Posted by gmaxey View Post
    You need to add a userform to the main document. In that userform add four text fields and title them txtA, txtB, txtC and txtD. You also need a command button titled CommnadButton1

    The code I sent you earlier goes in the UserForm module.

    In your main document add a macro in a standard module:

    Sub ScratchMacro()
    'A basic Word macro coded by Greg Maxey
    Dim oFrm As UserForm1
      Set oFrm = New UserForm1
      oFrm.Show
      Set oFrm = Nothing
    lbl_Exit:
      Exit Sub
    End Sub
    Add a button on your QAT to call ScratchMacro
    Ok. I am trying to follow you on this but I am a bit confused. So I have created the user form, and this is the userform code:
    Attachment 25979

    Then I created a module (Module1) and pasted the ScratchMacro code.

    However, I don't know how to call the ScratchMacro. I have the button, I can click view code. Do I paste the code here and rename the button to ScratchMacro?
    2020-02-13 19_16_15-Microsoft Visual Basic for Applications - AMB-007 Ficha controlo Rev06 [desi.jpg

    Sorry for the questions. I am pretty sure I am missing something. Do I have to create a second button, maybe? Since the first code seems to be linked to my current button.

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,118
    Location
    Don't use an activeX button in your document. Modify your Quick Access Toolbar and add a button that calls SratchMacro.
    Greg

    Visit my website: http://gregmaxey.com

  7. #7
    Quote Originally Posted by gmaxey View Post
    Don't use an activeX button in your document. Modify your Quick Access Toolbar and add a button that calls SratchMacro.
    Thanks for your patience. I have been playing around a bit with your take on this, and I managed to get the form to pop up and populate and print using the first portion of your text and using the aciveX button. So right now I have no module. Just the UserForm1.
    2020-02-13 19_47_49-Clipboard.jpg

    Then assigning this code to the button, results in the form popping up and printing (when not commented out, for sure).
    2020-02-13 19_49_34-Clipboard.jpg
    Now all I would need is to get rid of the "FORMTEXT " from each field and I'd be set. Any ideas?

  8. #8
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,118
    Location
    Well a bookmark that wraps a formfield is going to return that. So:

    Sub ScratchMacro()
    'A basic Word macro coded by Greg Maxey
      MsgBox Replace(ActiveDocument.Bookmarks("Description").Range.Text, "FORMTEXT ", "")
      MsgBox ActiveDocument.FormFields.Item("Description").Result
    lbl_Exit:
      Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  9. #9
    Quote Originally Posted by gmaxey View Post
    Well a bookmark that wraps a formfield is going to return that. So:

    Sub ScratchMacro()
    'A basic Word macro coded by Greg Maxey
      MsgBox Replace(ActiveDocument.Bookmarks("Description").Range.Text, "FORMTEXT ", "")
      MsgBox ActiveDocument.FormFields.Item("Description").Result
    lbl_Exit:
      Exit Sub
    End Sub
    Thanks.

    After tinkering around I have it good enough for my requirements. So in the end, the UserForm1 code is as follows:
    Option ExplicitPrivate Sub UserForm_Initialize()
    
    
      txtA.Text = Replace(ActiveDocument.Bookmarks("Number").Range.Text, "FORMTEXT ", "")
      txtA.Text = ActiveDocument.FormFields.Item("Number").Result
      txtB.Text = Replace(ActiveDocument.Bookmarks("Year").Range.Text, "FORMTEXT ", "")
      txtB.Text = ActiveDocument.FormFields.Item("Year").Result
      txtC.Text = Replace(ActiveDocument.Bookmarks("Author").Range.Text, "FORMTEXT ", "")
      txtC.Text = ActiveDocument.FormFields.Item("Author").Result
      txtD.Text = Replace(ActiveDocument.Bookmarks("Description").Range.Text, "FORMTEXT ", "")
      txtD.Text = ActiveDocument.FormFields.Item("Description").Result
    
    
    lbl_Exit:
      Exit Sub
    End Sub
    And the button code:
    Private Sub CommandButton1_Click()Dim oFrm As UserForm1
      Set oFrm = New UserForm1
      oFrm.Show
      Set oFrm = Nothing
      UserForm1.PrintForm
    lbl_Exit:
      Exit Sub
    End Sub

  10. #10
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,118
    Location
    Well you are repeating yourself four times. You can use either of the two methods I showed you but you don't need both.

    Use:
    txtA.Text = ActiveDocument.FormFields.Item("Number").Result
    Greg

    Visit my website: http://gregmaxey.com

  11. #11
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,118
    Location
    So I am assuming that despite the advice otherwise, you are using a ActiveX button to execute your code. Okay. In your ActiveX code:

    Sub ScratchMacro()
    'A basic Word macro coded by Greg Maxey
      Dim oFrm As UserForm1
      Set oFrm = New UserForm1
      oFrm.PrintForm
      Set oFrm = Nothing
    lbl_Exit:
      Exit Sub
    End Sub
    In your userform:

    Private Sub UserForm_Initialize()
      txtA.Text = ActiveDocument.FormFields.Item("Number").Result
      txtB.Text = ActiveDocument.FormFields.Item("Year").Result
      txtC.Text = ActiveDocument.FormFields.Item("Author").Result
      txtD.Text = ActiveDocument.FormFields.Item("Description").Result
    lbl_Exit:
      Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  12. #12
    Quote Originally Posted by gmaxey View Post
    So I am assuming that despite the advice otherwise, you are using a ActiveX button to execute your code. Okay. In your ActiveX code:

    Sub ScratchMacro()
    'A basic Word macro coded by Greg Maxey
      Dim oFrm As UserForm1
      Set oFrm = New UserForm1
      oFrm.PrintForm
      Set oFrm = Nothing
    lbl_Exit:
      Exit Sub
    End Sub
    In your userform:

    Private Sub UserForm_Initialize()
      txtA.Text = ActiveDocument.FormFields.Item("Number").Result
      txtB.Text = ActiveDocument.FormFields.Item("Year").Result
      txtC.Text = ActiveDocument.FormFields.Item("Author").Result
      txtD.Text = ActiveDocument.FormFields.Item("Description").Result
    lbl_Exit:
      Exit Sub
    End Sub
    Sorry, believe me that if I was able to troubleshoot and doo any better, I would. I have always been a tinker kind of guy, and pulling at the threads of your code gave me a pretty workable result. Nonetheless, I do appreciate you still trying to help me. I will give this a try for sure.

    If all else fails, at least what I have now is "workable". I still have an issue with a phantom userform1 that doesn't seem to want to go away after printing and only disappears when I exit the word document, but other than that, the current solution isn't half bad.

    I have now integrated your code into the file. I had to change ScratchMacro to CommandButton1_Click. It works great, but I still get the aforementioned phantom window.

    In any case, honestly, I do very much appreciate your input. Please don't think I mean any disrespect. It's just that my limited knowledge of VBA tends to make me choose the simplest path, even when it's not elegant. Hell, most of the files I have cobbled together VBA scripts to suite my needs would probably make an experienced programmer cry in anger. But I am always learning something with all this info and through trial and error.

    This is the file as it currently stands after your feedback:
    https://www.dropbox.com/scl/fi/q3p0r...o37reb3cua618k

    And I created a video with the phantom form that lingers on top of everything making an appearance at second 44 on the top left corner.
    https://youtu.be/zTxJNPKjky8

    I made the video before your current changes, though the issue still happens.

    Edit: using your ScratchMacro code and replacing my print button code with it made the phantom window go away. So now everything is working great!

    Thanks for your help. No way I would have gotten this done in such short time without you.
    Last edited by nmgmarques; 02-13-2020 at 05:22 PM.

  13. #13
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,118
    Location
    In your userform, use this (there is no need to print the command buttons)

    Option Explicit
    Private Sub CommandButton2_Click()
      CommandButton2.Visible = False
      CommandButton3.Visible = False
      PrintForm
      Hide
    lbl_Exit:
      Exit Sub
    End Sub
    Private Sub CommandButton3_Click()
      Hide
    lbl_Exit:
      Exit Sub
    End Sub
    
    Private Sub UserForm_Initialize()
      txtA.Text = ActiveDocument.FormFields.Item("Number").Result
      txtB.Text = ActiveDocument.FormFields.Item("Year").Result
      txtC.Text = ActiveDocument.FormFields.Item("Author").Result
      txtD.Text = ActiveDocument.FormFields.Item("Description").Result
    lbl_Exit:
      Exit Sub
    End Sub
    In your document module, use this:

    Private Sub CommandButton1_Click()
    'A basic Word macro coded by Greg Maxey
      Dim oFrm As UserForm1
      Set oFrm = New UserForm1
      oFrm.Show
      Unload oFrm
      Set oFrm = Nothing
    lbl_Exit:
      Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  14. #14
    Quote Originally Posted by gmaxey View Post
    In your userform, use this (there is no need to print the command buttons)

    Option Explicit
    Private Sub CommandButton2_Click()
      CommandButton2.Visible = False
      CommandButton3.Visible = False
      PrintForm
      Hide
    lbl_Exit:
      Exit Sub
    End Sub
    Private Sub CommandButton3_Click()
      Hide
    lbl_Exit:
      Exit Sub
    End Sub
    
    Private Sub UserForm_Initialize()
      txtA.Text = ActiveDocument.FormFields.Item("Number").Result
      txtB.Text = ActiveDocument.FormFields.Item("Year").Result
      txtC.Text = ActiveDocument.FormFields.Item("Author").Result
      txtD.Text = ActiveDocument.FormFields.Item("Description").Result
    lbl_Exit:
      Exit Sub
    End Sub
    In your document module, use this:

    Private Sub CommandButton1_Click()
    'A basic Word macro coded by Greg Maxey
      Dim oFrm As UserForm1
      Set oFrm = New UserForm1
      oFrm.Show
      Unload oFrm
      Set oFrm = Nothing
    lbl_Exit:
      Exit Sub
    End Sub
    Thanks for that. It's printing fine now, and without the buttons. I appreciate your efforts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •