MS Office Forum / Word / Programming / May 2008
UserForm question
|
|
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.
|
|
|