Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help creating VBA userform in Word

    Hello...I'm new to the vba world. I'm looking to create a userform in word that has multiple check boxes (>50) and a command button. The user will have the ability to check 1 or 10 or all 50 boxes. Once the command button had been pressed it will load the .dot template(s) (referenced by the checkbox) file from another location into a master document. each time it populates the information from a checkbox location it will advance one page in the master document. I've been trying to do something with if then else functions but i'm thinking there is something much easier.
    Any help would be appreciated. thanks. Greg
    Attached Images Attached Images

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Personally, I think you are going about this a long way around.

    Firstly, I would store the '1 page templates' as building blocks in 'the template'. This way you only have one file to deal with.

    Secondly, you can use one of the properties of each checkbox to store information related to the building block (or template if you ignore my first point) so that a loop can be employed on the checkboxes. The following code for instance returns the value of the tag property on each checked checkbox. If you replaced the msgbox with a line of code to insert the building block (or template) you would be up and running.
    Code:
    Private Sub cmbOK_Click()
      Dim aCtl As Control
      For Each aCtl In Me.Controls
        If TypeName(aCtl) = "CheckBox" And aCtl.Tag <> "" And aCtl = True Then
          MsgBox aCtl.Tag
        End If
      Next aCtl
    End Sub
    Last edited by Andrew Lockton; 2014-11-01 at 21:59.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks for your help.
    I've never heard of building blocks. sounds like this could be a much cleaner approach. how do i recall a building block with a checkbox?
    I was unsuccessful getting the above code to work. I must be doing something wrong. Can I just use that code for a single check box?
    Thanks again.

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Do you want to use the code for a single check box? This was not what you originally asked for. I thought the principle was to create a macro that would run AFTER all the relevant check boxes were ticked. The trigger for the code I supplied would therefore be something like an OK button (which in my sample was named cmbOK).

    You could create code for each and every checkbox but that is much more complicated & repetitive and you would get content you didn't want if the user checked a box and then changed their mind and unchecked it.

    Try recording a macro of inserting a building block. This will show you what that line of code looks like and where you could insert the tag string.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ideally i would like multiple checkboxes and the code (as you provide) to activate once they have been checked or not checked.
    Do the check boxes have to be in sub routine within the code you provided?


    Private Sub CheckBox1_Click()

    ChangeFileOpenDirectory "C:\Users\Greg\Desktop\CSA\"
    Documents.Open FileName:="template.docx", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.Copy
    ActiveDocument.Close
    Selection.PasteAndFormat (wdPasteDefault)

    End Sub


    Private Sub CommandButton1_Click()

    Dim aCtl As Control
    For Each aCtl In Me.Controls
    If TypeName(aCtl) = "CheckBox" And aCtl.Tag <> "" And aCtl = True Then
    'insert file here aCtl.Tag

    End If
    Next aCtl
    End Sub

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Your code shows you are only copying a single line of text from the source document. Why have external documents if that is all they are going to provide??? This is a REALLY inefficient way of importing content since each source document needs to be opened and handled requiring seconds per document. But working along with your existing methodology and assuming you want the entire contents of each source file...

    Put the name of the relevant source file into the tag property of each checkbox. Don't have any code for individual checkboxes and simply include this code for the CommandButton1_Click
    Code:
    Private Sub cmbOK_Click()
      Dim aCtl As Control, sDocName As String
      ChangeFileOpenDirectory "C:\Users\Greg\Desktop\CSA\"
      For Each aCtl In Me.Controls
        sDocName = aCtl.Tag
        If TypeName(aCtl) = "CheckBox" And sDocName <> "" And aCtl = True Then
          Selection.InsertFile FileName:=sDocName, Range:="", ConfirmConversions:=False, Link:=False, Attachment:=False
        End If
      Next aCtl
      Me.Hide
      Unload Me
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Andrew. I got the tag property function to work. Is there an index for the checkboxes? it appears to populate my document based on the order of my checbox selections. Can I index them from top to bottom? is this available in the properties?
    Thanks,
    Greg

  8. #8
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    this what I have thus far.


    Private Sub CommandButton1_Click()
    ChangeFileOpenDirectory "D:\"
    Documents.Open FileName:="""test packv.docx""", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    ActiveWindow.ActivePane.VerticalPercentScrolled = 54
    Selection.MoveDown Unit:=wdLine, Count:=130
    Selection.MoveUp Unit:=wdLine, Count:=22
    Selection.TypeParagraph
    Selection.TypeParagraph
    ForTag
    End Sub


    Private Sub ForTag()


    DocumentCreated = False


    For Each Control In UserForm1.Controls

    If TypeName(Control) = "CheckBox" Then
    If Control.Value = True Then
    '
    If DocumentCreated = False Then

    '
    DocumentCreated = True
    End If
    Selection.InsertBreak Type:=wdPageBreak
    Selection.InsertFile FileName:=Control.Tag, Range:="", _
    ConfirmConversions:=False, Link:=False, Attachment:=False

    'Application.Move Left:=157, Top:=87



    End If
    End If
    Next
    End Sub

  9. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    I can't work out how to change the order that the controls are arranged in. You can change their tab index for using the tab key to move between them. You can also cut them from the form and paste them into another form and them bring them back onto the original form one at a time but that only has partial success at changing the loop order.

    I would instead add bookmark ranges in the document where the relevant content needs to go. If the bookmarks are arranged in the correct order then your content could be inserted in the right place regardless of the loop order eg

    ActiveDocument.Bookmarks(aCtl.Name).Range.InsertFi le FileName:=sDocName, ConfirmConversions:=False, Link:=False, Attachment:=False
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #10
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andrew,
    I'm back. I've got most of it working. Thanks again.
    However, I'm stuck on the last part. If you recall I'm recalling multiple *.dot files into a master doc. Problem is that I would also like to build an index with the titles from each .dot file. I know I can set these up as an index but was wondering if there is another way that I can bring in these docs to build a master index that I can customize in a table.
    Thanks,
    Greg

  11. #11
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Are you talking about an 'index' or a 'Table of Contents'?

    If it is a TOC you are after, you just need to control the style of the contents you are inserting into the document. If it is an 'index' then you could either have the multiple dot files already containing {XE xxx} index fields or you could add these fields using a concatenation table.

    I'm not sure I can help you further without actually seeing what you are doing? From what I have understood so far this seems like a very roundabout way of doing relatively simple things.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  12. #12
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry for the delay...things have been a little busy.
    I could make a TOC work. problem is i would like a slightly more organized table. That's what i was thinking. Just create a title for each template and then i can suck them in at the end.

    I have another question. When i call the userform in word it doesn't load properly unless i first load a specific word file. Is there an easy fix for this? i would just like to call the form with a macro (which i have done) but if the file isn't loaded it crashes.

    The error message is "run-time error '5479' you cannot close ms office word because a dialog box is open. click ok, switch to word, and then close the dialog box.

  13. #13
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    It is not a standard problem for a userform to require another document in order to load. This implies that the code that opens the userform is also requesting information from that other document. This could either be in the macro that calls the userform or possibly in the initialise code of the userform itself. If the userform also sets values of fields that have onchange code then the issue could also be there.

    You haven't provided enough information for us to determine what exactly is causing your error. If you need help to solve this then you will need to post either the document or the relevant macros.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  14. #14
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew Lockton View Post
    It is not a standard problem for a userform to require another document in order to load. This implies that the code that opens the userform is also requesting information from that other document. This could either be in the macro that calls the userform or possibly in the initialise code of the userform itself. If the userform also sets values of fields that have onchange code then the issue could also be there.

    You haven't provided enough information for us to determine what exactly is causing your error. If you need help to solve this then you will need to post either the document or the relevant macros.




    Private Sub createtestpack1_Click()
    AddSections1
    End Sub

    Private Sub AddSections1()

    SelectionMade = False
    '
    For Each Control In Userform1.Controls
    If TypeName(Control) = "CheckBox" Then
    If Control.Value = True Then
    SelectionMade = True
    Exit For
    End If
    End If
    Next
    '
    If SelectionMade = True Then


    FileLocation = "D:\ordloc"

    Documents.Open FileName:="D:\ordloc\testpackord.dot", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    Selection.MoveDown Unit:=wdLine, Count:=41
    Selection.TypeParagraph

    If Userform1.CheckBox101.Value = True Then
    ChangeFileOpenDirectory "D:\ordloc"
    Selection.TypeParagraph
    Selection.InsertFile FileName:="temperature.dot", Range:="", _
    ConfirmConversions:=False, Link:=False, Attachment:=False
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.InsertBreak Type:=wdPageBreak
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

    End If

    Unload Me
    Documents("testpack.docm").Close SaveChanges:=wdDoNotSaveChanges
    End Sub
    Private Sub exit1_Click()
    Unload Me
    End Sub


    this is basically my code. the trestpack.docm is the doc that i use to open the userform. if i remove the unload function it still doesn't work without opening a specific word doc.(testpack.docm)

    Thanks,
    Greg

  15. #15
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Your code shows several areas of confusion that you will need to iron out to get back to some clarity. For instance, after you unload the form (which is where the code resides), how do you expect the subsequent code to be available?

    Also, you make use of the selection object after opening a document. Which document do you think this selection object is in - the document you had open when you opened the userform or the new one you just opened? This confusion needs to be cleared up before we solve your other issues.

    You should be naming the documents you have open and then you can make use of ranges so you can be explicit about what you are trying to manipulate.

    Dim aDoc as Document, aDocNew as Document
    Set aDoc = ActiveDocument
    Set aDocNew = Documents.Open(FileName:="D:\ordloc\testpackord.do t", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, Revert:=False, Format:=wdOpenFormatAuto)
    Andrew Lockton, Chrysalis Design, Melbourne Australia

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
  •