Input mask for "Save File As" input box. 3

[jdttek]

In Excel, I have writen macro to prompt user for new file name at close of file. "Save File As" macro uses input box to prompt user for new file name. How do I Create a mask that user must follow every time to assure file names are saved consistently (ie, want "FileName_DD/MM/YY.xls&quot

OR
How do I just add todays date to base file name each time it is saved?

Thanks for any suggestions
JDTtek

 

[uberpudge]

Since I don't think VBA has input mask function for an input box, I'd do it like this:


Public Sub TestInputMask()
Dim strMsgPrompt As String
Dim strFileName As String
Dim strXLS As String
Dim strUnderscore As String
Dim strDash1 As String
Dim strDash2 As String
Dim strExtension As String
Dim strMonth As String
Dim strDay As String
Dim strYear As String
Dim lngPos As Long

Dim booMaskConditionsMet As Boolean

booMaskConditionsMet = False
strXLS = ".xls"

Do Until booMaskConditionsMet
strFileName = Interaction.InputBox(strMsgPrompt, "Save File As&quot
If strFileName <> &quot;&quot; And Strings.Len(strFileName) >= 13 Then
lngPos = Strings.Len(strFileName) - 13 'Strings.Len(&quot;_mm-dd-yy.xls&quot
strUnderscore = Strings.Mid(strFileName, lngPos + 1, 1)

lngPos = Strings.Len(strFileName) - 12 'Strings.Len(&quot;_mm-dd-yy.xls&quot
strMonth = Strings.Mid(strFileName, lngPos + 1, 2)

lngPos = Strings.Len(strFileName) - 10 'Strings.Len(&quot;_mm-dd-yy.xls&quot
strDash1 = Strings.Mid(strFileName, lngPos + 1, 1)

lngPos = Strings.Len(strFileName) - 9 'Strings.Len(&quot;_mm-dd-yy.xls&quot
strDay = Strings.Mid(strFileName, lngPos + 1, 2)

lngPos = Strings.Len(strFileName) - 7 'Strings.Len(&quot;_mm-dd-yy.xls&quot
strDash2 = Strings.Mid(strFileName, lngPos + 1, 1)

lngPos = Strings.Len(strFileName) - 6 'Strings.Len(&quot;_mm-dd-yy.xls&quot
strYear = Strings.Mid(strFileName, lngPos + 1, 2)

strExtension = Strings.Right(strFileName, 4)

If strUnderscore = &quot;_&quot; And Information.IsNumeric(strMonth) And _
strDash1 = &quot;-&quot; And Information.IsNumeric(strDay) And _
strDash2 = &quot;-&quot; And Information.IsNumeric(strYear) And _
strExtension = strXLS Then
booMaskConditionsMet = True
Else
strMsgPrompt = &quot;The file name must end with the following format:&quot; + vbCrLf + _
Strings.Chr(34) + &quot;_MM-DD-YY.xls&quot; + Strings.Chr(34) + &quot;.&quot; + vbCrLf + _
&quot;Please try your entry again.&quot;
End If
Else
strMsgPrompt = &quot;The file name must end with the following format:&quot; + vbCrLf + _
Strings.Chr(34) + &quot;_MM-DD-YY.xls&quot; + Strings.Chr(34) + &quot;.&quot; + vbCrLf + _
&quot;Please try your entry again.&quot;
End If
Loop

Interaction.MsgBox &quot;The following file name was judged as correct: &quot; + strFileName
End Sub

The sample above worked when I tested it.

Hope this helps,
Pete