Input Box Promt

[Guest]

I have a macro in which I use a input box which enables user to input the
file name and saves the file.

Can i restrict the Input box so that the user can put in only specific names.

Aziz

 

[Guest]

In a blank worksheet, enter the valid names in a column, select the column
and name the selected range ValidNames.

The following code will validate the file name entered in the input box
against the list of names in the range named ValidNames

Dim wb As Workbook
Dim rng As Range
Dim strFileName As String
Dim strValidName As String
Dim i As Integer
Dim blnIsValid As Boolean

Set wb = ActiveWorkbook
Set rng = Range("ValidNames")

strFileName = InputBox("Enter the name of the file: ")

For i = 1 To rng.Cells.Count
strValidName = rng.Cells(i)
If strValidName = strFileName Then
blnIsValid = True
Exit For
End If
Next i

If Not blnIsValid Then MsgBox strFileName & _
" is an invalid file name."

 

[Ron de Bruin]

Why not use GetSaveAsFilename
See alo the VBA help

This example will save a copy of the activeworkbook
You can test fname for your criteria

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")
'On Error Resume Next
If fname = False Then Exit Sub
If Dir(fname) <> "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub

 

[Guest]

RON / KEVIN,

The solution given by both of you is good but it wouldnt work out in my case
as there are too many files involved and i dont really want to make another
tabs.

is it possible that we give the file names in the macro code itself..... so
that we dont really have to make any new files or new sheets.....

so there are 12-13 diff names which could ideally be given so if we write
all these names somewhere in the code it would be good for me....

Thanks a lot.... I know i didnt explain my self properly last time around.....

Aziz

 

[Ron de Bruin]

Try this

Sub Test()
Dim Filename As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Filename = Array("workbook1.xls", "workbook2.xls", "workbook3.xls", "workbook4.xls")

For N = LBound(Filename) To UBound(Filename)
Wb.SaveCopyAs "C:\Data\" & Filename(N)
Next
End Sub

Add your file names in the array Filename
It save the files in C:\Data\

 

[Guest]

Ron,

Thanks for this. I guess i have once again not put myself properly. Below is
the code that i am using

s1 = "ABC - "
s2 = InputBox(Prompt:="Enter the Date of the Report (MMDD)", _
Title:="Enter Date")
s3 = s1 & s2
ActiveWorkbook.SaveAs Workbooks("Input File").Path & "\" & s3

Now when the user will be prompted to enter a 4 digit number..... i want to
have control on this digits.... i.e. either 0131 or 0228 or 0331 and so
on........

sorry to have not explained my self properly......

Aziz