Solved: Userform - change bookmarks, keep formatting

[Saabeirik]

Hi! I just started with VBA today, but it seems I'm getting were I want soon.

I'm making a Userform template for a text with a lot of repeating text. The text contains bookmarks with crossreferences to them. When opening the document, you're requested to fill in data in the userform.

After filling in the userform, the commandbutton clear all bookmarks and assigns the new values to the bookmarks.

Then I have another macro set up for updating all fields (header/footer and text), before closing the userform.


This seems to be working fine except for one thing. Some of the crossreferenced bookmarks have different formatting in different parts of the document. And when I make a bookmark of 3 words instead of 2, the third word turns out formatted like the original bookmark, not like the rest of the crossreference.

Does anyone know what is wrong, and maybe how I could fix this? I want to keep the formatting the crossreference originally were given ! :-)

Thanks in advance for any tips!

[gmaxey]

Have you tried adding the \*CharFormat swith to your cross reference fields?

[macropod]

IIRC, the mergeformat switch causes the problem. deleting or changing mergeformat to charformat resolves the issue. The latter prevents its recurrence.

[Saabeirik]

Thank you both, I added charformat to my fieldcodes, and this solved my problem! :)

I've got a new related question though:

I want to be able to use my form not only at startup (autonew) but also assigned a keyboard shortcut to it, so that I can recall the userform and edit/update information. Some of the text fields will be left empty from time to time.

I just realized that if I leave all the userform textboxes empty, the letter after any bookmark will be deleted. Can you help me figure out why?

This is the code I'm using, and I'm thinking this must be the problem.
Private Sub Autofyll_Click()

With ActiveDocument

Selection.GoTo What:=wdGoToBookmark, Name:="Firmanavn"
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.InsertAfter Firmanavn
ActiveDocument.Bookmarks.Add Range:=Selection.Range, _
Name:="Firmanavn"


End With

inndata_ks.Hide

update_fields

End Sub

[macropod]

Your 'Autofyll_Click' code explicitly deletes a character. I have no idea why you would do that. If you're trying to update bookmarked content, you should use code like:
Sub UpdateBookmark (BmkNm as string, NewTxt as string)
Dim BmkRng as Range
With ActiveDocument
If.Bookmarks.Exists(BmkNm) Then
Set BmkRng =.Bookmarks(BmkNm).Range
BmkRng.Text = NewTxt
.Bookmarks.Add BmkNm, BmkRng
End if
End With
Set BmkRng = Nothing
End Sub
which you would call so:
Private Sub Autofyll_Click()
With ActiveDocument
Call UpdateBookmark("Firmanavn", Firmanavn)
.Fields.Update
End With
inndata_ks.Hide
End Sub

[Saabeirik]

Thank you so much! :) This worked perfectly, and seems like a much more "clean" code. The reason I had used that code is that I'm not able to make the code myself, so I used google and tried to put together something using what I came across :)

I also needed fields in footer and header updated, so I solved this using the following code, found online. I'm sorry I can't recall where I found it. (If anyone is interested)

Sub Oppdater_felt()
'
' Oppdater_felt Makro
' Makro registrert 20.10.2012 av Eirik

Dim oStory As Object
Dim oToc As Object

'exit if no document is open
If Documents.Count = 0 Then Exit Sub
Application.ScreenUpdating = False

For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update 'update fields in all stories
Next oStory

For Each oToc In ActiveDocument.TablesOfContents
oToc.Update 'update TOC's
Next oToc

Application.ScreenUpdating = True
End Sub

[gmaxey]

Not trying to be pedantic, but your code will not update all fields in a document. It would miss linked stories and it would miss fields in the text range of any shape anchored to a header of a footer:

Public Sub UpdateAllFields()
Dim rngStory As Word.Range
Dim lngJunk As Long
Dim oShp As Shape
lngJunk = ActiveDocument.Sections(1).Headers(1).Range.StoryType
For Each rngStory In ActiveDocument.StoryRanges
'Iterate through all linked stories
Do
On Error Resume Next
rngStory.Fields.Update
Select Case rngStory.StoryType
Case 6, 7, 8, 9, 10, 11
If rngStory.ShapeRange.Count > 0 Then
For Each oShp In rngStory.ShapeRange
If oShp.TextFrame.HasText Then
oShp.TextFrame.TextRange.Fields.Update
End If
Next
End If
Case Else
'Do Nothing
End Select
On Error GoTo 0
'Get next linked story (if any)
Set rngStory = rngStory.NextStoryRange
Loop Until rngStory Is Nothing
For Each oToc In ActiveDocument.TablesOfContents
oToc.Update
Next oToc
Next
End Sub

[brent.fraser]

Hey everyone,

I can see that you are doing the exact same thing I am (a Userform to put in variables - date, document number and project name) in an attempt to streamline our process for new documents.

I can pass the variable from the Userform to the word document and that's working fine. I then tried to create cross references to the bookmarks from the userform and that isn't working. I can't seem to create a cross reference to a bookmark without any text in it.

