Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prompt user to select item and use as a variable
This is probably really easy, but I'm not figuring it out.
I have a macro that manipulates a spreadsheet. At the end of that process I want to save the spreadsheet to a specific location with a specific name. i.e. Dim strMonth as String Dim strPath as String strMonth = InputBox("ask user to insert Month of report, i.e. January") strPath = "S:\Management Reports\2009\StatusSummary_" & _ strMonth & ".xlsx" Of Course, I don't want to use an InputBox since the user might not enter the month correctly. I think I want a ComboBox that the user can select from, but it would have to be created with VBA as the spreadsheet the user will be working with is actually a csv file output from another program. In addition to forcing the selection of a particular month by the user, I need to check to make sure that filename isn't already in the destiation folder. For that I've come up with Set objFSO = CreateObject("Scripting.FileSystemObject") If Not objFSO.FileExists(strPath) Then ActiveWorkbook.SaveAs Filename:= strPath, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If But if the file does exist, I don't want the user to be presented with the option of overwriting the existing file. How do I suppress the "file exists" message box and prompt the user to take a different course of action instead? ~ Roger |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt user to select a shape | Excel Programming | |||
How to Prompt a user to select a Folder. | Excel Programming | |||
Prompt user to select directory | Excel Programming | |||
Prompt user to select a printer using a checkbox within a user | Excel Programming | |||
how do I prompt the user to select a cell? | Excel Programming |