Word Userform Bookmark/Cross Reference Formatting and Other Issues

[mrsjetset]

First off, I am completely new to VBA and am struggling through. I have a few questions and have tried a couple of other forums and just got tired of everyone treating me like I was stupid because I had basic questions. So, here I am.

I have created a simple multi-page userform to fill in the blanks on a basic will. (I didn't use a plain MS Word form as sometimes my client will need to make edits to the text of the will, so that wouldn't work as best I could tell.)

Here is where I'm at now. I have the form and quite a bit of coding complete, but have encountered a few issues:
1-Some of the bookmarks also have cross-references that refer back to them, and while I have finally grasped the concept of the subroutine and running it multiple times, my cross-references end up formatted like the original bookmark rather than like the text surrounding them. (I.E. my first bookmark is the person's name. At the original bookmark location, the font size is larger than the rest of the document, so in all the cross-references, the person's name is two sizes larger than all the surrounding text.) I feel like in my never ending research on VBA and userforms that I found a solution to this issue, but I can't seem to find it again.
2-In the code for my cmdOK_Click subroutine, my last line is Unload Me, but the form doesn't disappear. I can click OK and then the X, and it will close, but that's it. Do I need a separate Close button? I was under the impression the Unload Me should work, but it is totally possible I have misunderstood.
3-When I open a new document from the template, it opens the form on whichever page I edited last, rather than the first page. Is there a way to make page 1 the default? I didn't see anything in properties, but honestly a lot of the things there I have no idea what they do.

Here is my code for the userform:


Private Sub UserForm_Initialize()
With cboCountyReside
.AddItem "Barrow"
.AddItem "Bartow"
.AddItem "Carroll"
.AddItem "Cherokee"
.AddItem "Clayton"
.AddItem "Cobb"
.AddItem "Coweta"
.AddItem "Dekalb"
.AddItem "Douglas"
.AddItem "Fayette"
.AddItem "Forsyth"
.AddItem "Fulton"
.AddItem "Gwinnett"
.AddItem "Hall"
.AddItem "Henry"
.AddItem "Jackson"
.AddItem "Newton"
.AddItem "Paulding"
.AddItem "Pickens"
.AddItem "Rockdale"
.AddItem "Spalding"
.AddItem "Walton"
End With

With cboCountyMade
.AddItem "Barrow"
.AddItem "Bartow"
.AddItem "Carroll"
.AddItem "Cherokee"
.AddItem "Clayton"
.AddItem "Cobb"
.AddItem "Coweta"
.AddItem "Dekalb"
.AddItem "Douglas"
.AddItem "Fayette"
.AddItem "Forsyth"
.AddItem "Fulton"
.AddItem "Gwinnett"
.AddItem "Hall"
.AddItem "Henry"
.AddItem "Jackson"
.AddItem "Newton"
.AddItem "Paulding"
.AddItem "Pickens"
.AddItem "Rockdale"
.AddItem "Spalding"
.AddItem "Walton"
End With
End Sub

Private Sub cmdCancel_Click()
Unload Me
ActiveDocument.Close SaveChanges:=False
End Sub

Private Sub cmdClear_Click()
optMaleValue = False
optFemale.Value = False
optExecutor.Value = False
optExecutrix.Value = False
optExecutor2.Value = False
optExecutrix2.Value = False
txtTestatorName.Value = Null
txtDateMade.Value = Null
cboCountyReside.Value = Null
cboCountyMade.Value = Null
txtPrimeBeneficiary.Value = Null
txtPrimeBenAddress.Value = Null
txtPrimeBenPhone.Value = Null
txtSecondBeneficiary.Value = Null
txtSecondBenAddress.Value = Null
txtSecondBenPhone.Value = Null
txtPersonalRepName.Value = Null
txtPersonalRepAddress.Value = Null
txtPersonalRepPhone.Value = Null
txtSecondPersonalRep.Value = Null
txtSecondPersonalRepAddress.Value = Null
txtSecondPersonalRepPhone.Value = Null
txtAIFName.Value = Null
txtAIFAddress.Value = Null
txtAIFPhone.Value = Null
txtRealtyforPOA.Value = Null
End Sub

Private Sub cmdOK_Click()

Dim strTestatorTestatrix As String
If optMale = True Then strTestatorTestatrix = "Testator"
If optFemale = True Then strTestatorTestatrix = "Testatrix"

