Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA User Form (Word 2000)

    I have finally created a User Form (with lots of help from the different newsgroups/forums/tips, etc.) and it works great EXCEPT for one thing. The end user can tab through the form entering information as they go but when the form should tab to the Combo Box set up for the signature it skips down to the Control Button instead. It is possible to tab from there to the choices for Closing (Very truly yours or Sincerely) and then it will tab to the Combo Box but I can't understand why it won't tab in order. The form starts with Name, Company Name, Address, City State Zip, Subject, Salutation, Closing, THEN Signature Combo Box, and finally the OK control button. What am I doing wrong or what step am I missing here. I have checked the books I have, the VBA help in the program, etc. and am at a loss. Any help would be greatly appreciated. I am also trying to figure out how to delete a blank line from the same form if there is no "Company Name" - not having any luck there either.

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA User Form (Word 2000)

    The default tab order is the order in which you created the controls, not their physical order on the Userform.
    You need to change the TabIndex property of the controls to get your desired order.
    Check TabIndex in the Object Browser.

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA User Form (Word 2000)

    Howard, that worked perfectly! Thank you so much. Any chance you might have an answer to my other question re deleting the blank line if there is no company name entered in the user form?

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA User Form (Word 2000)

    Reading a little bit into your post, you are inserting the information into a Word document. Are you identifying the relevant lines with specially named styles, or bookmarks, or... how would your program know it was on the line where Company goes?

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA User Form (Word 2000)

    The relevant lines are defined as bookmarks in the Word document and the language in the code for the User Form is as follows: ActiveDocument.Bookmarks("CompanyName").Range.Text = tbCompanyName.Text. I thought there should be some way to do an "If" statement but no matter how I tried to word it to delete the line if there was no "CompanyName" I got an error. I guess I shouldn't worry about it too much since the end user can just delete the line later and the letterhead template that had been set up in Word Perfect by someone else had the same problem but it bothers me just the same.

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA User Form (Word 2000)

    Use this code to remove the paragraph symbol and company name bookmark:
    <pre>With ActiveDocument.Bookmarks("myBmk")
    ' Delete everything after the bmk through and including the para symbol
    .Range.Paragraphs(1).Range.Delete
    ' Delete the bmk itself
    .Delete
    End With</pre>

    Not sure about the If test... you are testing whether the textbox is blank?

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: VBA User Form (Word 2000)

    <P ID="edit" class=small>(Edited by macropod on 23-Sep-02 15:30. minor clarification)</P>Hi Chris,

    Maybe you'll find the following technique using a field-code solution useful. It uses bookmarks for Name, CompanyName, Address, City, State & Zip. These can be set internally (as I've done with 'SET' statements) or called from elsewhere in your document:

    {QUOTE {SET Name "Name"}{SET CompanyName "Company Name"}{SET Address "123 zzz"}{SET City "City"}{SET State "ABC"}{SET Zip "1234"}{Name}11{CompanyName}{if{CompanyName}= "" "" {QUOTE 11}}{Address}11{City}160{State}160{Zip}}

    This will return:
    Name
    Company Name
    123 zzz
    City ABC 1234
    if the CompanyName bookmark has data ("Company Name" in the example), and
    Name
    123 zzz
    City ABC 1234
    otherwise.

    For what it's worth, the 11s in the code are linefeed codes and the 160s are hard spaces. As you'll see, there's an 'if' formula in the last line. This formula tests whether there is a company name and, if so, inserts a line feed after it.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA User Form (Word 2000)

    Yes, I want to test if the textbox is blank. What I want the user to end up with is:

    Addressee's Name
    Company Name
    Address
    City State Zip
    Subject
    Salutation

    If there is no Company Name I want the blank paragraph that is created to be deleted. I think I understand the code you used but I'm not sure where it should be inserted or whether an "If" statement would be before your code? I am really frustrated because I should be able to figure this out and I can't. I just can't seem to get the wording right.

  9. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: VBA User Form (Word 2000)

    Chris,

    Based on your previous post, and Jefferson's code, something like:

    <pre>If tbCompanyName.Text = "" Then
    ActiveDocument.Bookmarks("CompanyName").Range.Para graphs(1).Range.Delete
    End If</pre>

    should work (the second ".Delete" in Jefferson's code example probably isn't needed).

    Gary

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA User Form (Word 2000)

    > the second ".Delete" in Jefferson's code example probably isn't needed

    It's only needed if you want to get rid of the bookmark itself. At least in my test it was needed.

  11. #11
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: VBA User Form (Word 2000)

    It might have to do with vagaries such as what the bookmark is enclosing. In the test I tried, the bookmark was enclosing a word in the middle of the paragraph. The first line of code deleted the paragraph as well as the bookmark that was in the midst of it; in that situation, the second .Delete will cause a runtime error.

  12. #12
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA User Form (Word 2000)

    Gary, I have been out of the office for some advanced Word training the last couple of days so I haven't had a chance to respond to your reply. I put in the statement as you show in your reply but when I run the form I get a run-time error stating that "The requested member of the collection does not exist". I have checked and rechecked to see if I misspelled or entered the information wrong but I can't find any error. What am I missing?

  13. #13
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: VBA User Form (Word 2000)

    Chris,

    That usually indicates that the specific bookmark that is being referenced, is missing at the time that the line of code executes.
    Just as a test, try inserting this code just before the line of code I previously posted:

    If ActiveDocument.Bookmarks.Exists("CompanyName") = True Then
    MsgBox "The bookmark exists"
    Else
    MsgBox "The bookmark is missing!"
    End If

    If it does indicate that the bookmark is there, could you attach a Word file to a reply here with just the relevant text and bookmark included, so we can play with it some more?

    Gary

  14. #14
    New Lounger
    Join Date
    May 2002
    Location
    California
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA User Form (Word 2000)

    Gary, I finally figured out what the problem was - when I created the bookmarks in the main Word document I was following the tips from the MVP site on how to create a Userform. In the example it was suggested that you select a single space and then create the bookmarks (so that the text is inserted inside the bookmark, rather than after it. I am assuming that because there was nothing in the "CompanyName" when the template was run that is why I kept getting the error message. I went in, deleted the bookmark then re-created it without the enclosing space and everything worked exactly the way it was supposed to. When I put a Company Name in it's inserted and when there is no Company Name it deletes the blank paragraph. Thank you so much for your help. Sometimes it seems like it's the most simple things that create problems. BTW, because I was having so many problems I did find an alternate way of getting the information in - I changed the text box in the Userform so that you could enter more than one line of text - if you had a company name you would just press Shift-Enter first and then type your information; if there was no company name you ended up with just one line. So I guess there's more than one way to get things accomplished. Thanks again, Chris

Posting Permissions

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