Recall data upon re-opening form

[Benji84]

HI

I have created a userform which sends all data recorded in text boxes to bookmarks within document. I have also put a cmd button in the document to reopen the form. However, when the form re-opens all the data entered disappears.

Is there any code that I can use to keep the data entered previously?

Thanks

Ben

[gmaxey]

In your userform initialize procedure set the value of the .text property to the value of the bookmarked. For example say you have a bookmark in the document "nameDP" as a data point for the value entered in a "txtName" field on the form:

Private Sub CommandButton1_Click()
Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks("nameDP").Range
oRng.Text = Me.txtName.Text
ActiveDocument.Bookmarks.Add "nameDP", oRng
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.txtName.Text = ActiveDocument.Bookmarks("nameDP").Range.Text
With Me.txtName
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub

[Benji84]

Hi Greg thanks for your help. Unfortunately it is still not working. I am most probably missing a trick. Here is the code I have. I have substituted for one of my variables "Venue".


Private Sub CommandButton1_Click()
frmUserForm1.Show
Dim oRng As Word.Range
Set oRng = ActiveDocument.Bookmarks("Venue").Range
oRng.Text = Me.txtVenue.Text
ActiveDocument.Bookmarks.Add "Venue", oRng
Unload Me
End Sub
Private Sub cmdCancel_Click()
Unload Me
ThisDocument.Close SaveChanges:=False
End Sub
Private Sub cmdClear_Click()
txtFactsReasons.Value = Null
txtImpairment.Value = Null
txtInterimOrder.Value = Null
txtMisconductCompetence.Value = Null
txtProceedAbsence.Value = Null
txtSanctionsReasons.Value = Null
End Sub
Private Sub cmdOK_Click()

Application.ScreenUpdating = False
With ActiveDocument
.Bookmarks("Venue").Range.Text = txtVenue.Value
.Bookmarks("Date").Range.Text = txtDate.Value
.Bookmarks("Name").Range.Text = txtName.Value
.Bookmarks("Pin").Range.Text = txtPin.Value
.Bookmarks("PartReg").Range.Text = txtPartReg.Value
.Bookmarks("Proved").Range.Text = txtProved.Value
.Bookmarks("NotProved").Range.Text = txtNotProved.Value
.Bookmarks("Sanction").Range.Text = txtSanction.Value
.Bookmarks("IOForm").Range.Text = txtIOForm.Value
.Bookmarks("Details").Range.Text = txtDetails.Value
.Bookmarks("FactsReasons").Range.Text = txtFactsReasons.Value
.Bookmarks("Impairment").Range.Text = txtImpairment.Value
.Bookmarks("InterimOrder").Range.Text = txtInterimOrder.Value
.Bookmarks("MisconductCompetence").Range.Text = txtMisconductCompetence.Value
.Bookmarks("ProceedAbsence").Range.Text = txtProceedAbsence.Value
.Bookmarks("SanctionReasons").Range.Text = txtSanctionsReasons.Value
.Bookmarks("ImpairedForm").Range.Text = cboImpaired.Value
End With
Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.txtVenue.Text = ActiveDocument.Bookmarks("Venue").Range.Text
With Me.txtVenue
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
With cboImpaired
.AddItem "Impaired"
.AddItem "Not impaired"
End With
End Sub


As you can see I have 17 bookmarks that I have to do this for. I'd be grateful if you can shed any light on this.

Ben

[gmaxey]

Yes you are missing the trick. You are putting your userform results "at" bookmark range. The trick is to put the result "in" the bookmark range. That way you can query the range later to see what it is.

1. You set a range = to the bookmark range.
2. You set the text of that range = to the text in the text field.
3. Doing this destroys the bookmark so you have to recreate it.

In the code I sent you earlier I did this just one time for one bookmark and one text field. With 17 it would be better to have a dedicated procedure just for that task and pass arguments. Try this:

Option Explicit
Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Venue", txtVenue.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Date", txtDate.Text
'Change the rest of these accordingly.
' .Bookmarks("Name").Range.Text = txtName.Value
' .Bookmarks("Pin").Range.Text = txtPin.Value
' .Bookmarks("PartReg").Range.Text = txtPartReg.Value
' .Bookmarks("Proved").Range.Text = txtProved.Value
' .Bookmarks("NotProved").Range.Text = txtNotProved.Value
' .Bookmarks("Sanction").Range.Text = txtSanction.Value
' .Bookmarks("IOForm").Range.Text = txtIOForm.Value
' .Bookmarks("Details").Range.Text = txtDetails.Value
' .Bookmarks("FactsReasons").Range.Text = txtFactsReasons.Value
' .Bookmarks("Impairment").Range.Text = txtImpairment.Value
' .Bookmarks("InterimOrder").Range.Text = txtInterimOrder.Value
' .Bookmarks("MisconductCompetence").Range.Text = txtMisconductCompetence.Value
' .Bookmarks("ProceedAbsence").Range.Text = txtProceedAbsence.Value
' .Bookmarks("SanctionReasons").Range.Text = txtSanctionsReasons.Value
' .Bookmarks("ImpairedForm").Range.Text = cboImpaired.Value
Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.txtVenue.Text = ActiveDocument.Bookmarks("Venue").Range.Text
With Me.txtVenue
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtDate.Text = ActiveDocument.Bookmarks("Date").Range.Text
End Sub
Sub Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range(ByRef pBMName As String, pStr As String)
Dim bmRng As Word.Range
Set bmRng = ActiveDocument.Bookmarks(pBMName).Range
bmRng.Text = pStr
ActiveDocument.Bookmarks.Add pBMName, bmRng
End Sub