Dim strHimHer As String
If optMale = True Then strHimHer = "Him"
If optFemale = True Then strHimHer = "Her"

Dim strHisHer As String
If optMale = True Then strHisHer = "His"
If optFemale = True Then strHisHer = "Her"

Dim strPrimeExecutorExecutrix As String
If optExecutor = True Then strPrimeExecutorExecutrix = "Executor"
If optExecutrix = True Then strPrimeExecutorExecutrix = "Executrix"

Dim strSecondExecutorExecutrix As String
If optExecutor2 = True Then strSecondExecutorExecutrix = "Executor"
If optExecutrix2 = True Then strSecondExecutorExecutrix = "Executrix"

UpdateBookmark "TestatorName", txtTestatorName.Value
UpdateBookmark "DateMade", txtDateMade.Value
UpdateBookmark "CountyMade", cboCountyMade.Value
UpdateBookmark "CountyReside", cboCountyReside.Value
UpdateBookmark "TestatorTestatrix", strTestatorTestatrix
UpdateBookmark "HimHer", strHimHer
UpdateBookmark "HisHer", strHisHer
UpdateBookmark "PrimeBeneficiary", txtPrimeBeneficiary.Value
UpdateBookmark "PrimeBenAddress", txtPrimeBenAddress.Value
UpdateBookmark "PrimeBenPhone", txtPrimeBenPhone.Value
UpdateBookmark "SecondBeneficiary", txtSecondBeneficiary.Value
UpdateBookmark "SecondBenAddress", txtSecondBenAddress.Value
UpdateBookmark "SecondBenPhone", txtSecondBenPhone.Value
UpdateBookmark "PersonalRepTitle", strPrimeExecutorExecutrix
UpdateBookmark "PersonalRepName", txtPersonalRepName.Value
UpdateBookmark "PersonalRepAddress", txtPersonalRepAddress.Value
UpdateBookmark "PersonalRepPhone", txtPersonalRepPhone.Value
UpdateBookmark "SecondPersonalRep", txtSecondPersonalRep.Value
UpdateBookmark "SecondPersonalRepAddress", txtSecondPersonalRepAddress.Value
UpdateBookmark "SecondPersonalRepPhone", txtSecondPersonalRepPhone.Value
UpdateBookmark "SecondPersonalRepTitle", strSecondExecutorExecutrix
UpdateBookmark "AIFName", txtAIFName.Value
UpdateBookmark "AIFAddress", txtAIFAddress.Value
UpdateBookmark "AIFPhone", txtAIFPhone.Value
UpdateBookmark "RealtyforPOA", txtRealtyforPOA.Value
ActiveDocument.Fields.Update
Unload Me
End Sub

Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange

End Sub

[gmayor]

You are not doing so bad so far for an absolute beginner

First of all, a couple of minor issues with your form code.

Where you have two combo boxes with the same items, you can populate one from the other e.g.

cboCountyMade.List = cboCountyReside.List

which saves repeating a lot of entries, with the possibility of introducing errors.

It is also useful to include a prompt as the first item e.g.


With cboCountyReside
.AddItem "[Select County]" 'add a prompt
.AddItem "Barrow"
.AddItem "Bartow"
.AddItem "Carroll"
'etc
.ListIndex = 0 'select the prompt
End With

The code to clear the form appears to be superfluous. If you create a new document from your template, the form will already be cleared.

The code to write to the bookmarks could benefit from an error trap for a missing bookmark e.g.


Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
Dim BMRange As Range
On Error GoTo lbl_Exit 'trap missing bookmarks
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
lbl_Exit:
Set BMRange = Nothing
Exit Sub
End Sub

Your cancel button unloads the form before the save command.

1. Getting down to the issues you raised, the cross reference formatting can be fixed by adding a Charformat switch to the cross reference field (and remove any Mergeformat switch) - see http://www.gmayor.com/formatting_word_fields.htm. (http://www.gmayor.com/formatting_word_fields.htm)

Or you could provide additional bookmarks instead of cross references and use your function to write the same values to those bookmarks.

2. Frankly I don't like using Unload Me in the userform code. This is better placed in the calling macro. I prefer to Tag and Hide the userform, so your Cancel button code could be


Me.Hide
Me.Tag = 0


I would also put all the processing in the calling macro after the Submit button.

