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

    Word VB or Macro???? Help! (2K)

    Hi,
    I'm okay with using word but I'd like to know if there is anyway to use VB or a macro on a protected form?
    What I want to be able to do is have a standard drop list with names in. I'd then like another box to be filled with the initials of that person dependant on the name selected in the drop menu (This would be for a reference number). I know how to do it in excel and how to do it in Access but I've never really fiddled enough with word to know if this is possible. Is it?

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

    Re: Word VB or Macro???? Help! (2K)

    Yes, you can. For each control from the Forms toolbar (text box, dropdown list, check box), you can specify a macro to be run when the user enters the control, and a macro to be run when the user exits the control.Double click the dropdown (in unprotected mode) to set the macros (see screenshot.)

    For a very simple example, see <post#=379047>post 379047</post#>. It doesn't do what you want, but it will give you an idea how an On Exit macro works.
    Attached Images Attached Images
    • File Type: png x.PNG (11.5 KB, 0 views)

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

    Re: Word VB or Macro???? Help! (2K)

    Hi Hans,
    You seem to be a Guru at everything!!! Ho hum...........lol.
    The Macro actually makes sense for a change!
    I know that drop down list has a limitation of 25 names. I've just totalled up all the names I need and the come to 30. Is there any way around this limitation?

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

    Re: Word VB or Macro???? Help! (2K)

    The limit for the dropdown list from the Forms toolbar is "hard".

    One workaround would be to use two dropdown lists: the user selects a category from the first one (for example "Names A-M" or "Names N-Z"). The Exit macro for the first dropdown fills the second dropdown with the appropriate list of names.

    Or you can use a combo box from the Control Toolbox. This can display thousands of items.

    Or you can create a UserForm in the Visual Basic Editor (Insert | UserForm) with combo boxes, text boxes etc. and an OK command button. The On Click code for the command button would enter the information in the document.

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

    Re: Word VB or Macro???? Help! (2K)

    This is going to sound really daft, but how do I get text into the combobox?

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

    Re: Word VB or Macro???? Help! (2K)

    You need VBA code to populate a combo box. Which route have you chosen? A combo box in the document, or a userform?

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

    Re: Word VB or Macro???? Help! (2K)

    I chose a combobox in the document. I did have a look at the user form and did debate whether to use it but I'd be asking far more questions than I am now.

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

    Re: Word VB or Macro???? Help! (2K)

    You will probably have noticed that the Control Toolbox has a "Design Mode" button (the first one). If it's ON (down), you can select the combo box to modify its design; if the button is OFF (up), you can click the dropdown arrow of the combo box.

    Make sure that design mode is ON.
    Select the combo box.
    Switch to the Visual Basic Editor (Alt+F11)
    Note the name of the combo box in the Properties pane; probably ComboBox1.
    Double click the ThisWorkbook object in the Project Explorer (the treeview).
    Create code like this:

    Private Sub Document_Open()
    With Me.ComboBox1
    .AddItem "Mary"
    .AddItem "Had"
    .AddItem "A"
    .AddItem "Little"
    .AddItem "Lamb"
    End With
    End Sub

    Press F5 to run this code now (later, it will be run automatically when the document is opened). You can test the combo box now; running the code will have switched off design mode.

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

    Re: Word VB or Macro???? Help! (2K)

    Sorry Hans,
    This works fine. I have just been informed that each name has to have a title. Will I now have to do a macro that looks up the persons name and enters in the row below what that persons title is. i.e.

    Mrs Mary Contrary
    Senior Lawyer

    Obviously i could enter this into the .AddItem "Mrs Mary Contrary Senior Lawyer" but this would appear all on one line. Which isn't the correct format............

    Is there another way of doing it?

    I think I'm going to resign and go and do some good honest farming. At least people won't keep asking me to do things.........ho hum.

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

    Re: Word VB or Macro???? Help! (2K)

    If it is going to go on like this, a userform will be more efficient. Or do the whole thing in Access, and create the Word doc from there.

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

    Re: Word VB or Macro???? Help! (2K)

    (Edited by HansV to provide link to thread - see <!help=19>Help 19<!/help>)

    Hi Hans,
    Sorry to be a pain. If a user form would be more efficient than that would be the way to go.
    Unfortunately the document has to be in the form of Word Template because it will need to go onto a web based data system that has word documents within it that allow the user to produce letters in relation to the data.
    If you have time I would appreciate a little help.
    I've had a look at <post#=340997 >post 340997 </post#> at there seems to be several options of creating userforms. As I understand it (I've not done one before) the user form will have boxes that data can be entered into and then this can be bookmarked to the actual document. This sounds fine. I presume the process is that the user will enter the document and enter directly onto the user form, from there the user will fill out the data and it will then be bookmarked to the actual document.

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

    Re: Word VB or Macro???? Help! (2K)

    Bookmarks are just one out of several possibilities, but you've got the general idea:
    - Userform is opened automatically when user starts new document
    - User fills in information in text boxes, combo boxes, check boxes etc.
    - When the user clicks OK, VBA code checks the information, then puts it into the document.

Posting Permissions

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