[Benji84]

Hi Greg,

Thanks for you response. I have changed the code as follows

Option Explicit
Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Venue", txtVenue.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Date", txtDate.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Name", txtName.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Pin", txtPin.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "PartReg", txtPartReg.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Proved", txtNotProved.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Sanction", txtSanction.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "IOForm", txtIOForm.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Details", txtDetails.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "FactsReasons", txtFactsReasons.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Impairment", txtImpairment.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "InterimOrder", txtInterimOrder.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "MisconductCompetence", txtMisconductCompetence.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "ProceedAbsence", txtProceedAbsence.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "SanctionReasons", txtSanctionsReasons.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "ImpairedForm", cboImpaired.Text
Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range "Proved", txtProved.Text
Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.txtVenue.Text = ActiveDocument.Bookmarks("Venue").Range.Text
With Me.txtVenue
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtDate.Text = ActiveDocument.Bookmarks("Date").Range.Text
With Me.txtDate
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtName.Text = ActiveDocument.Bookmarks("Name").Range.Text
With Me.txtName
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtPin.Text = ActiveDocument.Bookmarks("Pin").Range.Text
With Me.txtPin
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtPartReg.Text = ActiveDocument.Bookmarks("PartReg").Range.Text
With Me.txtPartReg
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtProved.Text = ActiveDocument.Bookmarks("Proved").Range.Text
With Me.txtProved
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtNotProved.Text = ActiveDocument.Bookmarks("NotProved").Range.Text
With Me.txtNotProved
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtSanction.Text = ActiveDocument.Bookmarks("Sanction").Range.Text
With Me.txtSanction
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtIOForm.Text = ActiveDocument.Bookmarks("IOForm").Range.Text
With Me.txtIOForm
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtDetails.Text = ActiveDocument.Bookmarks("Details").Range.Text
With Me.txtDetails
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtFactsReasons.Text = ActiveDocument.Bookmarks("FactsReasons").Range.Text
With Me.txtFactsReasons
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtImpairment.Text = ActiveDocument.Bookmarks("Impairment").Range.Text
With Me.txtImpairment
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtMisconductCompetence.Text = ActiveDocument.Bookmarks("MisconductCompetence").Range.Text
With Me.txtMisconductCompetence
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtProceedAbsence.Text = ActiveDocument.Bookmarks("ProceedAbsence").Range.Text
With Me.txtProceedAbsence
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtSanctionsReasons.Text = ActiveDocument.Bookmarks("SanctionReasons").Range.Text
With Me.txtSanctionsReasons
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.cboImpaired.Text = ActiveDocument.Bookmarks("ImpairedForm").Range.Text
With Me.cboImpaired
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtInterimOrder.Text = ActiveDocument.Bookmarks("InterimOrder").Range.Text
With Me.txtInterimOrder
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
With cboImpaired
.AddItem "Impaired"
.AddItem "Not Impaired"
End With
End Sub
Sub Put_Text_IN_Bookmark_Range_NOT_AT_Bookmark_Range(ByRef pBMName As String, pStr As String)
Dim bmRng As Word.Range
Set bmRng = ActiveDocument.Bookmarks(pBMName).Range
bmRng.Text = pStr
ActiveDocument.Bookmarks.Add pBMName, bmRng
End Sub


However, I the frmUserForm1.Show command fails to work when I reload new document. Please excuse my ignorance as I'm very new to VBA so any help you can offer would be greatly appreciated.

Kind Regards

Ben

[gmaxey]

Ben,

You really only need the .Set focus part for the first field in the form. This way any previous text is selected and you can just start typing.

Try the following in the userform module:

