Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / May 2008

Tip: Looking for answers? Try searching our database.

UserForm question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jeri - 13 May 2008 19:55 GMT
I have created several bookmarks in my document.  I then created a UserForm
(UserForm1) and added code to the command button on the form such as:

Private Sub CommandButton1_Click()
With ActiveDocument
   .Bookmarks ("RecipientName").Range_
   .InsertBefore TextBox1
   .Bookmarks("RecipientReturnAddress").Range_
   .InsertBefore TextBox2
   .Bookmarks("RecipientCSZ").Range_
   .InsertBefore TextBox3
   .Bookmarks("ReferenceNo").Range_
   .InsertBefore TextBox4
   .Bookmarks("Salutation").Range_
   .InsertBefore TextBox5
   .Bookmarks("Scope").Range_
   .InsertBefore TextBox6
   .Bookmarks("PrimaryAttorney").Range_
   .InsertBefore TextBox7
   .Bookmarks("PrimaryAttorneyRate").Range_
   .InsertBefore TextBox8
   .Bookmarks("SecondaryAttorney").Range_
   .InsertBefore TextBox9
   .Bookmarks("SecondaryAttorneyRate").Range_
   .InsertBefore TextBox10
   .Bookmarks("MoniesRecoveredBy").Range_
   .InsertBefore TextBox11
   .Bookmarks("AmountsRecoveredFrom").Range_
   .InsertBefore TextBox12
   .Bookmarks("Retainer").Range_
   .InsertBefore TextBox13
   .Bookmarks("SigningAttorney").Range_
   .InsertBefore TextBox14
   .Bookmarks("DayofMonth").Range_
   .InsertBefore TextBox15
   .Bookmarks("Month").Range_
   .InsertBefore TextBox16
   .Bookmarks("TwoDigitsYear").Range_
   .InsertBefore TextBox17
End With

UserForm1.Hide
End Sub

Bookmark names are enclosed in " " after .Bookmarks
and text box names are referenced after .InsertBefore

I created a macro that will show the userform.
WHen the userform is filled in and the command button is clicked, I get an
error message:

Compile error:
Method or data member not found

The vba window opens and .Range_ is highlighted

Any ideas what is going wrong?

Also, the macro that shows the userform - what do I need to call it to get
it to run automatically when the document is opened?  

The file is saved as a .doc (Word 2003 Pro) - can't do a .dot in this
particular situation so it needs to be a doc, not a template.

THanks so much, in advance, for any assistance.
Jay Freedman - 13 May 2008 20:38 GMT
The underscores at the ends of lines are meant as continuation characters,
that is, "this line and the one that follow are parts of the same
statement". To make that work, however, you must have a space character
between the underscore and the letter to its left, like

   .Bookmarks ("RecipientName").Range _

(see http://www.word.mvps.org/FAQs/MacrosVBA/_AtEndOfLine.htm). Without that
space, VBA assumes the underscore is part of the word Range, but there is no
such thing as a Range_ so you get a compile error.

Signature

Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

> I have created several bookmarks in my document.  I then created a
> UserForm (UserForm1) and added code to the command button on the form
[quoted text clipped - 62 lines]
>
> THanks so much, in advance, for any assistance.
Gordon Bentley-Mix - 14 May 2008 22:57 GMT
Jeri,

Jay has answered your first question; I'll answer your second.

The solution is pretty simple; just create an AutoOpen macro similar to the
following:

Sub AutoOpen
    Load UserForm1
    UserForm1.Show
End Sub

This should achieve the desired result, although there may be a lot more
that you might choose to do - like determine some conditions under which the
UserForm should or should not be displayed.

And just a couple of notes on your code:

Generally, it's best to give things like UserForms, CommandButtons,
TextBoxes, etc. descriptive names. This avoids confusion about exactly what
the thing is, which can be a real problem in a large project. (Probably not
so important in your little project here.)

It's also better to create specific instances of objects (like Documents,
UserForms, Ranges) and refer to them explicitly. Using the generic, default
or class name (e.g. ActiveDocument, UserForm1, [Object].Range) can cause
problems if there is more than one instance of a particular object loaded at
any one time. (Again, probably not something to worry about in this case.)

