frmXXXXXX.show command

[AnchorLoans]

Hi Everyone,

First post here. I hope someone can help me out.

Situation:

I created a Word Template (.dot) containing a VBA script that displays a Userform with fields the user can fill out and it will propogate into the template. I can get this to work on my computer only if I open the file using these steps: File->Open->xxxx.dot. If it open it this way, the Userform is displayed as soon as I open the template, which is what I want. But if I try opening the file through the Template Wizard, the Userform will not display. If I save the file on the network share and then open it from there, the Userform is not displayed. I hope I'm making sense here.

Here is a copy of the code:



Option Explicit

' --------------------------------------------------------------------------------
'
' Document: Term Sheet
' Template: Term Sheet - Texas.dot
'
' Created : By Steve Pollack 12/1/06
'
' --------------------------------------------------------------------------------


Private Sub Document_Open()

frmTermSheet.Show
If frmTermSheet.Tag = "Cancel" Then
GoTo Bye
End If

' Date
ActiveDocument.Bookmarks("Date").Select
Selection.TypeText Format(Now, "mmmm d, yyyy")

With frmTermSheet

'Borrower / Property / Lender Info
ActiveDocument.Bookmarks("Borrower").Select
Selection.TypeText .txtBorrower
ActiveDocument.Bookmarks("Lender").Select
Selection.TypeText .cboLender
ActiveDocument.Bookmarks("Guarantor").Select
Selection.TypeText .txtGuarantor
ActiveDocument.Bookmarks("PropAddress").Select
Selection.TypeText .txtProperty

'Loan Info
ActiveDocument.Bookmarks("LoanAmt").Select
Selection.TypeText .txtLoanAmt
ActiveDocument.Bookmarks("Points").Select
Selection.TypeText .txtPoints
ActiveDocument.Bookmarks("UWFee").Select
Selection.TypeText Format(.txtUWFee, "##,##0")
ActiveDocument.Bookmarks("AppFee").Select
Selection.TypeText Format(.txtAppFee, "##,##0")
ActiveDocument.Bookmarks("DocFee").Select
Selection.TypeText Format(.txtDocFee, "##,##0")
ActiveDocument.Bookmarks("Terms").Select
Selection.TypeText .txtTerms & " months"
ActiveDocument.Bookmarks("Maturity").Select
If Day(Now) = 1 Then
Selection.TypeText Format(DateAdd("m", .txtTerms, Now), "mmmm 1, yyyy")
Else
Selection.TypeText Format(DateAdd("m", .txtTerms + 1, Now), "mmmm 1, yyyy")
End If
End With

PrintDoc:
' Document is done, print it
If frmTermSheet.Tag = "Print" Then
Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="1-2", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False
' ActiveDocument.Undo 50
' ActiveDocument.Close
ElseIf frmTermSheet.Tag = "Preview" Then
Application.ActiveDocument.PrintPreview
End If

Bye:
If frmTermSheet.Tag = "Cancel" Then
ThisDocument.Close savechanges:=False
End If
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
'' ''
'' CREATE FUNCTION - Converts a Number to its Spelled out Equivalent ''
'' ''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''

Public Function ConvertNumbertoSpelling(InputNumber)
' 100 to 999
If Len(InputNumber) = 6 Then
Hundreds:
If Mid(Right(InputNumber, 6), 1, 1) = "0" Then
If Mid(Right(InputNumber, 5), 1, 1) = "0" Then
If Mid(Right(InputNumber, 4), 1, 1) = "0" Then
Selection.TypeText "dollars"
GoTo Finish
Else
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 4), 1, 1)) & " dollars"
GoTo Finish
End If
ElseIf Mid(Right(InputNumber, 5), 1, 1) = "1" Then
Selection.TypeText TentoNineteen(Mid(Right(InputNumber, 5), 1, 2)) & " dollars"
GoTo Finish
Else: Selection.TypeText TwentytoNinety(Mid(Right(InputNumber, 5), 1, 1)) & " "
If Mid(Right(InputNumber, 4), 1, 1) = "0" Then
Selection.TypeText "dollars"
GoTo Finish
Else
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 4), 1, 1)) & " dollars"
GoTo Finish
End If
End If
Else
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 6), 1, 1))
Selection.TypeText " hundred "
If Mid(Right(InputNumber, 5), 1, 1) = "0" Then
If Mid(Right(InputNumber, 4), 1, 1) = "0" Then
Selection.TypeText "dollars"
GoTo Finish
Else
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 4), 1, 1)) & " dollars"
GoTo Finish
End If
ElseIf Mid(Right(InputNumber, 5), 1, 1) = "1" Then
Selection.TypeText TentoNineteen(Mid(Right(InputNumber, 5), 1, 2)) & " dollars"
GoTo Finish
Else: Selection.TypeText TwentytoNinety(Mid(Right(InputNumber, 5), 1, 1)) & " "
If Mid(Right(InputNumber, 4), 1, 1) = "0" Then
Selection.TypeText "dollars"
GoTo Finish
Else
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 4), 1, 1)) & " dollars"
GoTo Finish
End If
End If
End If
End If

