Solved: Userform run time error when opening document based on template

[Dr Dre]

Hi,

I have created a template with a userform that opens and gets relevant infomation off the user, and places it within certain fields under the document properties, and updates the document.

If I open this up as a template, the userform runs, takes the infomation, places it in the correct places and closes fine.

However if i double click the file, to open it as a document based on the template (which i want it to eventually do), the userform will crash when attempting to update any of the fields, giving the following "Run-time error '5981' Could not open macro storage."

I am using Word 97, on windows 2000 pro. I haven't tried it on any other systems yet.

I have attached the file here, and any help would be greatly appreciated, and hopefully its something simple.

I understand that the userform doesn't run automatically when its opened as a document, but i think this is relatively easy to sort out.

Cheers in advance.

Dre.

[sandam]

can't download the attachement right now but just a thought - what is the macro security level set to? You can also post your code in VBA tags [ v b a ] [ / v b a ] (just remove the spaces :))

[fumei]

1. I am getting an error:
Word cannot open this template "C:\..\RPS Energy Report Template.dot"

As this is NOT the name of the template file, I am confused. I have searched through the code, and searched through the document for this string "RPS Energy Report.dot" and can not find it...which seems logical as Word can not either. However, I can't find out WHY Word is looking for it...

2. I too am getting the error "can not find macro storage". I am assuming this is a reference to the RPS Energy Report template.

OK. First of all, please describe EXACTLY the process of your use of this file. Are you opening the .dot file? Are you using File > New and cloning the .DOT file. Please tell me it is the latter.

You wrote:
"If I open this up as a template, the userform runs, takes the infomation, places it in the correct places and closes fine.

However if i double click the file, to open it as a document based on the template (which i want it to eventually do), the userform will crash when attempting to update any of the fields, giving the following "Run-time error '5981' Could not open macro storage."

Please be specific about what you mean by "open as a template". If you open the file itself, you are NOT opening it as a template. You are opening it as a file.

Further, are you saying you are opening it as a template (opening the .DOT), inputing the stuff, then saving it as a .DOC? You then double click the .DOC?

If so, this is NOT the way to process a template.

Further some more. Your UserForm is OK, although you really should error trap the "x" Close button. And yes, using Custom Doc Properties can be used. But here are, I think better ways.

You also have the notice to the user regarding the user form at the beginning of the document. Are you aware that the document property for "Title" is this message? If you are going to use DocProperties it is a good idea tio have them all containing valid data.

Why do your fields in the table state page 1 of 13? When you update the field, it changes to 1.

Why do you have the table inside a Shape box?

Do NEVER unload the userForm!!!! You hide it with the Finish button...but you never actually close it. This means it is not released from memory.

The Finish tab states "all required information has now been entered." This is completely incorrect and untrue. I clicked the Finish tab, clicked the Finish button without putting anything at all....this hardly meets "all required information has now been entered." There is no erro trapping to make sure that..in fact...all required information, or ANY information has been entered. You should at least have checks to see if anything has actually been enterd.

I then entered complete gibberish into the data entry boxes. It happily took it - no error trapping on inputs either.

So. Again, are the users opening the .DOT file itself? If so, do not do this. Use the preper method for using .DOT files. Then, Document_New to open the UserForm.

Is one of your requirements that a document created from the template always open the UserForm when the file is opened?

[fumei]

Another thing (and I really hope you are not feeling I am trashing this thing, because I am not, I really want you to have a good design that functions the way YOU want it to), it is not a particularly good design to have an Update command for each multi-page. A Next maybe, so they can use it (or click the next tab) to go to the next input page.

This however, is based on whether you have a REQUIREMENT to have all input boxes completed. If you don't, then OK. But if you DO requie all input boxes to contain data, then the UserForm should have ONE command that:

1. checks input for valid input
2. inserts and updates the document.

[TonyJollans]

Hi Dr Dre,

Welcome to VBAX!

Gerry is right to surmise that Word's inability to find "RPS Energy Report Template.dot" is the root of your problem. You have a reference to this in your template; none of us have this and it would rather seem that you don't either. If it is something you think you need than make sure it is available, otherwise go to Tools > References (where you will probably find it listed as MISSING) and remove it.

With that minor problem out of the way you can get on and sort out the other issues. If you feel you need help with anything else do, please, post again.

