Preserving & clearing userform data

[Greg]

Hi everyone,

With the help of this forum I have constructed some very useful macros and userforms to fill bookmarks.

However, once executed, my userforms cannot be re-used to make minor alterations without re-entering everything once more.

If possible, I would prefer bring up the userform (with the existing data still showing in the userform) and make minor changes only. It would also help if I had a "Clear" button so that I could remove all the existing data if I wished to.

Can anyone help me enhance my code in the desired manner?

Greg.

[ALe]

Many ways.

1. store values in cells and get them back when you re-open the form
2. use savesettings
3. use public declarations
4. set control last value as its default value

[matthewspatrick]

As ALe mentions, in Excel probably the easiest way is to store UserForm results in a worksheet. I often do this using a hidden worksheet, with range names matching the names of the controls. If it's an add-in, it's unnecessary to hide the worksheet, as the whole workbook is hidden for an add-in.

You are working in Word, though, so this is not an option. I would recommend that you either use SaveSetting (again, as ALe mentions) to save entries to your Registry, or that you save past entries in a simple text file.

[fumei]

OR - if the userform controls are putting information INTO bookmarks, then in the Initialize event of the userform, you could do a check for values in those bookmarks.

In other words, you could fill the controls on the userform with the existing bookmarks values. The userform will show current values, and the user can change as they will.

A Clear button is fairly easy.Sub Clear_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
Me.Controls(ctl.Name).Text = ""
ElseIf TypeOf ctl Is CheckBox Then
Me.Controls(ctl.Name).Value = False
ElseIf TypeOf ctl Is ComboBox Then
Me.Controls(ctl.Name).ListIndex = 0
End If
Next
End Sub

[Greg]

Thanks Fumei (and others).

You correctly surmise that I am putting information into bookmarks and that presumably means I have to use the Initialise event you refer to.

Do you mind telling me how the "check for values" code should be written?

The Help files suggest the following code to save the data but having read your comments I doubt that the help file code will be of any use to me.

'Code in module to declare public variables
Public strRegion As String
Public intSalesPersonID As Integer
Public blnCancelled As Boolean

'Code in form
Private Sub cmdCancel_Click()
Module1.blnCancelled = True
Unload Me
End Sub

Private Sub cmdOK_Click()
'Save data
intSalesPersonID = txtSalesPersonID.Text
strRegion = lstRegions.List(lstRegions.ListIndex)
Module1.blnCancelled = False
Unload Me
End Sub

Private Sub UserForm_Initialize()
Module1.blnCancelled = True
End Sub

'Code in module to display form
Sub LaunchSalesPersonForm()
frmSalesPeople.Show
If blnCancelled = True Then
MsgBox "Operation Cancelled!", vbExclamation
Else
MsgBox "The Salesperson's ID is: " & _
intSalesPersonID & _
"The Region is: " & strRegion
End If
End Sub

Greg.
Edited 20-Jul-06 by geekgirlau. Reason: insert vba tags

[fumei]

Aaiiieeeee...what the heck is that???

Please use the VBA tags to post code.

Actually, Help file help a lot. You just have to work with them. OK, what....EXACTLY...have you tried? Have you actually put information from the userform into bookmarks? If so..describe precisely how you did that.


Write out - step-by-step - EXACTLY what you want to happen. Or perhaps think you want to happen.

I see nothing here to indicate the use of bookmarks. It just displays a message box.

I am not sure you need the variable strRegion as Public. Why do you have it Public?

As for check for values...this depends on EXACTLY what you have in the document.

Do you have intitial values? When do you want to check. I assume when the userform starts up. When would the user possibly need a refresh of the information on the userform? Are you talking about using the userform to put information into the document - saving it - then sometime later opening it and having the userform fills with current data?

You MUST think the whole thing through, otherwise doing this stuff gets real messy.

[fumei]

I guess I should mention - considering the text of your Subject - that you can not really preserve userform data. Once the userform is unloaded, any values it had are destroyed. However, if what you mean is, can a userform be filled with existing data (if it DOES exist) in the document? Yes.

[Greg]

The code I included is not mine and was copied straight from the help files. Please ignore it.

My actual requirements are much more straightforward. In a Word document I enter data through a Userform directly into bookmarks.

However, to change an entry I have to bring up the Userform (which appears empty) and re-enter all the previously entered data to make the necessary changes.

This wastes time and I want the previously entered data to reappear in the Userform when I bring it up on succesive occassions so that I only need to change that part of it that requires amendment.

I have an old Wordbasic macro that does this but I am unfamiliar with the language and don't fancy trying to adapt it to my present document.

Greg.

[mdmackillop]

Hi Greg,
If you can post your userform and the code, that will tell us where the data is going and give a good idea of where to fetch it back from; and a sanitised copy of your document would also make life easier.
Regards
MD

[Greg]

Thanks MD,

The document is attached.

Greg.

[mdmackillop]

Thanks Greg,
But you should really have used ficticious names on the form.
Regards
MD

[Greg]

Thanks MD. I thought I had. Anyway, I hope there is a simple solution.

[fumei]

Greg, Malcolm is pulling your leg. "Scrotum Square"?????

Oh bollocks!

Hey!.....that is my code in there. Eeek.

Never ever have a starting document with data in it. In this case, it is OK, as you want to figure out how to fill the userform with data from the document. But generally speaking, you really should be working from an empty template, a .DOT file.

You need to be much more careful with your bookmarks.

Costs
CostsonClaim

both point to the same location, and;

CostsOnSummons go to the same location as well, but includes the text (numbers) you want. Three bookmarks at the same location is NOT a good idea.