' 1,000 to 9,999
If Len(InputNumber) = 8 Then
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 8), 1, 1))
Selection.TypeText " thousand "
GoTo Hundreds
End If

' 10,000 to 99,999
If Len(InputNumber) = 9 Then
TenThousands:
If Mid(Right(InputNumber, 10), 1, 1) = "0" And Mid(Right(InputNumber, 9), 1, 1) = "0" And Mid(Right(InputNumber, 8), 1, 1) = "0" Then
GoTo Hundreds
End If
If Mid(Right(InputNumber, 9), 1, 1) = "0" Then
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 8), 1, 1))
If Mid(Right(InputNumber, 8), 1, 1) = "0" Then
Selection.TypeText "thousand "
Else: Selection.TypeText " thousand "
End If
GoTo Hundreds
ElseIf Mid(Right(InputNumber, 9), 1, 1) = "1" Then
Selection.TypeText TentoNineteen(Mid(Right(InputNumber, 9), 1, 2))
Selection.TypeText " thousand "
GoTo Hundreds
Else
Selection.TypeText TwentytoNinety(Mid(Right(InputNumber, 9), 1, 1)) & " "
If Mid(Right(InputNumber, 8), 1, 1) = "0" Then
Selection.TypeText "thousand "
GoTo Hundreds
Else
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 8), 1, 1)) & " thousand "
GoTo Hundreds
End If
End If
End If

' 100,000 to 999,999
If Len(InputNumber) = 10 Then
HundredThousands:
If Mid(Right(InputNumber, 10), 1, 1) = "0" Then
GoTo TenThousands
Else
Selection.TypeText OnetoNine(Mid(Right(InputNumber, 10), 1, 1))
Selection.TypeText " hundred "
GoTo TenThousands
End If
End If

' 1,000,000 to 9,999,999
If Len(InputNumber) = 12 Then
Selection.TypeText OnetoNine(Left(InputNumber, 1))
Selection.TypeText " million "
GoTo HundredThousands
End If

Finish:
End Function

Function OnetoNine(InputNumber)
If InputNumber = "1" Then
OnetoNine = "one"
ElseIf InputNumber = "2" Then
OnetoNine = "two"
ElseIf InputNumber = "3" Then
OnetoNine = "three"
ElseIf InputNumber = "4" Then
OnetoNine = "four"
ElseIf InputNumber = "5" Then
OnetoNine = "five"
ElseIf InputNumber = "6" Then
OnetoNine = "six"
ElseIf InputNumber = "7" Then
OnetoNine = "seven"
ElseIf InputNumber = "8" Then
OnetoNine = "eight"
ElseIf InputNumber = "9" Then
OnetoNine = "nine"
End If
End Function

Function TentoNineteen(InputNumber)
If InputNumber = "10" Then
TentoNineteen = "ten"
ElseIf InputNumber = "11" Then
TentoNineteen = "eleven"
ElseIf InputNumber = "12" Then
TentoNineteen = "twelve"
ElseIf InputNumber = "13" Then
TentoNineteen = "thirteen"
ElseIf InputNumber = "14" Then
TentoNineteen = "fourteen"
ElseIf InputNumber = "15" Then
TentoNineteen = "fifteen"
ElseIf InputNumber = "16" Then
TentoNineteen = "sixteen"
ElseIf InputNumber = "17" Then
TentoNineteen = "seventeen"
ElseIf InputNumber = "18" Then
TentoNineteen = "eighteen"
ElseIf InputNumber = "19" Then
TentoNineteen = "nineteen"
End If
End Function