The basic code I would suggest for the userform is as follows though if you want to add error handling e.g. to ensure that all the key fields are filled that can go in the form also


Option Explicit

Private Sub cmdCancel_Click()
Me.Hide
Me.Tag = 0
End Sub

Private Sub cmdOK_Click()
Me.Hide
Me.Tag = 1
End Sub


The calling Macro in an ordinary module could therefore be something like


Option Explicit

Sub Example()
Dim strTestatorTestatrix As String
Dim strHimHer As String
Dim strHisHer As String
Dim strPrimeExecutorExecutrix As String
Dim strSecondExecutorExecutrix As String
Dim oFrm As New UserForm2 'the name of the userform
With oFrm
With .cboCountyReside
.AddItem "[Select County]"
.AddItem "Barrow"
.AddItem "Bartow"
.AddItem "Carroll"
.AddItem "Cherokee"
.AddItem "Clayton"
.AddItem "Cobb"
.AddItem "Coweta"
.AddItem "Dekalb"
.AddItem "Douglas"
.AddItem "Fayette"
.AddItem "Forsyth"
.AddItem "Fulton"
.AddItem "Gwinnett"
.AddItem "Hall"
.AddItem "Henry"
.AddItem "Jackson"
.AddItem "Newton"
.AddItem "Paulding"
.AddItem "Pickens"
.AddItem "Rockdale"
.AddItem "Spalding"
.AddItem "Walton"
.ListIndex = 0
End With
.cboCountyMade.List = .cboCountyReside.List
.cboCountyMade.ListIndex = 0
.Show
If .Tag = 0 Then 'Cancel button pressed
ActiveDocument.Close wdDoNotSaveChanges
GoTo lbl_Exit
End If

If .optMale.Value = True Then 'use the same condition for all three values
strTestatorTestatrix = "Testator"
strHimHer = "Him"
strHisHer = "His"
End If
'as .option buttons toggle you could use Else instead of separate conditional statements
If .optFemale.Value = True Then
strTestatorTestatrix = "Testatrix"
strHimHer = "Her"
strHisHer = "Her"
End If

If .optExecutor.Value = True Then strPrimeExecutorExecutrix = "Executor"
If .optExecutrix.Value = True Then strPrimeExecutorExecutrix = "Executrix"

If .optExecutor2.Value = True Then strSecondExecutorExecutrix = "Executor"
If .optExecutrix2.Value = True Then strSecondExecutorExecutrix = "Executrix"

UpdateBookmark "TestatorName", .txtTestatorName.Text
UpdateBookmark "DateMade", .txtDateMade.Text
UpdateBookmark "CountyMade", .cboCountyMade.Text
UpdateBookmark "CountyReside", cboCountyReside.Value
UpdateBookmark "TestatorTestatrix", strTestatorTestatrix
UpdateBookmark "HimHer", strHimHer
UpdateBookmark "HisHer", strHisHer
UpdateBookmark "PrimeBeneficiary", .txtPrimeBeneficiary.Text
UpdateBookmark "PrimeBenAddress", .txtPrimeBenAddress.Text
UpdateBookmark "PrimeBenPhone", .txtPrimeBenPhone.Text
UpdateBookmark "SecondBeneficiary", .txtSecondBeneficiary.Text
UpdateBookmark "SecondBenAddress", .txtSecondBenAddress.Text
UpdateBookmark "SecondBenPhone", .txtSecondBenPhone.Text
UpdateBookmark "PersonalRepTitle", strPrimeExecutorExecutrix
UpdateBookmark "PersonalRepName", .txtPersonalRepName.Text
UpdateBookmark "PersonalRepAddress", .txtPersonalRepAddress.Text
UpdateBookmark "PersonalRepPhone", .txtPersonalRepPhone.Text
UpdateBookmark "SecondPersonalRep", .txtSecondPersonalRep.Text
UpdateBookmark "SecondPersonalRepAddress", .txtSecondPersonalRepAddress.Text
UpdateBookmark "SecondPersonalRepPhone", .txtSecondPersonalRepPhone.Text
UpdateBookmark "SecondPersonalRepTitle", strSecondExecutorExecutrix
UpdateBookmark "AIFName", .txtAIFName.Text
UpdateBookmark "AIFAddress", .txtAIFAddress.Text
UpdateBookmark "AIFPhone", .txtAIFPhone.Text
UpdateBookmark "RealtyforPOA", .txtRealtyforPOA.Text
ActiveDocument.Fields.Update
ActiveDocument.Save
End With
lbl_Exit:
Unload oFrm
Set oFrm = Nothing
Exit Sub
End Sub

