UserForm question

[Jeri]

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]

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.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Gordon Bentley-Mix]

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.
--
Cheers!
Gordon

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

 

[Gregory K. Maxey]

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,

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.
--
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:

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.

 

[Gordon Bentley-Mix]

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.

--
Cheers!
Gordon

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

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

[Lines deleted to for readability]

 

[Gregory K. Maxey]

Gordon,

Sound advice all. Thanks.

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.

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

[Lines deleted to for readability]

 

[Gordon Bentley-Mix]

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...)
--
Cheers!
Gordon

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