Update Bookmarks from a Userform

[alshcover]

What I have done so far:

• I created a UserForm.
  
• I added a Control (DTPicker) and named it "dtDateFiled".
  
• I added a Control (CommandButton) and named it "cmdOK".
  
• I inserted a bookmark in the Word Template titled "DateFiled"; therefore, the date that the User selects on the UserForm (dtDateFiled) shows up in the Word document.

Here is the code:

Private Sub cmdOK_Click()

Application.ScreenUpdating = False

With ActiveDocument

.Bookmarks("DateFiled").Range.Text = dtDateFiled.Value

End With

Application.ScreenUpdating = True

Unload Me

End Sub

What currently happens:


The date selected by the User is inserted at the bookmark just fine, but the date displays in this format: MM/DD/YYYY (e.g., 4/29/2014).

Question:
How do I change the date format to display: Month DD, YYYY (e.g., April 29, 2014)?

[macropod]

All you need do is specify the output format of 'dtDateFiled.Value'. However, as written, your code also doesn't allow the date to be updated within the bookmark and lacks error-handling. Try:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim BmkNm As String, NewTxt As String, BmkRng As Range
BmkNm = "DateFiled"
With ActiveDocument
  If .Bookmarks.Exists(BmkNm) Then
    Set BmkRng = .Bookmarks(BmkNm).Range
    BmkRng.Text = Format(dtDateFiled.Value, "MMMM DD, YYYY ")
    .Bookmarks.Add BmkNm, BmkRng
  Else
    MsgBox "Bookmark: " & BmkNm & " not found."
  End If
End With
Set BmkRng = Nothing
Application.ScreenUpdating = True
Unload Me
End Sub

[alshcover]

Originally Posted by macropod

All you need do is specify the output format of 'dtDateFiled.Value'. However, as written, your code also doesn't allow the date to be updated within the bookmark and lacks error-handling. Try:

Private Sub cmdOK_Click()
...
End Sub

That worked. Thank you very much!

[alshcover]

I created a UserForm. I added a ComboBox ("cboName") with 4 names available. When the User selects a name from cboName, the value will be inserted at a bookmark titled "Name" in the Word document.

However, there are ID numbers associated with each of the names that I would like inserted in the Word document at a location different than the "Name" bookmark. Therefore, is it possible to have the ID number automatically inserted in the Word document (at a bookmark titled "ID") based on User's selection in cboName?

For example, the ID numbers associated with each name:

• Bob = 1
• Cindy = 2
• Peter = 3
• Vanessa = 4

Here is the code:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
    With ActiveDocument
        .Bookmarks("Name").Range.Text = cboName.Value
    End With
Application.ScreenUpdating = True
Unload Me
End Sub

Private Sub UserForm_Initialize()
    With cboName
        .AddItem "Bob"
        .AddItem "Cindy"
        .AddItem "Peter"
        .AddItem "Vanessa"
    End With
End Sub

[macropod]

You could use code like the following to populate a 'StrOut' variable, which is then used to update the document's bookmark (for which I've already provided demo code):

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim StrOut As String
Select Case cboName.Value
  Case "Bob": StrOut = "1"
  Case "Cindy": StrOut = "2"
  Case "Peter": StrOut = "3"
  Case "Vanessa": StrOut = "4"
End Select
'update code goes here
Application.ScreenUpdating = True
Unload Me
End Sub

[alshcover]

How are StrOut variables defined as values in the bookmark code?

For example (just taking the basic bookmark code without error handling),

.Bookmarks("ID").Range.Text = ____.Value

[macropod]

Originally Posted by alshcover

How are StrOut variables defined as values in the bookmark code?

You do that by passing the StrOut value to the code you use to update the bookmark.

Since it seems you have multiple bookmarks to update, I've merged this thread and your previous one, so you can see how the two examples we have so far can be made to work with some common bookmark-updating code.

For the two examples so far we could use:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim StrOut As String
StrOut = Format(dtDateFiled.Value, "MMMM DD, YYYY ")
Call UpdateBookmark("DateFiled", StrOut)
Select Case cboName.Value
  Case "Bob": StrOut = "1"
  Case "Cindy": StrOut = "2"
  Case "Peter": StrOut = "3"
  Case "Vanessa": StrOut = "4"
End Select
Call UpdateBookmark("Name", StrOut)
Application.ScreenUpdating = True
End Sub

You'll see how we now have two references to 'Call UpdateBookmark', with the bookmark name & content being included as arguments. Those two lines run the following sub, which handles the bookmark updating:

Sub UpdateBookmark(StrBkMk As String, StrTxt As String)
Dim BkMkRng As Range
With ActiveDocument
  If .Bookmarks.Exists(StrBkMk) Then
    Set BkMkRng = .Bookmarks(StrBkMk).Range
    BkMkRng.Text = StrTxt
    .Bookmarks.Add StrBkMk, BkMkRng
  End If
End With
Set BkMkRng = Nothing
End Sub