OK, to bring data FROM a bookmark into the userform is easy. However, I question why you wish to do this. I hope - really hope - it is not your idea to use the same document over again, with new data, for a different person. Or new data for a different claim.

That would be very bad. Very bad. Do NOT do that.

But to get data INTO the userform from the document, essentially you reverse putting stuff into the document.Private Sub UserForm_Initialize()
Dim oBM As Word.Bookmarks
Set oBM = ActiveDocument.Bookmarks
If oBM("Amount").Range.Text <> "" Then
Me.txtAmount.Text = oBM("Amount").Range.Text
End If
If oBM("CostsOnClaim").Range.Text <> "" Then
Me.txtCostsOnClaim.Text = oBM("CostsOnClaim").Range.Text
End If
If oBM("StartDate").Range.Text <> "" Then
Me.txtStartDate.Text = oBM("StartDate").Range.Text
End If
' etc etc etc
Set oBM = Nothing
' and we will have NO discussion on the
' explicit deletion of object variables
' thank you very much
End SubAgain, though. if this is for use of an amendment of a current document...OK. But if this is to help make a NEW document (and then doing a SaveAs)...then do NOT do that!

[mdmackillop]

If I was to create this, I would looking at storing the data in tables (of some kind) to be used as a datasource for a mailmerge.
Regards
MD

[Greg]

Many thanks fumei.

The main reason for this coded is to amend the document if the wrong data gets entered. But the document will form the basis of a precedent for use in other cases. Do you see this as a problem?

Greg.

[Greg]

Hi again fumei,

Your code works beautifully on the document provided but I am having difficulty adapting it to a different document. The Userform code that document is as follows:


Option Explicit
Private Sub ClickToFinish_Click()
Dim oControl As Control
Dim strBM_Name As String
Dim strBM_Text As String
For Each oControl In frmDataInput.Controls
If Left(oControl.Name, 3) = "txt" Then
strBM_Name = Right(oControl.Name, Len(oControl.Name) - 3)
strBM_Text = frmDataInput.Controls(oControl.Name).Text
Call FillABookmark(strBM_Name, strBM_Text)
End If
Next
ActiveDocument.Fields.Update
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub


It would appear that something a little different may be required in this case.

Your further help will be appreciated.

Greg.
Edited 20-Jul-06 by geekgirlau. Reason: insert vba tags

[lucas]

Set oBM = Nothing
' and we will have NO discussion on the
' explicit deletion of object variables
' thank you very much
End Sub
:rotlaugh: What....I didn't say anything!

[mdmackillop]

Hi Greg,
Please use the VBA tags on any posted code
Regards
MD

[fumei]

But the document will form the basis of a precedent for use in other cases. Do you see this as a problem?Depends on what you mean by "precedence". If you mean, the concept, the process...no, I see no problem. If you mean to actually USE the file in ANY way for other cases, I repeat...do NOT do this. This is very very very bad use of Word.

As to statement regarding other document userforms...I will reiterate something I mentioned previously.

What you posted does nothing for me. It tells me nothing. It tells me you had SOME sort of problem. With zero indication of what that problem(s), may have been.

So I am going to ignore it. It tells me nothing.
but I am having difficulty adapting it to a different document"Difficulty"????? What the heck does that mean Greg? Even posting that userform code tells me....nothing. Although my first question would be, are you sure the textbox controls on that userform DO have their names starting with "txt". because if they don't....then sure, you may have "difficulties".

[Greg]

funei, you said:

"OK, to bring data FROM a bookmark into the userform is easy. However, I question why you wish to do this. I hope - really hope - it is not your idea to use the same document over again, with new data, for a different person. Or new data for a different claim.

That would be very bad. Very bad. Do NOT do that."

My question is simple. Why is it very bad and why should I not do that?

Greg.

[fumei]

Because there is always the possibility of error. Always.

Use a template. A blank template that has all the code you need. All the bookmarks are there (but blank), all the code to fill them is there.

Have the userform come up on Document_New (when the template clones itself into a new document). There is no data in the new document. The users fills it in. Saves it. If you want, put a button on the toolbar to load the userform again, in order to make changes. Then the code in the userform (the new stuff I gave) will load the userform with the existing data. The user can make changes to THAT document.

If you want a new document, simply fire the template again. It will make a new document.

To use an old document to make a new document is bad practice. Yes, you can try and make sure that nothing from one claim makes it into the new claim. But that takes careful error trapping. Yes, it can be done, but there is no need to do so. Use a template to make new documents. You file should be a .DOT file, not a .DOC file. It should make .doc files.

This is what templates are for, and this is what Word is designed to do. It does it well. It is a bad idea to use existing data storage (a document) to make new ones. It makes it a requirement of the user to ensure the data for each claim is independent. Why do that? It should not be their responsibility. You are designing a process, and the responsibility to make sure that process is efficient and effective, is yours, as the designer. The best way to make sure each document is independent, is to make them separate. Each one generated from the same template - so the design structure is the same - but each one separate from any others. They are individual claims, make them so.

[mdmackillop]

...and with proper use of the template and userform, you can also automate your Save procedures to avoid losing documents due to typos, and find them when you need to edit them.

[fumei]

That too.

[Killian]

Set oBM = Nothing
' and we will have NO discussion on the
' explicit deletion of object variables
' thank you very much
End Sub Even though they dispose of themselves when they go out of scope or run out of reference pointers? :devil2:

[fumei]

Yes...precisely. Although...I am not sure what you mean by "run out" of reference pointers. Are reference pointers stored up like fuel in a tank, and when the tank is empty....POOF...the object disappears?

That was rhetorical...no answer is required.... or will be believed.