automatically populating dates in a userform

L

Linda Fiske

Hello:
I have created ongoing one-week schedules in MS Word and want to have the
user type in the beginning date and have the rest of the date fields
populate from that. Here is a plain text version example of the table I
created.

July 30 [bookmark "firstDate] - August 3 [bookmark "lastDate"]
Monday 30 [bookmark "mon"]
Tuesday 31 [bookmark "tues"]
Wednesday 1 [bookmark "weds"}
Thursday 2 [bookmark "thurs"]
Friday 3 [bookmark "fri"]

I am having the user input the first date in a visual basic form with the
following :
visible textbox named firstDate
invisible textbox named lastDate
invisible textbox named mon
invisible textbox named tues
invisible textbox named weds
invisible textbox named thurs
invisible textbox named fri
command control named buttonPopulate
several labels

Here is the code:
Private Sub buttonPopulate_Click()
With ActiveDocument
.Bookmarks("firstDate").Range.InsertBefore firstDate
.Bookmarks("lastDate").Range.InsertBefore lastDate
.Bookmarks("mon").Range.InsertBefore mon
.Bookmarks("tues").Range.InsertBefore tues
.Bookmarks("weds").Range.InsertBefore weds
.Bookmarks("thurs").Range.InsertBefore thurs
.Bookmarks("fri").Range.InsertBefore fri
End With

UserForm1.Hide
End Sub

Private Sub firstDate_Change()
End Sub

Private Sub labelBegDate_Click()
End Sub

Private Sub labelDirections_Click()
End Sub

Private Sub lastDate_Change()
Dim lastDate As Date
Dim IntervalType As String
Dim Number As Integer
IntervalType = "d" ' "d" specifies days as interval.
lastDate = DateAdd(d, 5, firstDate)
End Sub

Private Sub mon_Change()
mon as date
mon = Day (firstDate)
End Sub

Private Sub tues_Change()
tues as date
tues = DateAdd(d, 1, firstDate)
Day(tues)
End Sub

Private Sub weds_Change()
weds as date
weds = DateAdd(d, 2, firstDate)
Day(weds)
End Sub

Private Sub thurs_Change()
thurs as date
thurs = DateAdd(d, 3, firstDate)
Day(thurs)
End Sub

Private Sub fri_Change()
fri as date
fri = DateAdd(d, 4, firstDate)
Day(fri)
End Sub

Private Sub UserForm_Click()
End Sub

I got the "firstDate" bookmark to populate, but none of the others. Am I
even on the right track? Any assistance anyone can offer would be very much
appreciated.

Thank you.
Linda
 
D

Doug Robbins - Word MVP

In responding to your post in the UserForm newsgroup, I assumed that you
were using formfields in a document protected for forms. While it now
appears that you are using a userform, the logic is really the same and a
modification of the code that I was suggesting be run on exit from the Start
date formfield, would be run on exit from the firstDate textbox.

Post back here if you get stuck with converting what I had suggested for use
with a userform.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Linda Fiske said:
Hello:
I have created ongoing one-week schedules in MS Word and want to have the
user type in the beginning date and have the rest of the date fields
populate from that. Here is a plain text version example of the table I
created.

July 30 [bookmark "firstDate] - August 3 [bookmark "lastDate"]
Monday 30 [bookmark "mon"]
Tuesday 31 [bookmark "tues"]
Wednesday 1 [bookmark "weds"}
Thursday 2 [bookmark "thurs"]
Friday 3 [bookmark "fri"]

I am having the user input the first date in a visual basic form with the
following :
visible textbox named firstDate
invisible textbox named lastDate
invisible textbox named mon
invisible textbox named tues
invisible textbox named weds
invisible textbox named thurs
invisible textbox named fri
command control named buttonPopulate
several labels

Here is the code:
Private Sub buttonPopulate_Click()
With ActiveDocument
.Bookmarks("firstDate").Range.InsertBefore firstDate
.Bookmarks("lastDate").Range.InsertBefore lastDate
.Bookmarks("mon").Range.InsertBefore mon
.Bookmarks("tues").Range.InsertBefore tues
.Bookmarks("weds").Range.InsertBefore weds
.Bookmarks("thurs").Range.InsertBefore thurs
.Bookmarks("fri").Range.InsertBefore fri
End With

UserForm1.Hide
End Sub

Private Sub firstDate_Change()
End Sub

Private Sub labelBegDate_Click()
End Sub

Private Sub labelDirections_Click()
End Sub

Private Sub lastDate_Change()
Dim lastDate As Date
Dim IntervalType As String
Dim Number As Integer
IntervalType = "d" ' "d" specifies days as interval.
lastDate = DateAdd(d, 5, firstDate)
End Sub

Private Sub mon_Change()
mon as date
mon = Day (firstDate)
End Sub

Private Sub tues_Change()
tues as date
tues = DateAdd(d, 1, firstDate)
Day(tues)
End Sub

Private Sub weds_Change()
weds as date
weds = DateAdd(d, 2, firstDate)
Day(weds)
End Sub

Private Sub thurs_Change()
thurs as date
thurs = DateAdd(d, 3, firstDate)
Day(thurs)
End Sub

Private Sub fri_Change()
fri as date
fri = DateAdd(d, 4, firstDate)
Day(fri)
End Sub

Private Sub UserForm_Click()
End Sub

I got the "firstDate" bookmark to populate, but none of the others. Am I
even on the right track? Any assistance anyone can offer would be very
much
appreciated.

Thank you.
Linda
Click to expand...
 
L

Linda Fiske

Thank you for the help, Doug. I had been experimenting with both types:
formfields in a protected document and also userfields.
I input the code you provided into the document with formfields and it
worked beautifully. Thank you. For learning purposes I am trying to get it
to work in the document with the userform, but am running into snags. Do I
have to abolish the bookmarks in the document and insert formfields, or can
I keep the book marks and have the information populate in the textBoxes in
the form and then have the command button push the data into the bookmarked
locations in the document?
Thanks again for your help. I wasn't sure how to code the textBoxes.

Private Sub buttonPopulate_Click()
With ActiveDocument
.Bookmarks("firstDate").Range.InsertBefore Start
.Bookmarks("lastDate").Range.InsertBefore Finish
.Bookmarks("mon").Range.InsertBefore Monday
.Bookmarks("tues").Range.InsertBefore Tuesday
.Bookmarks("weds").Range.InsertBefore Wednesday
.Bookmarks("thurs").Range.InsertBefore Thursday
.Bookmarks("fri").Range.InsertBefore Friday
End With

UserForm1.Hide
End Sub

Private Sub Start_Change()
Dim sdate As Date
Dim sday As Integer
With ActiveDocument

If IsDate(.TextBox("Start").Value) Then
sdate = .TextBox("Start").Value
sday = Format(sdate, "d")
.TextBox("Finish").Value = Format(DateAdd("d", 4, sdate), "MMMM dd,
yyyy")
.TextBox("Monday").Value = "Monday " & Format(sday, "0#")
.TextBox("Tuesday").Value = "Tuesday " & Format(sday + 1, "0#")
.TextBox("Wednesday").Value = "Wednesday " & Format(sday + 2, "0#")
.TextBox("Thursday").Value = "Thursday " & Format(sday + 3, "0#")
.TextBox("Friday").Value = "Friday " & Format(sday + 4, "0#")
Else
MsgBox "You did not input a valid date."
End If
End With
End Sub


..


Doug Robbins - Word MVP said:
In responding to your post in the UserForm newsgroup, I assumed that you
were using formfields in a document protected for forms. While it now
appears that you are using a userform, the logic is really the same and a
modification of the code that I was suggesting be run on exit from the Start
date formfield, would be run on exit from the firstDate textbox.

Post back here if you get stuck with converting what I had suggested for use
with a userform.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Linda Fiske said:
Hello:
I have created ongoing one-week schedules in MS Word and want to have the
user type in the beginning date and have the rest of the date fields
populate from that. Here is a plain text version example of the table I
created.

July 30 [bookmark "firstDate] - August 3 [bookmark "lastDate"]
Monday 30 [bookmark "mon"]
Tuesday 31 [bookmark "tues"]
Wednesday 1 [bookmark "weds"}
Thursday 2 [bookmark "thurs"]
Friday 3 [bookmark "fri"]

I am having the user input the first date in a visual basic form with the
following :
visible textbox named firstDate
invisible textbox named lastDate
invisible textbox named mon
invisible textbox named tues
invisible textbox named weds
invisible textbox named thurs
invisible textbox named fri
command control named buttonPopulate
several labels

Here is the code:
Private Sub buttonPopulate_Click()
With ActiveDocument
.Bookmarks("firstDate").Range.InsertBefore firstDate
.Bookmarks("lastDate").Range.InsertBefore lastDate
.Bookmarks("mon").Range.InsertBefore mon
.Bookmarks("tues").Range.InsertBefore tues
.Bookmarks("weds").Range.InsertBefore weds
.Bookmarks("thurs").Range.InsertBefore thurs
.Bookmarks("fri").Range.InsertBefore fri
End With

UserForm1.Hide
End Sub

Private Sub firstDate_Change()
End Sub

Private Sub labelBegDate_Click()
End Sub

Private Sub labelDirections_Click()
End Sub

Private Sub lastDate_Change()
Dim lastDate As Date
Dim IntervalType As String
Dim Number As Integer
IntervalType = "d" ' "d" specifies days as interval.
lastDate = DateAdd(d, 5, firstDate)
End Sub

Private Sub mon_Change()
mon as date
mon = Day (firstDate)
End Sub

Private Sub tues_Change()
tues as date
tues = DateAdd(d, 1, firstDate)
Day(tues)
End Sub

Private Sub weds_Change()
weds as date
weds = DateAdd(d, 2, firstDate)
Day(weds)
End Sub

Private Sub thurs_Change()
thurs as date
thurs = DateAdd(d, 3, firstDate)
Day(thurs)
End Sub

Private Sub fri_Change()
fri as date
fri = DateAdd(d, 4, firstDate)
Day(fri)
End Sub

Private Sub UserForm_Click()
End Sub

I got the "firstDate" bookmark to populate, but none of the others. Am I
even on the right track? Any assistance anyone can offer would be very
much
appreciated.

Thank you.
Linda
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calendar Question 6
Relationship between a Calendar Table and Employee Table 1
function help for counting text as a value.... 1
Excel Table Help 2
Move from one table to other table in a different format 1
Creating a macro to update dates in a word doc 1
modify code to just insert Mon-Sat date and day for Given Week End 0
Out of Stack Space error 4

Top