Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: VBA for Word

  1. #1
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA for Word

    Hi

    I'm new to VBA. I've created a template with lots of userforms on it. What I want to do is once i have completed my form, and moved on to the next user form, be able to go back to my previous form, I can manage to do this, but what is happening is that the text the user has input into the form disappears when I click the commend button to go back to the previous form. Can anyone point me in the right direction?

    Thanks

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    856
    Thanks
    0
    Thanked 9 Times in 8 Posts
    Are you unloading the first form before you show the next form? If you don't unload each form (save the unload statements until the end), the values should still be in place when you show the form again. Also, be aware that the Initialize event of the form fires when the form is first loaded into memory. The Activate event fires each time the form is shown. You might want to be sure you use Initialize instead of Activate. Depending on what you're doing, code in the Activate event might be overwriting the values.

  4. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    195
    Thanks
    4
    Thanked 45 Times in 37 Posts
    Taking another tack, I'd be curious about why a template needs "lots" of userforms. Users quickly get annoyed when one userform after another pops up. Can you combine and condense the userforms, maybe using tabs or a multipage control if that makes sense?

    Another suggestion: Instead of directly pushing text from each userform into the body of the document and leaving the userform in memory, save the data from the userform's controls into document variables and then unload the userform. In the userform's Initialize procedure, first try to load the controls with data from the document variables; if they don't exist, then leave the controls blank (this will always happen the first time the userform is shown). Use DocVariable fields in the body of the document to display the text of the document variables. For examples, see http://gregmaxey.mvps.org/word_tip_p..._userform.html.

  5. #4
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Richard

    I am unloading each form before I start the next:-


    frmdirectory.Hide
    Unload frmdirectory
    frmUserForm1.Show

    I have no code in my Activate even, only this code in my Initialize


    Private Sub UserForm_Initialize()
    cmbprelimlocation.AddItem ("Aberdeen")
    cmbprelimlocation.AddItem ("Belfast")
    cmbprelimlocation.AddItem ("Edinburgh")
    cmbprelimlocation.AddItem ("Glasgow")
    cmbprelimlocation.AddItem ("Leeds")
    cmbprelimlocation.AddItem ("London")
    cmbprelimlocation.AddItem ("Manchester")



    End Sub

    Thanks

    Angie

  6. #5
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Richard - I'm all sorted thank you very very much.

    JJFreedman, thank you also.

  7. #6
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahh - but when I now move forward after going to the previous form the text is now in twice?

  8. #7
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Richard

    Moving saving all the unloads to the end works a treat - but now if I have text in and go forward and backward I have double text in my output document from where I have gone back -does that make sense??

    Angie

  9. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    856
    Thanks
    0
    Thanked 9 Times in 8 Posts
    Before you fill the combo box, check to see if the .ListCount is greater than zero (If .ListCount < 0) then add the items if the count is 0. If the count is already greater than 0, you'll know that the combo box has been filled and you don't need to add the items again.

    You won't want to .Clear each time, because that will lose whatever the user selected previously.

  10. #9
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the list boxes are fine Richard, its all the text in my text boxes which has double text in it.

  11. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    856
    Thanks
    0
    Thanked 9 Times in 8 Posts
    Do you have code that fills the text boxes? For example, does the text property of a text control change depending on the user's selection in the combo box? Even so, txtName.text = "Richard" would overwrite the existing value, not append to it. Are you sure that there is nothing in your code that appends text to the existing text of your text boxes?

  12. #11
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    661
    Thanks
    3
    Thanked 19 Times in 18 Posts
    Are you inserting the text as you dismiss the userforms? Perhaps if you wait and put them all in after the last form is filled?

    Best, Kim

  13. #12
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's my code for my command button, this would put the text into the boxed and go to the next userform:

    Private Sub cmdPage3_Click()

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewfirst"
    Selection.TypeText Text:=txtdeptnewfirst

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewsur"
    Selection.TypeText Text:=txtdeptnewsur

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewjoin"
    Selection.TypeText Text:=txtdeptnewjoin

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewcomment"
    Selection.TypeText Text:=txtdeptnewcomment

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewfirst2"
    Selection.TypeText Text:=txtdeptnewfirst2

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewsur2"
    Selection.TypeText Text:=txtdeptnewsur2

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewjoin2"
    Selection.TypeText Text:=txtdeptnewjoin2

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewcomment2"
    Selection.TypeText Text:=txtdeptnewcomment2

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewfirst3"
    Selection.TypeText Text:=txtdeptnewfirst3

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewsur3"
    Selection.TypeText Text:=Txtdeptnewsur3

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewjoin3"
    Selection.TypeText Text:=txtdeptnewjoin3

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewcomment3"
    Selection.TypeText Text:=txtdeptnewcomment3

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewfirst4"
    Selection.TypeText Text:=txtdeptnewfirst4

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewsur4"
    Selection.TypeText Text:=txtdeptnewsur4

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewjoin4"
    Selection.TypeText Text:=txtdeptnewjoin4

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewcomment4"
    Selection.TypeText Text:=txtdeptnewcomment4

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewfirst5"
    Selection.TypeText Text:=txtdeptnewfirst5

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewsur5"
    Selection.TypeText Text:=txtdeptnewsur5

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewjoin5"
    Selection.TypeText Text:=txtdeptnewjoin5

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewcomment5"
    Selection.TypeText Text:=txtdeptnewcomment5

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewfirst6"
    Selection.TypeText Text:=txtdeptnewfirst6

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewsur6"
    Selection.TypeText Text:=txtdeptnewsur6

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewjoin6"
    Selection.TypeText Text:=txtdeptnewjoin6

    Selection.GoTo what:=wdGoToBookmark, Name:="deptnewcomment6"
    Selection.TypeText Text:=txtdeptnewcomment6

    Selection.GoTo what:=wdGoToBookmark, Name:="departfirst"
    Selection.TypeText Text:=txtDepartfirst

    Selection.GoTo what:=wdGoToBookmark, Name:="departsur"
    Selection.TypeText Text:=txtdepartsur

    Selection.GoTo what:=wdGoToBookmark, Name:="departdest"
    Selection.TypeText Text:=txtdepartdest

    Selection.GoTo what:=wdGoToBookmark, Name:="departfirst2"
    Selection.TypeText Text:=txtdepartfirst2

    Selection.GoTo what:=wdGoToBookmark, Name:="departsur2"
    Selection.TypeText Text:=txtdepartsur2

    Selection.GoTo what:=wdGoToBookmark, Name:="departdest2"
    Selection.TypeText Text:=txtdepartdest2

    Selection.GoTo what:=wdGoToBookmark, Name:="departfirst3"
    Selection.TypeText Text:=txtdepartfirst3

    Selection.GoTo what:=wdGoToBookmark, Name:="departsur3"
    Selection.TypeText Text:=txtdepartsur3

    Selection.GoTo what:=wdGoToBookmark, Name:="departdest3"
    Selection.TypeText Text:=txtdepartdest3

    Selection.GoTo what:=wdGoToBookmark, Name:="departfirst4"
    Selection.TypeText Text:=txtdepartfirst4

    Selection.GoTo what:=wdGoToBookmark, Name:="departsur4"
    Selection.TypeText Text:=txtdepartsur4

    Selection.GoTo what:=wdGoToBookmark, Name:="departdest4"
    Selection.TypeText Text:=txtdepartdest4

    Selection.GoTo what:=wdGoToBookmark, Name:="departfirst5"
    Selection.TypeText Text:=txtdepartfirst5

    Selection.GoTo what:=wdGoToBookmark, Name:="departsur5"
    Selection.TypeText Text:=txtdepartsur5

    Selection.GoTo what:=wdGoToBookmark, Name:="departdest5"
    Selection.TypeText Text:=txtdepartdest5

    frmUserForm1.Hide
    frmUserForm2.Show


    End Sub


    This is the code for my back button to take me back to the previous form

    Private Sub CommandButton1_Click()
    frmUserForm1.Hide
    frmdirectory.Show

    End Sub

  14. #13
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Kim, thats something else I will try if I move all the code for each form to the very last "OK" button, how will the last form know which form to enter the text for? Angie
    Last edited by angiec69; 2013-10-10 at 04:10.

  15. #14
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    856
    Thanks
    0
    Thanked 9 Times in 8 Posts
    So the "double" text was not in the dialog box... it was in the document. Don't put your code to fill the bookmarks "behind" the OK button. Put it in the module where you run the macro from. Show the user forms one at a time, and run you code when the user clicks OK in the last form.

    After the last form, your code would reference each previous form by name:
    Code:
    With frmOne
     ActiveDocument.Bookmarks("Name").Range.Text = .txtName.text
    end With
    Code:
    With frmTwo
     ActiveDocument.Bookmarks("City").Range.Text = .txtCity.text
    End With
    Also, you can write a small function to fill bookmarks:

    Code:
    Function FillBookmark(sBookmarkName As String, sText As String)
        With ActiveDocument
            If .Bookmarks.Exists(sBookmarkName) <> False Then
                .Bookmarks(sBookmarkName).Range.Text = sText
                .Bookmarks(sBookmarkName).Delete ' option code if you want to delete the bookmark
            End If
        End With
    End Function
    
    Sub testbm()
        FillBookmark "Name", "Sample Text"
    End Sub
    This way, you need only one line of code to fill a bookmark, and with the Range object, your cursor won't have to "GoTo" each bookmark. The code will run faster, and the screen won't flicker.

  16. #15
    Lounger
    Join Date
    Jul 2008
    Location
    Edinburgh, Midlothian, United Kingdom
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Richard, I will give that a go. You guys rock!

Page 1 of 2 12 LastLast

Posting Permissions

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