Similarly, with properties of an object it's best not to rely on the default
as you've done with your TextBoxes. You should identify which property you
want to use explicitly. (The default property of a TextBox is .Value so it
works OK in this instance, but it's a bit sloppy and could cause problems
later.)

Since you're doing the same thing several times over, you might want to
consider creating a procedure that uses arguments and calling this procedure
each time rather than writing the same code repeatedly. For example, a
procedure for inserting the value from a TextBox into a Bookmark might look
like this:

Private Sub InsertTextBoxValue(TextBoxValue as String, BookmarkName as String)
Dim myRange as Range
    Set myRange = ActiveDocument.Bookmarks(BookmarkName).Range
    myRange.InsertBefore TextBoxValue
End Sub

Then in your code for the Click event of CommandButton1 you would simply use:

Private Sub CommandButton1_Click()
    InsertTextBoxValue "RecipientName", TextBox1.Value
    InsertTextBoxValue "RecipientReturnAddress", TextBox2.Value
    InsertTextBoxValue "RecipientCSZ", TextBox3.Value
    [And so on for the remaining Bookmarks and TextBoxes]
End Sub

This code is much simpler to use because it requires much less typing and
doesn't make you think about which objects/properties/methods you want to use
every time, and Intellisense will prompt you for the Bookmarks and TextBoxes
so you won't accidentally leave something out. (Note that the
'InsertTextBoxValue' code also uses a specific instance of a Range object in
the way I mentioned above.)

Finally, just to keep things neat, you should probably unload your UserForm
at the end of everything. Just put 'Unload UserForm1' before the 'End Sub'.
This will release the memory used by the UserForm and also ensure that any
values entered into the UserForm aren't accidentally retained in memory.
Signature

Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

> I have created several bookmarks in my document.  I then created a UserForm
> (UserForm1) and added code to the command button on the form such as:
[quoted text clipped - 61 lines]
>
> THanks so much, in advance, for any assistance.
Gregory K. Maxey - 15 May 2008 12:58 GMT
Gordon,

Passing the variables in this order "RecipientName", TextBox1.Value to the
procedure Private Sub InsertTextBoxValue(TextBoxValue as String,
BookmarkName as String)
results in errors.

I would also suggest taking the extra step to redefine the bookmark range to
include the desired text.

Private Sub CommandButton1_Click()
    InsertTextBoxValue "RecipientName", TextBox1.Value
    InsertTextBoxValue "RecipientReturnAddress", TextBox2.Value
'     InsertTextBoxValue "RecipientCSZ", TextBox3.Value
'     [And so on for the remaining Bookmarks and TextBoxes]
End Sub

Private Sub InsertTextBoxValue(BookmarkName As String, TextBoxValue As
String)
Dim myRange As Range
Set myRange = ActiveDocument.Bookmarks(BookmarkName).Range
myRange.Text = TextBoxValue
ActiveDocument.Bookmarks.Add BookmarkName, myRange
End Sub

> Jeri,
>
[quoted text clipped - 148 lines]
>>
>> THanks so much, in advance, for any assistance.
Gordon Bentley-Mix - 15 May 2008 23:24 GMT
Greg,

Thanks for catching my mistake. Once again I was in a rush to get the post
out and didn't look closely enough at what I was writing and reversed the
order of the arguments. My bad. As the LTSA is always telling us, "The faster
you go, the bigger the mess." :-D

As for the process used for actualling inserting the TextBox value, I would
agree completely. In fact, this is what I usually do - use the TextBox value
in the .Text property of the Range and then wrap the bookmark around the
Range again. However, Jeri used the .InsertBefore method in the original
post, so I decided to just stick with that.

I would also avoid using the ActiveDocument object and instead reference a
specific Document object, similar to the following:

Private Sub InsertTextBoxValue(BookmarkName As String, TextBoxValue As  
String)
Dim myDoc as Document
Dim myRange As Range
    Set myDoc = ActiveDocument
    Set myRange = myDoc.Bookmarks(BookmarkName).Range
    myRange.Text = TextBoxValue
    myDoc.Bookmarks.Add BookmarkName, myRange
End Sub

However, I would probably declare the Document object in the general
declarations and set it to the ActiveDocument well before I ever got to my
InsertTextBoxValue procedure - most likely in the AutoNew procedure. I would
also do the same with a UserForm object for UserForm1 and write some code to
collect the various TextBox values without relying on the existence of the
object itself.