[Dr Dre]

Thankyou for the replies

Tony:

I've done that tools>references thingy and its sorted the problem. Ta!

Gerry:

I'm always a fan of constructive critism, and you've given me some useful tips on how to make this userform better. Some answers to your quieries tho:

1. I tried to make the userform anonyous by removing all instances of "RPS", but seemed to have failed somewhere. D'oh. This also explains the page field showing 1/13, and the table in the shape box.

2. I am now opening the file the way you suggested, and it works fine, after i saved a copy of the template in the C:/program files/microsoft office/templates

Trapping the 'X' button worked a treat from somewhere else on this forum!

However one thing i am unsure of how to achieve, is creating an error message box that appears when someone hits the finish button (when they haven't entered all the infomation), prompting them to fill-in any boxes that they left blank. I will want them to provide all the infomation the userform asks for. What would be the code for this, and can it be set to the date box so that rather than giving a runtime error as i think it does now, it just prompts the user to re-enter the date in a valid format?

I took your advice, and only updated the fields when they click the 'finish' button, as it makes more sense, and works quicker... (the office is full of 600Mhz PC's).

And finally what do you mean by "Do NEVER unload the userForm!!!! You hide it with the Finish button...but you never actually close it. This means it is not released from memory." Do you mean to use the 'unload me' command, rather than the 'userform.hide' command? If so i think i've now done that.

And thanks again.

Dre

[sandam]

I think I can clrify a little of the whys. When you unload a userform, VBA realeases all the variables from memory - you lose your data. However there may be times when swithcing between froms that you need to maintain data - so use hide and show. You can even use Me.Hide to hide a form. Just remeber to hide the first form before showing the second one.

As for the trapping - In your OK button, use you validation of the user inputs as conditions and if those conditions are met then the user progresses other wise you pop up a msgbox or something of the like to let them know that they have missed information. e.g.



Private Sub CommandButton1_Click()

If len(TextBox1.Text) > 0 And Len(TextBox2.Text) > 0 Then

'do what happens if form is correct

Else

MsgBox "Sorry, information missing, Please input _

all fields.",vbCritical+vbOkOnly,"ERROR"

End If

End Sub

[fumei]

sandam is correct in the principle of error trapping with the IF statement "len(Textbox1.Text) > 0.

What that means, though, literally, is that:

IF the length of the text string in Textbox1 is longer than 0 then proceed.

I will tell you right off...full detailed error trapping is THE most difficult and tedious part of coding. I once had a little VBA app that took about 300 lines of code to run the whole thing....but 3,000 lines of code to do rock solid error trapping. It required very specific parsing and testing of the user input.

In sandam's example "ckfht789acs" would be acceptable as the length is > 0.

If you want to check if it is blank you could use IF Textbox1.Text = "".

sandam is also correct RE: unloading from memory. Hiding the userform allows the data to persist. That is fine, if you are going to be programatically bringing the userform back. However, you have no code to do so. Therefore it seems to me, that if the purpose is to "finish", then you are done...and the userform should be truly closed. And the memory released.

[MOS MASTER]

Hi Dre, :D

There are many ways of validating Userform fields results.

To check fields for specific results use the events of the control itself!
For your date question you can use:
Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(Me.txtDate.Text) Then
Cancel = True
Beep
MsgBox "Field result is not a valid date", vbExclamation, "Date Check"
End If
End Sub

2 more for validation of numeric Entry's and Data Entry's are 2 different events...try them all out to see what there doing)
Private Sub txtEmpty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Me.txtEmpty.Text) = 0 Then
Cancel = True
Beep
MsgBox "Field result contains no valid data", vbExclamation, "Data Check"
End If
End Sub
Private Sub txtNumber_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(Me.txtNumber.Text) Then
Cancel = True
Beep
MsgBox "Field result is not a valid Number", vbExclamation, "Numeric Check"
End If
End Sub

You've talked about diabeling of the X-button (QueryClose). Don't know what you're using but I simply use:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
Me.Caption = "Use Ok button or DIE!"
End If
End Sub


All these events are very nifty and serve good purpose but there's a cave-at! What if a control never receives the focus and data is never entered....Yepz you've guest it all those nifty events will never trigger.