Private Sub cmdOK_Click()
Application.ScreenUpdating = False
Write_To_Bookmark "Venue", txtVenue.Text
Write_To_Bookmark "Date", txtDate.Text
Write_To_Bookmark "Name", txtName.Text
Write_To_Bookmark "Pin", txtPin.Text
Write_To_Bookmark "PartReg", txtPartReg.Text
Write_To_Bookmark "Proved", txtNotProved.Text
Write_To_Bookmark "Sanction", txtSanction.Text
Write_To_Bookmark "IOForm", txtIOForm.Text
Write_To_Bookmark "Details", txtDetails.Text
Write_To_Bookmark "FactsReasons", txtFactsReasons.Text
Write_To_Bookmark "Impairment", txtImpairment.Text
Write_To_Bookmark "InterimOrder", txtInterimOrder.Text
Write_To_Bookmark "MisconductCompetence", txtMisconductCompetence.Text
Write_To_Bookmark "ProceedAbsence", txtProceedAbsence.Text
Write_To_Bookmark "SanctionReasons", txtSanctionsReasons.Text
Write_To_Bookmark "ImpairedForm", cboImpaired.Text
Write_To_Bookmark "Proved", txtProved.Text
Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.txtVenue.Text = ActiveDocument.Bookmarks("Venue").Range.Text
With Me.txtVenue
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.txtDate.Text = ActiveDocument.Bookmarks("Date").Range.Text
Me.txtName.Text = ActiveDocument.Bookmarks("Name").Range.Text
Me.txtPin.Text = ActiveDocument.Bookmarks("Pin").Range.Text
Me.txtPartReg.Text = ActiveDocument.Bookmarks("PartReg").Range.Text
Me.txtProved.Text = ActiveDocument.Bookmarks("Proved").Range.Text
Me.txtNotProved.Text = ActiveDocument.Bookmarks("NotProved").Range.Text
Me.txtSanction.Text = ActiveDocument.Bookmarks("Sanction").Range.Text
Me.txtIOForm.Text = ActiveDocument.Bookmarks("IOForm").Range.Text
Me.txtDetails.Text = ActiveDocument.Bookmarks("Details").Range.Text
Me.txtFactsReasons.Text = ActiveDocument.Bookmarks("FactsReasons").Range.Text
Me.txtImpairment.Text = ActiveDocument.Bookmarks("Impairment").Range.Text
Me.txtMisconductCompetence.Text = ActiveDocument.Bookmarks("MisconductCompetence").Range.Text
Me.txtProceedAbsence.Text = ActiveDocument.Bookmarks("ProceedAbsence").Range.Text
Me.txtSanctionsReasons.Text = ActiveDocument.Bookmarks("SanctionReasons").Range.Text
Me.cboImpaired.Text = ActiveDocument.Bookmarks("ImpairedForm").Range.Text
Me.txtInterimOrder.Text = ActiveDocument.Bookmarks("InterimOrder").Range.Text
End Sub
Sub Write_To_Bookmark(ByRef pBMName As String, pStr As String)
Dim bmRng As Word.Range
Set bmRng = ActiveDocument.Bookmarks(pBMName).Range
bmRng.Text = pStr
ActiveDocument.Bookmarks.Add pBMName, bmRng
End Sub


Put the following a standard code module:

Sub AutoNew()
CallForm
End Sub
Sub CallForm()
frmUserForm1.Show
End Sub


This will display a new blank form when a new document is created. Fill in the data and click the OK button on the form. This populates the data in the document. Assign "CallForm" to the button you have in your document. This will redisplay the userform with data in the document.

[Benji84]

Thanks for the help.

I have made amendments but then when I try it out it gives me an error saying

Run time error 5941 and then when I debug it flags up

frmUserForm1.Show

I'm not too sure why.

Thanks

Ben

[Tinbendr]

Run time error 5941 and then when I debug it flags up

frmUserForm1.Show
You'll have to press F8 until you find the line where the error actually occures. I can't remember why if does that. I'm sure it's a built-in FEATURE. :p

[gmaxey]

Can you upload your document and I will have a look. Thanks.

[Benji84]

Can I send it to your private address rather than post it up here. my e-mail address is ben_hutchens@hotmail.co.uk


Thanks

Ben

[gmaxey]

Sure.

[geekgirlau]

I haven't looked at the error, but you can streamline the code somewhat further:


Private Sub cmdOK_Click()
Dim ctl As Control

Application.ScreenUpdating = False

' assumes you are writing to a bookmark for every text box control,
' and that the naming convention for the control and the matching
' bookmark are consistent
For Each ctl In Me.Controls
If Left(ctl.Name, 3) = "txt" Then
Write_To_Bookmark Mid(ctl.Name, 4), ctl.Text
End If
Next ctl

Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim ctl As Control

' assumes there is a bookmark for every text box control,
' and that the naming convention for the control and the matching
' bookmark are consistent
For Each ctl In Me.Controls
If Left(ctl.Name, 3) = "txt" Then
ctl.Text = ActiveDocument.Bookmarks(Mid(ctl.Name, 4)).Range.Text
End If
Next ctl

With Me.txtVenue
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub

[gmaxey]

Yes exactly. Ben never sent the document, but he did indicate that he got the issue resoloved.