Private Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
Dim BMRange As Range
On Error GoTo lbl_Exit 'trap missing bookmarks
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
lbl_Exit:
Set BMRange = Nothing
Exit Sub
End Sub


3. Which brings us to your final issue - the opening of the document at the top. If you put the cursor at the top of the template, before you save it, the new documents created from it will open at the top. If you want to re-inforce that to cover reasons you have not explained then use an AutoNew macro to put the cursor at the top

Sub AutoNew()
Selection.HomeKey Unit:=wdStory 'Puts the cursor at the start
lbl_Exit:
Exit Sub
End Sub

The code was modified without access to your form so I apologize for any typos, but Option Explicit at the tops of the modules should throw them up if you run Debug > Compile Normal, from the VBA editor toolbar.

[gmaxey]

Obviously a Georgian. Absolutely nothing amiss that I can see in Graham's suggestions. We will each develop our own styles. Mine often drives Graham bonkers and his can sometimes make my eyes bleed ;-)

I certainly tend to do more in the form than he typically does so if I where writing your Initialize and Clear procedures, they would look something like this:


|Barrow|Bartow|Carroll|Cherokee|Clayton|Cobb|Coweta|Dekalb|Douglas|Fayette| Forsyth|Fulton|Gwinnett" _
& "|Hall|Henry|Jackson|Newton|Paulding|Pickens|Rockdale|Spalding|Walton", "|")

cboCountyReside.List = arrCounties
cboCountyReside.ListIndex = 0
cboCountyMade.List = cboCountyReside.List
cboCountyMade.ListIndex = 0
lbl_Exit:
Exit Sub
End Sub

Private Sub cmdClear_Click()
Dim oCtrl As Control
For Each oCtrl In Controls
Select Case TypeName(oCtrl)
Case "OptionButton": oCtrl.Value = False
Case "ComboBox": oCtrl.ListIndex = 0
Case "TextBox": oCtrl.Text = vbNullString
End Select
Next
lbl_Exit:
Exit Sub
End Sub]

I don't see anything in the code you provided that would keep the form up when you click cmd_Cancel. I would have to see your document.

[mrsjetset]

Thank you for the assistance, and apologies it has taken so long for the reply. This has finally come back around after some other things took priority. I was going to post my file, but it seems .DOTM isn't an accepted format. If there is another way to post that I'm missing, please let know.

One thing I don't understand is your comment "I would also put all the processing in the calling macro after the Submit button." My interpretation is that you mean instead of putting it in the userform code, to put it all the module object where the Load/Show operation takes place? I apologize that I am not seeing what you mean.

I did not realize you could autopopulate one list from another, very helpful!, nor the way to shorten the condition/value statements. Will certainly help keep the code cleaner and shorter.

On Greg's response, the form does disappear when I click Cancel, but not when I click OK. It populates the document just fine on the cmdOK_Click(), but doesn't close the form.

I am trying to digest the formatting link you sent also.

Again, I really appreciate both of your assistance!

[gmayor]

Thank you for the assistance, and apologies it has taken so long for the reply. This has finally come back around after some other things took priority. I was going to post my file, but it seems .DOTM isn't an accepted format. If there is another way to post that I'm missing, please let know.You can make the template available from a web resource such as OneDrive or DropBox and post a link.


One thing I don't understand is your comment "I would also put all the processing in the calling macro after the Submit button." My interpretation is that you mean instead of putting it in the userform code, to put it all the module object where the Load/Show operation takes place? That's exactly what I meant. The userform code is simply
Option Explicit

Private Sub cmdCancel_Click()
Me.Hide
Me.Tag = 0
End Sub

Private Sub cmdOK_Click()
Me.Hide
Me.Tag = 1
End Sub


Greg's method of populating the list from an array is more elegant, and can be done outside the form. I used the same method as you to make things clearer ... and to save a lot of re-typing :)

[mrsjetset]

Oh it seems I'm cursed. When I put it on my OneDrive and tried to post the link, I got a "Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words."

I will plug along as best I can within this input and see where I end up.

Thank you again!

[mrsjetset]

