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? Please advise.
Thanks.