I see that you have written some code to update all your headers and footers (bookmarks and cross references) with the information in the userform. How have you done that? I have been banging my head against the wall for hours on it.

Here's the code that passes the information to the bookmark fields in the document:
Private Sub cmdOK_Click()
txtDOC_DATE = Calendar1.Value
txtDOC_DATE.Value = Format(txtDOC_DATE, "mmmm d, yyyy")
Application.ScreenUpdating = False
With ActiveDocument
.Bookmarks("DOC_NAME").Range.Text = txtDOC_NAME.Value
.Bookmarks("PROJECT_NAME").Range.Text = txtPROJECT_NAME.Value
.Bookmarks("PROPOSAL_NUMBER").Range.Text = txtDOC_Number.Value
.Bookmarks("PROPOSAL_DATE").Range.Text = txtDOC_DATE.Value
End With
Application.ScreenUpdating = True
Unload Me
End Sub

Thank you so much all.

[macropod]

Hi Brent,

Your code does not update the bookmarks, so nothing you do to update the fields that refer to them will have any effect. If you study the code that has been posted, you'll see how to do it properly.

[fumei]

And...bookmarks are not fields. Cross-references are, but bookmarks are not. So any Fields.Update instruction will do nothing to bookmarks.

Also your code: With ActiveDocument
.Bookmarks("DOC_NAME").Range.Text = txtDOC_NAME.Value
will put the text into the bookmark range, but it will also delete the bookmark.

[brent.fraser]

Thanks Paul,

I am working on it. So in my word document, I can bookmark some words in the document ("This is the project name"), assign it a bookmark name (PROJECT_NAME) and then create cross-references to it in all the places I want to show it. Then use some of the code above to update "PROJECT_NAME" and it will all work?

Just want to make sure I am on the right path.

Thanks Paul.

Have a good day on the other side of the world.

[brent.fraser]

And...bookmarks are not fields. Cross-references are, but bookmarks are not. So any Fields.Update instruction will do nothing to bookmarks.

Also your code: With ActiveDocument
.Bookmarks("DOC_NAME").Range.Text = txtDOC_NAME.Value
will put the text into the bookmark range, but it will also delete the bookmark.

Yeah, that's the part that I am having problems with. I am trying to cross-reference to the bookmarks but when I send the information, it deletes the bookmarks and thus breaks the corss-reference..... ugh.

I know I will figure it out.

Thanks for the tips.

Brent

[fumei]

Here is a version of a standard routine to update bookmarks. If you put text into bookmarks often, put this in a global template (normal or your own), so you can access it whenever you want.
Sub UpdateBM(strBM As String, strText As String)
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
r.Text = strText
ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End Sub
You use by passing in a bookmark name, and the string you want to put in it.

Call UpDateBM("BookMark_Name", "the text")


You may want to add an exist test, to make suyre the bookmark is really there.

[brent.fraser]

Thank you to all of you that helped out. I have the userform working perfectly and it's filling in all the bookmarks and updating the cross-references in the document.

Now just working on a way to invoke the userform again to update the existing information.

Thanks again everyone.

Brent :beerchug:

[fumei]

How are you calling the userform in the first place?

You could have the userform show by way of a shortcut key; or a button. Then you can call it any time you want.

[brent.fraser]

I am first calling the userform by the following


Private Sub Document_New()
frmBidProposal.Show
End Sub


I also created another form for updating current information. It will pull the current bookmark information in it and populate the userform. I looked up a procedure how to assign a keyboard shortcut to invoke a macro by working with the customize keyboard and assigning the following macro to Alt + U


Public Sub UpdateVariables()
Dim f As frmUpdateVariableInformation
Set f = New frmUpdateVariableInformation
f.Show
On Error Resume Next
Set f = Nothing
End Sub


It all seems to work. When you open the document for the first time, it requires you to fill in the variables in the form. After you have completed the form, you select Alt + U to get the form to update all the variables in the document.

Seems to work.

[fumei]

But you are using two different forms???

[brent.fraser]

Yes I am using two different forms. One to add the first information, the second one to update existing information.

[fumei]

Why not use the same one?

[brent.fraser]

Well I created a template (*.dotm) that will open up the unpopulated userform. This will make sure that the people enter information into the form before proceeding. If any of the fields are blank, they cannot continue. Also, they can't use the x in the top right corner to close the form. They can only cancel and it will close the template. This forces them to enter information into the document before going on.

The second form is for updating the existing information and it pulls in the bookmark text that is already there and populates the form. This way, they can change the revision, date and so on. They can close the userform by the x in the top right or cancel the form to bring them back to the document.

I think it makes sense...... but that's me. If you have any thoughts or opinions, I am all ears.

[fumei]

You could still use just one userform. Have the userform Initialize check for a document variable.

If the value is 0, proceed along the lines of your first userform.
- do not pull anything from the bookmarks
- fields are blank
etc. etc.

When the userform is closed (user is done), set the variable to 1.

If the variable is 1 (userform is used for update), pull the data from the bookmarks and put it into the userform fields.

Even disabling the close "X" can be handled logically.

Two userforms is not "bad", I am just saying that it is not necessary.