Function TwentytoNinety(InputNumber)
If InputNumber = "2" Then
TwentytoNinety = "twenty"
ElseIf InputNumber = "3" Then
TwentytoNinety = "thirty"
ElseIf InputNumber = "4" Then
TwentytoNinety = "forty"
ElseIf InputNumber = "5" Then
TwentytoNinety = "fifty"
ElseIf InputNumber = "6" Then
TwentytoNinety = "sixty"
ElseIf InputNumber = "7" Then
TwentytoNinety = "seventy"
ElseIf InputNumber = "8" Then
TwentytoNinety = "eighty"
ElseIf InputNumber = "9" Then
TwentytoNinety = "ninety"
End If
End Function
' *************************************************************************** *******
' BM: Replaces bookmark BookmarkName with value BookmarkValue and adds DocVariable
' for the same name so that it can be referenced somewhere else in the document.
'
' The BM() procedure works as follows:
'
' 1. If a DataType is passed then force the BookmarkValue to that type.
' 2. If a Mask is passed...use it.
' 3. If the Mask is missing then use the BookmarkValue variable type default mask.
' 4. If the Mask is a null string ("") then return BookmarkValue as is.
'
' Examples:
' BM("Amount") = 1000@ Returns: $1,000.00
' BM("Date") = #1/1/99# Returns: 01/01/1999
' BM("Amount","") = 1000@ Returns: 1000
' BM("Amount","#,##0.0") = 1000@ Returns: 1,000.0
' BM("Amount","Currency",vbCurrency) = Null Returns: $0.00
'
' *************************************************************************** *******
Public Property Let BM(ByVal BookmarkName As String, Optional ByVal Mask, Optional DataType, ByVal BookmarkValue As Variant)
Dim Msg As String

On Error GoTo BM_Error
With ActiveDocument.Bookmarks
.Item(BookmarkName).Select
Selection.Text = Format(BookmarkValue, Mask, DataType)
.Add BookmarkName, Selection.Range
End With

BM_Exit:
Exit Property

BM_Error:
Msg = "An error occurred printing Bookmark: '" & BookmarkName & "'." & vbCrLf
Msg = Msg & Err.Description & vbCrLf & vbCrLf
Msg = Msg & "Please verify that you typed the correct Bookmark name."
MsgBox Msg, vbExclamation + vbSystemModal, "The Mortgage Office"
Resume BM_Exit
End Property

How do I get this Userform to open up everytime? Is there something wrong or missing in this code? :dunno Please advise.

Thanks.

[lucas]

Hi Anchorloans,

I can get this to work on my computer only if I open the file using these steps: File->Open->xxxx.dot. If it open it this way, the Userform is displayed as soon as I open the template, which is what I want.
Which is exactly what you do not want.....someone will correct me if I'm out of school here but I think when you open the file using file>open>xxx.dot that you are actually opening the template and so the macro runs on open just as you have instructed it to.

What you want(I think)is for the macro to run when you create a file using the template. In other words when you open word and go to file-new-Templates on my computer and use your template from there. So instead of Document open you should be putting your code in


Private Sub Document_New()
frmTermSheet.Show
End Sub

I just used the formshow part to test with. You will want all of your code with it...
I tried it and saved it as a dot and it worked that way....

[fumei]

Yes, please clarify what it is you are trying to do.

A template should NEVER be opened by the user. The template file itself should be only opened by the developer of the template.

A template (.DOT) file should be cloned - that is, used to created a new cloned copy of it. This is done by File > New, and selecting the template. A new cloned copy is created. The original .DOT file is not opened.

To have a userform open when a clone is created use the Document_New event in the template (.DOT) file. When a new document is cloned Document_New is fired.

If I may make some suggestions:

1. Use objects.'Borrower / Property / Lender Info
ActiveDocument.Bookmarks("Borrower").Select
Selection.TypeText .txtBorrower
ActiveDocument.Bookmarks("Lender").Select
Selection.TypeText .cboLender
ActiveDocument.Bookmarks("Guarantor").Select
Selection.TypeText .txtGuarantor
ActiveDocument.Bookmarks("PropAddress").Select
Selection.TypeText .txtProperty

Here you are selecting each bookmarks by way of its full, rather long, name, and then typingtext.Dim oBM As Word.Bookmarks
Set oBM = ActiveDocument.Bookmarks
' now you can use oBM
'Borrower / Property / Lender Info
oBM("Borrower").Select
Selection.TypeText .txtBorrower
oBM("Lender").Select
Selection.TypeText .cboLender
oBM("Guarantor").Select
Selection.TypeText .txtGuarantor
oBM("PropAddress").Select
Selection.TypeText .txtPropertyLess typing, yes?

