Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Promt
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Promt
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." -- Kevin Backmann "Aziz Ahmedabadwala" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Promt
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 -- Regards Ron De Bruin http://www.rondebruin.nl "Aziz Ahmedabadwala" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Promt
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" wrote: 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 -- Regards Ron De Bruin http://www.rondebruin.nl "Aziz Ahmedabadwala" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Promt
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\ -- Regards Ron De Bruin http://www.rondebruin.nl "Aziz Ahmedabadwala" wrote in message ... 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" wrote: 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 -- Regards Ron De Bruin http://www.rondebruin.nl "Aziz Ahmedabadwala" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Promt
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 "Ron de Bruin" wrote: 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\ -- Regards Ron De Bruin http://www.rondebruin.nl "Aziz Ahmedabadwala" wrote in message ... 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" wrote: 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 -- Regards Ron De Bruin http://www.rondebruin.nl "Aziz Ahmedabadwala" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Promt when closing Excel | Excel Discussion (Misc queries) | |||
ms query promt in vba | Excel Programming | |||
Can we Set Default value for the Excel promt(Y/N) | Excel Programming | |||
macro that promt user for input | Excel Programming | |||
Printing to Adobe without getting promt for name | Excel Programming |