Although I've referenced StrOut twice in the cmdOK_Click sub's call to the UpdateBookmark sub, just to show how the same variable can be re-used, the first instance could have been replaced with:
Call UpdateBookmark("DateFiled", Format(dtDateFiled.Value, "MMMM DD, YYYY "))

[alshcover]

My original two questions/threads were for two different UserForms, so I'd like to keep the codes separate only to simplify things because I'm not well versed in this field... yet.

I have a UserForm with one control (cboName).
cboName values = Bob, Cindy, Peter, Vanessa. Each person has a unique ID number (1, 2, 3, 4 respectively).

Therefore, I created StrOut variables (1, 2, 3, 4), which are associated with each cboName value.

The document has a bookmark titled "Name". The value for cboName is inserted at this bookmark.

The document has another bookmark titled "ID". I would like the StrOut variable for its associated cboName value to be inserted at this bookmark.

With your help, I've put together the code below. Is this the correct use of the StrOut variables and the Call UpdateBookmark?

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim StrOut As String
Select Case cboName.Value
  Case "Bob": StrOut = "1"
  Case "Cindy": StrOut = "2"
  Case "Peter": StrOut = "3"
  Case "Vanessa": StrOut = "4"
End Select
Call UpdateBookmark("ID", StrOut)
With ActiveDocument
    .Bookmarks("Name").Range.Text = cboName.Value
End With
Application.ScreenUpdating = True
Unload Me
End Sub

Sub UpdateBookmark(StrBkMk As String, StrTxt As String)
Dim BkMkRng
With ActiveDocument
If .Bookmarks.Exists(StrBkMk) Then
    Set BkMkRng = .Bookmarks(StrBkMk).Range
    BkMkRng.Text = StrTxt
    .Bookmarks.Add StrBkMk, BkMkRng
End If
End With
Set BkMkRng = Nothing
End Sub

[macropod]

Originally Posted by alshcover

The document has a bookmark titled "Name". The value for cboName is inserted at this bookmark.

The document has another bookmark titled "ID". I would like the StrOut variable for its associated cboName value to be inserted at this bookmark.

With your help, I've put together the code below. Is this the correct use of the StrOut variables and the Call UpdateBookmark?

You're still not updating the "Name" bookmark for 'cboName.Value' properly:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim StrOut As String
StrOut = cboName.Value
Call UpdateBookmark("Name", StrOut)
Select Case cboName.Value
  Case "Bob": StrOut = "1"
  Case "Cindy": StrOut = "2"
  Case "Peter": StrOut = "3"
  Case "Vanessa": StrOut = "4"
End Select
Call UpdateBookmark("ID", StrOut)
Application.ScreenUpdating = True
End Sub

[alshcover]

With the current code, the cboName.Value is being inserted at Bookmark "Name" as I wanted it to. What kind of possible errors will the current code create regarding the bookmark?

[macropod]

I don't see that the code would 'create' errors. Of course, if you have an empty or invalid cboName.Value, that's what will be output at the Bookmark "Name". For the same reason, because the Select Case statement only tests valid values, you could end up with an invalid output for the Bookmark "ID". That could be prevented by adding:
Case Else: StrOut = ""

PS: Please don't automatically quote the entire posts being replied to. If there's something in a reply you need to reference, quote just that portion.

[scarda]

Has anyone had any luck in adding a st (for 1), nd (for 2) rd (for 3) or th (for 4,5,6,7,8,9,0) following the DD in this code?

Originally Posted by macropod

Try:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim BmkNm As String, NewTxt As String, BmkRng As Range
BmkNm = "DateFiled"
With ActiveDocument
  If .Bookmarks.Exists(BmkNm) Then
    Set BmkRng = .Bookmarks(BmkNm).Range
    BmkRng.Text = Format(dtDateFiled.Value, "MMMM DD, YYYY ")
    .Bookmarks.Add BmkNm, BmkRng
  Else
    MsgBox "Bookmark: " & BmkNm & " not found."
  End If
End With
Set BmkRng = Nothing
Application.ScreenUpdating = True
Unload Me
End Sub

[macropod]

To do that, you would need to use code like:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Dim BmkNm As String, NewTxt As String, BmkRng As Range
BmkNm = "DateFiled"
With ActiveDocument
  If .Bookmarks.Exists(BmkNm) Then
    Set BmkRng = .Bookmarks(BmkNm).Range
    BmkRng.Text = Format(dtDateFiled.Value, "MMMM ") & Ordinal(Format(dtDateFiled.Value, "DD")) & Format(dtDateFiled.Value, ", YYYY ")
    .Bookmarks.Add BmkNm, BmkRng
  Else
    MsgBox "Bookmark: " & BmkNm & " not found."
  End If
End With
Set BmkRng = Nothing
Application.ScreenUpdating = True
Unload Me
End Sub
 
Function Ordinal(Val As Integer) As String
Dim strOrd As String
If (Val Mod 100) < 11 Or (Val Mod 100) > 13 Then strOrd = Choose(Val Mod 10, "st", "nd", "rd") & ""
Ordinal = Val & IIf(strOrd = "", "th", strOrd)
End Function