Better yet, though, you do not need to do any selecting, and typing at all.Dim oBM As Word.Bookmarks
Set oBM = ActiveDocument.Bookmarks
oBM("Borrower").Range.Text = txtBorrower
oBM("Lender").Range.Text =cboLender
oBM("Guarantor").Range.Text = txtGuarantor
oBM("PropAddress").Range.Text = txtProperty

Using the Selection object is a very inefficient way of doing things. Selection makes action with the actual user interface. The cursor, or selection, will actually move and select all those items and do its thing.

By using the Range, the bookmarks will simply change to the given values.

Generally speaking, and in most cases, it is not needed to do a bunch of .Typetext instructions to the Selection.

There ia a whole lot of stuff with numbrs and strings. I am not fully following it, my impression is that this could be done (probably) a lot easier. I could be wrong, as I said, I have not gone through the whole thing - and I am not sure exactly what the source data is - but whenever I see a huge mess of If, ElseIf statements I am inclined to think there is likely an easier, cleaner, method.

Could you give a few examples of what this code is actioning on?

[AnchorLoans]

:hi: Hi Fumei and Lucas,

First of all, I want to thank you for taking the time to help me out. I definitely already feel welcomed. Thanks again. Now onto the situation.

Unfortunately, entering this event did not work: :dunno


VBA:


Private Sub Document_New()
frmTermSheet.Show
End Sub

In fact, after I did this, it wouldn't even open at all even on the template itself when I was able to at least open it from my end. :help

Basically, this Userform was created for our employees to enter information into the fields on the Userform and it will fill out the Word document for them.

I will attach the original file so you can get a better understanding of what it's suppose to do.

Both of you are right on the spot. I want the macros to run when it's opened by the user. I want the Userform to display as soon as the document is opened. : pray2:

[lucas]

If I double click on the attached file it opens a document 1 based on the template and the form opens. This shows how to use document new. Hope you can see the difference. Let us know.

[lucas]

I tried to run your form once but there are missing bookmarks......

[AnchorLoans]

Lucas,

Is there something I am doing wrong here? You state that the form opens for you but it doesn't. What am I doing wrong? Please help me fix this error so in the future I can avoid this problem. As you can see I am a newbie at this and any guidance would be greatly appreciated. I just want the form to open to all users. This has to be a simple process I am assuming. Thank you.

[lucas]

When you download the file from post #5 please give exact steps of what you do with it please, ie:
1 I locate the file on my hard drive
2 I extract the Term Sheet2.dot
3 I double click on the Term Sheet2.dot (I suggest this as a starting place)
4 or I put it in my Word documents folder and use File-new in Word
5 any more details you can think of that might help us understand.
6 exactly what happens at each step please

[AnchorLoans]

Sorry. Yes, step 3 is step 1. You open it as a regular document as in File-->New--> then the file.

[lucas]

I'm confused....step 3 is double clicking on the .dot file
You've got to give a little more info. I listed 6 steps and you returned one confusing answer.......If you need help with this then your going to have to put a little more into it....can't read your mind. Besides I'm pretty nice about things and if we don't get this worked out before Gerry comes along.......well, I'll just let you work it out with him if you can.

Seriously if you want help with this your going to have to help us help you...

[AnchorLoans]

Hi Lucas,

Sorry. I'm not trying to be difficult. Here is what I do with Post #5. I saved your .zip file onto my HD. I then extract the .zip file to get a .dot file onto my desktop. I double click the extracted .dot file and I do not get any Useform displayed. The only thing that is displayed is the Word document itself with "$borrower" , etc. (the bookmarks where the fields are suppose to propogage) without the form being displayed. Tell me if this isn't enough info. I will gladly help you, help me.

Thanks,

David

[mdmackillop]

Hi David,
Check that your Macro Seurity level is set to Medium (not High)
Regards
MD

[fumei]

Hi there.

Ok. Let's walk this through here, shall we? I hate any icons on my desktop, but for the sake of duplicating what you say you have done, I

1. saved the zip file to my HD
2. I extracted the contents (the .dot file) to my desktop (bleech).
3. I double clicked the icon for the .dot file.

The userform is displayed. Which is correct - the userform Show instruction is in the Document_New event.

If I right click the icon on the desktop, and select Open, the userform will NOT display - as the Show instruction is in Document_New, not Document_Open.

Question, when you do whatever it is you are doing, what shows on the title bar in Word? Is it Document 1 (or whatever number). Or does it show Term Sheet2.dot?

If it shows Document 1 - then you called the template and it clones itself, which is what templates are supposed to do. If you did, then the userform displays - it is in Document_New.

