Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User form help! (2000)

    Hi,
    I have previously posted regarding creating a word document template using VB.
    I have been advised that using a user form would be the best way to do it as I need drop down lists of names. From this list I then need to extract that persons title and also their initial.
    So drop list=
    John Jeremy Doe
    Text box under his name fills with:
    Senior Marketing Manager
    Text box at top of letter fills with:
    JJD
    I'm no good with coding so I'm not too sure how to do this. Access or Excel wouldn't be a problem but Word is. The document has to be completed in word as its used for an online database.
    Does anyone have a template with a user form in that I can scrutinise to try and work out how to do it?

    I've attached a template that might give you a clue to what I'm after. The red text is populated by the drop menu in the user form that is filled with names. The blue text is what needs to be autofilled based on the information in red text.
    Anything with black text with ENTER before it will be populated by text boxes within the user form.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User form help! (2000)

    See attached zipped document. The userform will open automatically when you open the document.

    If you look at the code for the userform, you will see that I used an array to hold the names, titles and initials. The row source of the combo box is set to this array. Only the names are displayed in the combo box; the other columns are used in the code.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form help! (2000)

    Thanks Hans,
    Thats fantastic. I've had a look at how the user form works and I would have taken a long time to get there. Thankyou for your help.
    I presume to add text boxes that populate the relevant bookmarks I would need to do the following.
    Add a bookmark to the document. i.e. Address
    Add a text box to the user form titled Address
    In the user form coding on the Private Sub cmdOK_Click() command enter
    ActiveDocument.Bookmarks("Address").Range.InsertBe fore Address
    I'm guessing here...........I've been reading some posts on the google newsgroups...........

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User form help! (2000)

    That's correct, more or less. For each item, create a bookmark in the document in the appropriate location, and add a control to the userform; this might be a combo box (if you want the user to select from a list) or a text box (if the user should be able to enter the information.). In the On Click event procedure of the OK button, set the text of the bookmark. You can use the code you posted, or

    ActiveDocument.Bookmarks("Address").Range.Text = Me.txtAddress

    where Address is the name of the bookmark and txtAddress is the name of the control (a text box in this case.)

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form help! (2000)

    Sorry Hans, few more questions................
    Can I get a text box to behave in the manner of multiple lines, or will the user form have to use field like Address Line 1, Address Line 2, City, County, Country etc?
    Also what is the best way to protect the form from accidental fiddling.............?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User form help! (2000)

    You can set the MultiLine and the EnterKeyBehavior property of a text box to True; the user will be able to enter multiple line then. The code will faithfully transfer them.

    You can protect the document for forms. Since you won't be able to modify the document any more, you must unlock it in code, fill the bookmarks, then reprotect the document again:

    ActiveDocument.Unprotect Password:="TopSecret"
    ActiveDocument.Bookmarks("...") = ...
    ...
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="TopSecret"

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form help! (2000)

    Thanks Hans, as always I appreciate all your help

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form help! (2000)

    Hi Hans,
    Sorry to disturb you again. I've been entering the 30 odd names in the format that is in the coding and I am now getting an error on the opening of the user form.
    Runtime error '9'
    Subscript out of range

    This relates to this coding:

    Private Sub Document_Open()
    frmInput.Show
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User form help! (2000)

    The array used to hold the names etc. is declared as

    Private arrNames(0 To 3, 0 To 2) As String

    This means that the array holds 4 rows (numbered 0, 1, 2, 3) and 3 columns (numbered 0, 1 and 2, corresponding to name, title and initials). If you want to enter 37 persons, the array must have 37 rows (0, 1, ... , 36), so the declaration must change to

    Private arrNames(0 To 36, 0 To 2) As String

    In general, you must specify a number one less than the number of persons (since we start counting at 0).

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form help! (2000)

    Sorry I posted this on the wrong thread (i've deleted this entry!) ooops!

    I'm also unsure as to where to put the coding for a drop list. I've clicked on view code on the drop list and entered:
    Private Sub CbxArea_Click()
    With Me.CbxArea
    .AddItem "Eastern Team"
    .AddItem "Western Team"
    .AddItem "Trials Unit"
    End With
    End Sub
    But doesn't work.....................

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User form help! (2000)

    In general, you populate a list box or combo box in the Initialize event of the userform. Your code would keep on adding items each time you click on the combo box.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form help! (2000)

    Hans, attached is the final form. The userform seems to not start in a document template. Is there any reason for this?
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User form help! (2000)

    Yep, the userform is now opened in the Document_Open event. When you create a new document from a template, the Document_New event occurs instead of the Document_Open event. So you must change Document_Open to Document_New in the code.

    I also set the tab order for the controls on the userform, and added the unprotect/protect code. See attached version.
    Attached Files Attached Files

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User form help! (2000)

    Thanks for all your help again.
    How did you get the tabs to order? I've had a look in the coding and cannot discern anything relevant. It's more out of curiousity than anything....

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User form help! (2000)

    There are two ways to set the tab order. You must, of course, have opened the userform in design view in the Visual Basic Editor.

    - Select View | Tab Order...; select a control and use the Move Up and Move Down buttons to change its position in the tab order. Repeat until satisfied or exhausted.

    or

    - Set the TabIndex property for the relevant controls. It starts with 0. If you set the TabIndex for one control, the TabIndex for all controls following it will automatically be increased by 1.

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
  •