I got through the array portion and that makes total sense - that is certainly easier than my original code.

But I have question about the cmdClear_Click() procedure. I copied it into my code, but honestly I'd like to know how it works. I know the
Dim oCtrl As Control is declaring the variable oCtrl as a control, but the next two lines I don't quite get. The
Case "OptionButton": oCtrl.Value = False
Case "ComboBox": oCtrl.ListIndex = 0
Case "TextBox": oCtrl.Text = vbNullString I mostly understand, except the Case at the beginning, which I know has something to do with the code selecting each different piece but I can't quite wrap my head around it. If you have a moment and could explain what those couple of lines mean, I would greatly appreciate it, so that I can use this method in the future (SO MUCH FASTER than clearing each box/option/etc. individually!).





Private Sub UserForm_Initialize()
Dim arrCounties() As String
arrCounties = Split("[Select]|Barrow|Bartow|Carroll|Cherokee|Clayton|Cobb|Coweta|Dekalb|Douglas|Fayette| Forsyth|Fulton|Gwinnett" _
& "|Hall|Henry|Jackson|Newton|Paulding|Pickens|Rockdale|Spalding|Walton", "|")

cboCountyReside.List = arrCounties
cboCountyReside.ListIndex = 0
cboCountyMade.List = cboCountyReside.List
cboCountyMade.ListIndex = 0
lbl_Exit:
Exit Sub
End Sub

Private Sub cmdClear_Click()
Dim oCtrl As Control
For Each oCtrl In Controls
Select Case TypeName(oCtrl)
Case "OptionButton": oCtrl.Value = False
Case "ComboBox": oCtrl.ListIndex = 0
Case "TextBox": oCtrl.Text = vbNullString
End Select
Next
lbl_Exit:
Exit Sub
End Sub

I don't see anything in the code you provided that would keep the form up when you click cmd_Cancel. I would have to see your document.

[mrsjetset]

I have made most of the suggested changes, other than relocating the code, and that one I did try but it didn't work for me. After re-reading I missed a couple of things, but in the mean time I did make some other edits suggested - the array to load the combo boxes, added in the error catch for the bookmarks, added the \*CHARFORMAT switch to the necessary cross-reference fields, and reset the opt button code so I just check the value once and assign all the necessary variables at that time. But apparently I have broken something else in the process: I get a "Run-time error '13': Type Mismatch. When I go to debug, it takes me to the module code that loads the form and highlights the Load frmSimpleWill line. I deleted that line just to see what would happen, and it just gave the same error again for the next line, that says frmSimpleWill.Show.

I have tried several times to post a link to the file on my OneDrive, but I get the error "Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words."

Thank you again, your taking the time out of your day to help is greatly appreciated!

[mrsjetset]

One more update. I seem to have everything working except the form refuses to close when I click OK. I have tried Unload Me, Unload frmSimpleWill and Exit Sub. All of these give the same result - when I click OK, the bookmarks and cross references are correctly populated, but the userform just clears itself and remains on the screen. I cannot figure out what on earth I am doing wrong. Everything I've read says the Unload Me or the Unload frmSimple Will should work fine. I have tried to post a link to my file multiple times, but I am too new of a user for it to allow me to post a link.

[gmaxey]

Dim oCtrl as Control, Is declaring a variable as Type Control.
The userform is made up of controls so:
For Each oCtrl in Contols, loops through each control in the form. (Graham might use Me.Controls, but I don't like Me)
Each form control is of a certain type. "TextBox" OptionButton" etc. so

Select Case TypeName(oCtrl) is going to return the type of control
Case "OptionButton" means the type returned was an option button. so,
set its value to False
and so on

Think:

Dim strName as String

strName = "mrsjetset"

Select Case strName
Case "Greg", "Graham": Msgbox "Master teach"
Case "mrsjetset": Msgbox "Grasshopper learn."
End Select

[gmaxey]

Send me website feedback. I will reply and you can attach your file to my reply.

[mrsjetset]

Thanks for the assistance. I got everything working yesterday, although it is probably a hack job. I figured out the reason it wouldn't close on cmdOK_Click() was because I also had it show the form in the This Document code module. I thought I had deleted that a while ago but apparently it was still lingering. I would still like you to take a look at the file though, just to get some feedback on where I could improve. I did end up going back to an earlier version while I was trying to figure out what had broken, so it incorporates some of the above suggestions and not others.