I left this out of my original post to save Jeri from being overwhelmed, but
since we've come this far...

Assuming the following:
- A main module called Module1
- A UserForm called UserForm1
- Four TextBoxes on the UserForm for collecting information
- Two CommandButtons on the UserForm; one for creating the document and one
to cancel it.
- Four bookmarks in the document named in accordance with Jeri's original post

In Module1 I would place the following code:

Option Explicit

Public bNewDoc As Boolean
Dim myDoc As Document
Dim myForm As UserForm1
Dim TextBox1Value As String
Dim TextBox2Value As String
Dim TextBox3Value As String
Dim TextBox4Value As String

Sub AutoNew()
   Set myDoc = ActiveDocument
   Set myForm = New UserForm1
   Load myForm
   myForm.Show
   If bNewDoc = True Then CollectValues
   Unload myForm
   Set myForm = Nothing
   If bNewDoc Then InsertValues Else myDoc.Close wdDoNotSaveChanges
End Sub

Private Sub CollectValues()
   With myForm
       TextBox1Value = .TextBox1.Value
       TextBox2Value = .TextBox2.Value
       TextBox3Value = .TextBox3.Value
       TextBox4Value = .TextBox4.Value
   End With
End Sub

Private Sub InsertValues()
   InsertTextBoxValue "RecipientName", TextBox1Value
   InsertTextBoxValue "RecipientReturnAddress", TextBox2Value
   InsertTextBoxValue "RecipientCSZ", TextBox3Value
   InsertTextBoxValue "ReferenceNo", TextBox4Value
End Sub

Private Sub InsertTextBoxValue(BookmarkName As String, TextBoxValue As String)
   With myDoc
       If .Bookmarks.Exists(BookmarkName) Then
       Dim myRange As Range
           Set myRange = .Bookmarks(BookmarkName).Range
           myRange.Text = TextBoxValue
           .Bookmarks.Add BookmarkName, myRange
       Else: MsgBox "Bookmark " & BookmarkName & "not found."
       End If
    End With
End Sub

And in UserForm1:

Option Explicit

Private Sub UserForm_Initialize()
   bNewDoc = False
End Sub

Private Sub CommandButton1_Click()
   bNewDoc = True
   Me.Hide
End Sub

Private Sub CommandButton2_Click()
   bNewDoc = False
   Me.Hide
End Sub

Even this is stripped down a bit. For instance, there is nothing in the
UserForm1 code to ensure that all values have been entered or to format the
values if necessary, etc., and the error handling is minimal elsewhere as
well. However, it's more than sufficient for the purpose of answering Jeri's
question. (And I even tested it to make sure it works! ;-P)

Jeri, if you would like me to explain what I've done in more detail, feel
free to contact me at the email address in my profile. I'll send you a
commented version of my test template for you to pick apart.

Signature

Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

> Gordon,
>
[quoted text clipped - 20 lines]
> ActiveDocument.Bookmarks.Add BookmarkName, myRange
> End Sub

[Lines deleted to for readability]
Gregory K. Maxey - 16 May 2008 00:55 GMT
Gordon,

Sound advice all.  Thanks.

> Greg,
>
[quoted text clipped - 147 lines]
>>
> [Lines deleted to for readability]
Gordon Bentley-Mix - 16 May 2008 01:11 GMT
Jeri,

I forgot that you said you needed this to work on the Open event rather than
the New event. Simple enough to fix: Just change the AutoNew procedure to
AutoOpen and (optionally) remove the code to close the document without
saving the changes if CommandButton2 is clicked - something like this:

Sub AutoOpen()
    Set myDoc = ActiveDocument
    Set myForm = New UserForm1
    Load myForm
    myForm.Show
    If bNewDoc = True Then CollectValues
    Unload myForm
    Set myForm = Nothing
    If bNewDoc Then InsertValues Else myDoc.Close
End Sub

Just using myDoc.Close will prompt to save the changes if there are any.
Otherwise, you can just remove that line completely and rely on the user to
decide if the document should be saved (which is bloody risky IMHO because
they might delete a bookmark or something that could really stuff the doc...)
Signature

Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.


Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.