#1
|
|||
|
|||
Update Bookmarks from a Userform
What I have done so far:
Here is the code: Quote:
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)? |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
That worked. Thank you very much!
|
#4
|
|||
|
|||
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:
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 |
#5
|
||||
|
||||
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] |
#6
|
|||
|
|||
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 |
#7
|
||||
|
||||
Quote:
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 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 Call UpdateBookmark("DateFiled", Format(dtDateFiled.Value, "MMMM DD, YYYY "))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
Quote:
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] |
#10
|
|||
|
|||
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. |
#11
|
||||
|
||||
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] |
#12
|
|||
|
|||
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:
|
#13
|
||||
|
||||
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] |
Tags |
date, format, userform |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 and variables in document | webharvest | Word VBA | 5 | 06-21-2011 03:22 PM |