If it did not display the userform, then you have opened the file itself, and you are not doing what you say you are doing.

File > New will ONLY find the .dot file if it is somewhere in the file locations known to Word. The desktop is not one of them.

[AnchorLoans]

Fumei,

I finally understand what you've been saying. :bow: You are a genuis. Thanks for putting up with my stupidity. :banghead: Haha.

Thank you again :beerchug:,

David

[AnchorLoans]

Maybe you can help me out on this situation. I'm not sure what this user is trying to tell me:

"When I open the attachment, it performs as you describe.

However, when I then do a Save As and save it as a template and then attempt to re-open it, the Userform does not appear."

I noticed that when I do a Save As like this user just did, it creates a .doc and .dot file with the same name. When I open the .doc file from File->New the Userform opens but when I open the .dot file the Userform does not.

[fumei]

There is serious confusion here regarding documents (.DOC files), and templates (.DOT files).

Let's walk it through again.

ALL Word files have a Document_Open, and a Document_New. Both documents (.DOC), and templates (.DOT).

Now the Document_New event in a document is just plain silly to me, and I never use it. You can not really make a new document from a document, so it is a useless event IMO.

However, the Document_New event in a template is VERY important. It is what fires when a template is called - NOT opened, but called. It is the event that fires when a template makes a new document.

Double clicking a .DOT file, creates a new document. Document_New fires.

Document_Open - as stated - exists in BOTH the template file, AND the document file.

When a template is cloned, its Document_New fires. Have the userform.show in there. Make a new document - the userform shows.

Save the document. Open the document...no userform.

So, you have to put the userform.show in BOTH Document_New, AND Document_Open of the template.

_New to display the userform when the new document is created.

_Open to display when the new document is saved, then opened.

Document_Open in the template will fire for a document created from the template, when it is opened.

However, the ORDER is:

1. Document_Open (in the document)
2. Document_Open (in the template)

If there is a Document_Open in the document, it will supercede any Document_open in the template. The Document_Open in the template will NOT fire.

So, back to your problem. Put a userform.show in both Document_New, AND Document_Open in the template. The userform will display when a new document is created, and it will display when a document (cloned from the template) is saved and re-opened.


However, when I then do a Save As and save it as a template and then attempt to re-open it, the Userform does not appear."

1. WHY are you doing a SaveAs and saving it as a template???? This is NOT what you do with cloned templates.

2. No, the userform will not display. If you look in the VBAProject there will not BE a userform.

The first template has the userform. It displays on _New. If it also has .Show in _Open, then the document will display the userform on open. It will be making a call to the template - which has the userform. The document does NOT have the userform. The document does NOT have the userform!

If you now save the document - as a template (DON"T DO THAT!), then there IS no userform to display. It doesn't have the userform. The attached template has the userform. So if you save it as a template...there ain't no userform to display.


When I open the .doc file from File->New the Userform opens but when I open the .dot file the Userform does not.
Explain this. Using File > New to open an existing file does not make sense. But in any case, the above still holds.

If you open a document, ANY code or userforms in the attached template are available....the userform displays.

Template save as Document save as Template

Bad bad bad. Well, it certainly can be done, but it is a incorrect use of templates.

Template - contains code, userforms.

cloned to new document....

New document does NOT have code or userforms. It uses the code and userform of the template.

new document saved as template (DON'T DO THAT!).....this new template has...no code or userforms, as the document it was saved from has none.

Just to repeat:
When I open the .doc file from File->New the Userform opens but when I open the .dot file the Userform does not.No, it does not. Because there is NO useform to display.

I know this sounds all weird and confusing, but it is not really. Once you get it - how and what templates are for - then it is easy.

Bottom line? Your user should not be saving a document cloned from a template, as another template. If they are, then you need to find out why. There should be absolutely no need for them to do this. If the existing template is not meeting their needs, then THAT template needs to be adjusted so it does meet those needs.

[AnchorLoans]

Haha okay. Thanks for the explaination. I do get it. It's the end user that doesn't get it. I will try explaining it to them using your post. Hopefully I can get across them as you have done for me. I will let you know what happens. Thanks again. :banghead: <---You and Me :dunno<---End User.

[fumei]

Oh we all have the same issue with end users.

Seriously though. If you are responsible for the template, then you need to find out why they are saving any documents created as a template again.

[fumei]

Oh, and lastly, if you ARE the developer of the template, you may want to consider doing the SaveAs yourself. MAKE the new document be saved as a document.