This is why you always need a simple function to check those fields that you really need filled out, if they contain any data!
This Function should return boolean and if field has no value exit and setfocus to the field so a person can fill in the blanks.

This Function should be under the Ok button of the Form before data gets written to the document. (or else..)

This is the function I use (Makes use of Tag property of control which is set to "Validate")

Private Function IsItEmpty() As Boolean
Dim oCtl As MSForms.Control
IsItEmpty = True
For Each oCtl In frmCheck.Controls

If oCtl.Visible = True And oCtl.Tag = "Validate" Then

If Trim(frmCheck.Controls(oCtl.Name).Text) = "" Then

Beep
MsgBox "This field is required", vbExclamation, "Check Form"

IsItEmpty = False
oCtl.SetFocus
Exit Function
End If

End If

Next oCtl

End Function


The Call under the Ok button should be something like:
Private Sub cmdOk_Click()

If IsItEmpty = False Then Exit Sub
'Else do your thing!
Unload Me

End Sub


This is quit a bit off information so I've made a little testfile for you so you can play with it and see how this validation works.

Enjoy! :whistle:

[Dr Dre]

Right Thanks for all your help, and i have my userform up and running and functioning great (well for my standards anyway)

There is one niggling little problem that i cannot solve if you can help.

The users will be entering the date in the uk style of dd/mm/yy, and when they do this, the userform interprets it as american style mm/dd/yy, which leads to the wrong date being shown in my report.

What is the best way to change this, as what would be best would be some code that treats the whole macro in the uk style, rather than having to tell the userform to swap the days and month characters around, as i reckon that has the potential to create more bugs.

Cheers again, and i will mark this solved once i've got the solution. Ta!

[sandam]

format the text from when the users enter the date with this and then insert it into the report :)



Textbox1.text = cstr(format(textbox1.text,#dd/MM/yyyy#))

[Dr Dre]

nope. I can't get that to work.

with the '#' i get an error message saying that the '#' is unexpected,

if i leave it blank i get a runtime error 6 overflow message, and if i replace it with "", it gets ignored.

I've tried that command in a variety of places in my code, all with the same effect.

One thing i have done is 'dim textbox1 as date' earlier on, so can this be part of the problem?

Also i'm not sure i explained myself properly before.

I expect the user to enter the date in one of the following formats:

dd/mm/yy
dd/mm/yyyy
dd month year
month dd year, or
month year

And i expect vba to understand this.

I keep finding that vba will keep jumble up the order in which ever way it thinks it is right. for example if i put in march 2005, vba thinks it is 3rd of january 2005. Or if i enter 32/05/05, vba will think it is the 5th of May 1932, but i would rather get an error message.

I'm sure other people creating userforms would have come across this problem, so there should be a relatively simple way of solving it.

[TonyJollans]

There can be odd problems with dates in textboxes but you should be alright if you use CDate to convert the text to a date. Or is that not working for you?

[sandam]

THanks to Tonys little pointer (CDate) try this - it should work, and there is no need to dim your text boxes to anything - that will cause errors



Function ConvertToDateAndFormat(DateText as String) As String
Dim Temp As Date

Temp = CDate(DateText)
ConvertToDateAndFormat = Format(Temp, "dd/MM/yyyy")
End Function

Sub ConvertIt
Dim UserDateString As String
UserDateString = ConvertToDateAndFormat(textbox1.text)
End Sub

[MOS MASTER]

Hi, :D

Another posibility is to use a Calendar Control on you're Form.

Then the user only has to press a button to get the required date and you can Control the output much better. This will be the most solid sollution, manual input will always trigger problems!

If you're going with Andy's method I would sugest you Check for existens of a date first like:
Private Sub txtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(Me.txtDate.Text) Then
Cancel = True
Beep
MsgBox "Field result is not a valid date", vbExclamation, "Date Check"
Else
Me.txtDate.Text = Format(CDate(Me.txtDate.Text), "dd/MM/yyyy")
End If
End Sub


Enjoy! :whistle:

[MOS MASTER]

Hi Dre, :D

Have you found what you've been looking for? :whistle:

[Dr Dre]

I think the infomation i need is there, haven't had a chance to work on it yet, but it looks like what i needed.

Thanks very much.

Dre

[MOS MASTER]

Glad we could be of help! :yes