InputBox

[ratman]

I have many .xls files in same directory.&nbsp;&nbsp;Each filename is named mmddyy.xls&nbsp;&nbsp;&nbsp;&nbsp;<br>example<br>c:\My Excel File\Production Files\0705.00.xls<br>or as referenced in the macro<br>C:\My Excel Files\Production Files\&quot; & myFile<br><br>I now have an InputBox that auto_opens and user requests to readonly one of the files by entering <br>mmddyy&nbsp;&nbsp;&nbsp;<br>for example:&nbsp;&nbsp;070500<br><br>Sub button0_onclick()<br>&nbsp;&nbsp;Dim returntext<br><br>&nbsp;&nbsp;&nbsp;returntext = InputBox(&quot;Enter the month day year with NO SPACES.....example: you type&nbsp;&nbsp;&nbsp;080400&nbsp;&nbsp;&nbsp;to view the file for 8/4/00&quot<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;myFile = Format(thisdate, &quot;mm&quot & Format(thisdate, &quot;dd&quot & Format(thisdate, &quot;yy&quot & returntext & &quot;.xls&quot;<br><br>Workbooks.Open Filename:=&quot;C:\My Excel Files\Production Files\&quot; & myFile<br><br><br>as long as the file exists it opens <br>I now need to validate or error control the inputbox with an error message &quot;date format incorrect or file not found&quot;&nbsp;&nbsp;<br>to loop until inputboxtext = true or application.quit<br><br>also need to have inputbox open again after activeworkbook minimized or closed.<br>

 

[dsi]

You may be looking for something like this:<br><br>Private Sub Autpen()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim sMsg As String, sFile As String, sPath As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;sMsg = &quot;Enter the month day and year with NO SPACES&quot; & vbCrLf & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;Example: 080400 to view the file for 8/4/00&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;sPath = &quot;C:\My Excel Files\Production Files\&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Do While sFile Like &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sFile = InputBox(sFile, &quot;Enter the Date&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If sFile Like &quot;&quot; Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'User hit the cancel button, or did not enter text<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If MsgBox(&quot;Are you sure you want to quit?&quot;, vbYesNo, &quot;Quit Application?&quot = vbYes Then End<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else&nbsp;&nbsp;&nbsp;&nbsp;'entered something<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Dir(sPath & sFile * &quot;.xls&quot = &quot;&quot; Then&nbsp;&nbsp;'does not exist<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Invalid Entry&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else&nbsp;&nbsp;&nbsp;&nbsp;'exists<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Do<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;Workbooks.Open FileName:=sPath & sFile & &quot;.xls&quot;<br>End Sub<br><br>

 

[ratman]

thanks tclere.....I will try this at work tomorrow.<br><br>Ratman