Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-29-2014, 05:28 PM
alshcover alshcover is offline Update Bookmarks from a Userform Windows 7 64bit Update Bookmarks from a Userform Office 2010 64bit
Novice
Update Bookmarks from a Userform
 
Join Date: Apr 2014
Posts: 9
alshcover is on a distinguished road
Question Update Bookmarks from a Userform

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:

Quote:
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)?
Reply With Quote
  #2  
Old 04-29-2014, 06:57 PM
macropod's Avatar
macropod macropod is offline Update Bookmarks from a Userform Windows 7 32bit Update Bookmarks from a Userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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:
Code:
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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-30-2014, 10:56 AM
alshcover alshcover is offline Update Bookmarks from a Userform Windows 7 64bit Update Bookmarks from a Userform Office 2010 64bit
Novice
Update Bookmarks from a Userform
 
Join Date: Apr 2014
Posts: 9
alshcover is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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:
Code:
Private Sub cmdOK_Click()
...
End Sub
That worked. Thank you very much!
Reply With Quote
  #4  
Old 04-30-2014, 03:37 PM
alshcover alshcover is offline Update Bookmarks from a Userform Windows 7 64bit Update Bookmarks from a Userform Office 2010 64bit
Novice
Update Bookmarks from a Userform
 
Join Date: Apr 2014
Posts: 9
alshcover is on a distinguished road
Question How do I insert text that is dependent on the ComboBox value?

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:
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
Reply With Quote
  #5  
Old 04-30-2014, 04:11 PM
macropod's Avatar
macropod macropod is offline Update Bookmarks from a Userform Windows 7 32bit Update Bookmarks from a Userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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):
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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 04-30-2014, 05:03 PM
alshcover alshcover is offline Update Bookmarks from a Userform Windows 7 64bit Update Bookmarks from a Userform Office 2010 64bit
Novice
Update Bookmarks from a Userform
 
Join Date: Apr 2014
Posts: 9
alshcover is on a distinguished road
Default

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

For example (just taking the basic bookmark code without error handling),
Code:
.Bookmarks("ID").Range.Text = ____.Value
Reply With Quote
  #7  
Old 04-30-2014, 05:30 PM
macropod's Avatar
macropod macropod is offline Update Bookmarks from a Userform Windows 7 32bit Update Bookmarks from a Userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by alshcover View Post
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:
Code:
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:
Code:
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 "))
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 05-01-2014, 05:26 PM
alshcover alshcover is offline Update Bookmarks from a Userform Windows 7 64bit Update Bookmarks from a Userform Office 2010 64bit
Novice
Update Bookmarks from a Userform
 
Join Date: Apr 2014
Posts: 9
alshcover is on a distinguished road
Default

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?
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
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
Reply With Quote
  #9  
Old 05-01-2014, 05:32 PM
macropod's Avatar
macropod macropod is offline Update Bookmarks from a Userform Windows 7 32bit Update Bookmarks from a Userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by alshcover View Post
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:
Code:
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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 05-02-2014, 11:20 AM
alshcover alshcover is offline Update Bookmarks from a Userform Windows 7 64bit Update Bookmarks from a Userform Office 2010 64bit
Novice
Update Bookmarks from a Userform
 
Join Date: Apr 2014
Posts: 9
alshcover is on a distinguished road
Default

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?

Last edited by macropod; 05-02-2014 at 02:32 PM. Reason: Deleted unnecessary quote of entire post replied to.
Reply With Quote
  #11  
Old 05-02-2014, 02:44 PM
macropod's Avatar
macropod macropod is offline Update Bookmarks from a Userform Windows 7 32bit Update Bookmarks from a Userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 01-12-2015, 06:39 PM
scarda scarda is offline Update Bookmarks from a Userform Windows 8 Update Bookmarks from a Userform Office 2010 64bit
Novice
 
Join Date: Jan 2015
Posts: 5
scarda is on a distinguished road
Default Datepicker with suffix

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?

Quote:
Originally Posted by macropod View Post
Try:
Code:
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
Reply With Quote
  #13  
Old 01-12-2015, 06:53 PM
macropod's Avatar
macropod macropod is offline Update Bookmarks from a Userform Windows 7 64bit Update Bookmarks from a Userform Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

To do that, you would need to use code like:
Code:
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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
date, format, userform

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update Bookmarks from a Userform Checkbox in Userform lukael Excel Programming 5 02-18-2014 05:20 AM
Userform Code not quite right - help please vbanovice Word VBA 1 09-29-2013 09:20 PM
Update Bookmarks from a Userform Help with a drop down in userform and variables leemoreau Word VBA 1 09-14-2013 06:01 AM
VBA code to update record in Access 2003 using Userform in Excel primmer3001 Excel Programming 0 08-29-2011 04:25 PM
Update Bookmarks from a Userform Update bookmarks and variables in document webharvest Word VBA 5 06-21-2011 03:22 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft