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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prompt user to select item and use as a variable
A ListBox or ComboBox would provide better control over the user input for a
date related entry. You can put it on a UserForm and use it like a pop up. Make a public variable to capture the value of the control, whichever you decide to use. i.e. Public myVar = UserForm1.ComboBox1.Value As for not overwriting the existing file, if the file exists, produce a message box that tells the user what you want them to do, and use the Else part of the If statement to exit the sub. i.e. If Not objFSO.FileExists(strPath) Then 'Save as Else MsgBox "This file name already exists. Do something else" End If Or you can determine what to do and write the code to make it happen, taking the user out of any decision making. "RogerM" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prompt user to select item and use as a variable
If you give the user the option to select a month using a drop down list.
he/she can still choose an incorrect month. Can you automate the naming process to cover the month? In other words, is it likely the file always relates to the current month? or previous month? Alternatively, you could check for validity using a function. Function checkMonth(UserString) As Boolean Dim temp As String checkMonth = False For i = 1 To 12 temp = DateSerial(Year(Date), i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkMonth = True Exit For End If Next i End Function OR, check a shorter period using a variation. For example, this check the text month entered by the user is in format MMM and is either the current month or one of the previous two months. Function checkThreeMonth(UserString) As Boolean Dim temp As String checkThreeMonth = False For i = 0 To 2 temp = DateSerial(Year(Date), Month(Date) - i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkThreeMonth = True Exit For End If Next i End Function Dir() will help you deal with a file that exists. : http://spreadsheetpage.com/index.php...vba_functions/ -- Steve "RogerM" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prompt user to select item and use as a variable
I hadn't considered that possibility. The process would always be run on at
the beginning of a month for the prior month's data. So the filename should always be the previous month. How do I obtain that value? "AltaEgo" <Somewhere@NotHere wrote in message ... If you give the user the option to select a month using a drop down list. he/she can still choose an incorrect month. Can you automate the naming process to cover the month? In other words, is it likely the file always relates to the current month? or previous month? Alternatively, you could check for validity using a function. Function checkMonth(UserString) As Boolean Dim temp As String checkMonth = False For i = 1 To 12 temp = DateSerial(Year(Date), i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkMonth = True Exit For End If Next i End Function OR, check a shorter period using a variation. For example, this check the text month entered by the user is in format MMM and is either the current month or one of the previous two months. Function checkThreeMonth(UserString) As Boolean Dim temp As String checkThreeMonth = False For i = 0 To 2 temp = DateSerial(Year(Date), Month(Date) - i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkThreeMonth = True Exit For End If Next i End Function Dir() will help you deal with a file that exists. : http://spreadsheetpage.com/index.php...vba_functions/ -- Steve "RogerM" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prompt user to select item and use as a variable
Function PrevMth()
Dim temp temp = DateSerial(Year(Date), Month(Date) - 1, Day(Date)) PrevMth = Format(temp, "MMM") End Function -- Steve "RogerM" wrote in message ... I hadn't considered that possibility. The process would always be run on at the beginning of a month for the prior month's data. So the filename should always be the previous month. How do I obtain that value? "AltaEgo" <Somewhere@NotHere wrote in message ... If you give the user the option to select a month using a drop down list. he/she can still choose an incorrect month. Can you automate the naming process to cover the month? In other words, is it likely the file always relates to the current month? or previous month? Alternatively, you could check for validity using a function. Function checkMonth(UserString) As Boolean Dim temp As String checkMonth = False For i = 1 To 12 temp = DateSerial(Year(Date), i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkMonth = True Exit For End If Next i End Function OR, check a shorter period using a variation. For example, this check the text month entered by the user is in format MMM and is either the current month or one of the previous two months. Function checkThreeMonth(UserString) As Boolean Dim temp As String checkThreeMonth = False For i = 0 To 2 temp = DateSerial(Year(Date), Month(Date) - i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkThreeMonth = True Exit For End If Next i End Function Dir() will help you deal with a file that exists. : http://spreadsheetpage.com/index.php...vba_functions/ -- Steve "RogerM" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prompt user to select item and use as a variable
You are right. The controls are not listed in the tab index order; but in the
order the control was placed into the form. If this post helps click Yes --------------- Jacob Skaria "AltaEgo" wrote: If you give the user the option to select a month using a drop down list. he/she can still choose an incorrect month. Can you automate the naming process to cover the month? In other words, is it likely the file always relates to the current month? or previous month? Alternatively, you could check for validity using a function. Function checkMonth(UserString) As Boolean Dim temp As String checkMonth = False For i = 1 To 12 temp = DateSerial(Year(Date), i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkMonth = True Exit For End If Next i End Function OR, check a shorter period using a variation. For example, this check the text month entered by the user is in format MMM and is either the current month or one of the previous two months. Function checkThreeMonth(UserString) As Boolean Dim temp As String checkThreeMonth = False For i = 0 To 2 temp = DateSerial(Year(Date), Month(Date) - i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkThreeMonth = True Exit For End If Next i End Function Dir() will help you deal with a file that exists. : http://spreadsheetpage.com/index.php...vba_functions/ -- Steve "RogerM" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prompt user to select item and use as a variable
Steve,
That will work perfect. Thanks. "AltaEgo" <Somewhere@NotHere wrote in message ... Function PrevMth() Dim temp temp = DateSerial(Year(Date), Month(Date) - 1, Day(Date)) PrevMth = Format(temp, "MMM") End Function -- Steve "RogerM" wrote in message ... I hadn't considered that possibility. The process would always be run on at the beginning of a month for the prior month's data. So the filename should always be the previous month. How do I obtain that value? "AltaEgo" <Somewhere@NotHere wrote in message ... If you give the user the option to select a month using a drop down list. he/she can still choose an incorrect month. Can you automate the naming process to cover the month? In other words, is it likely the file always relates to the current month? or previous month? Alternatively, you could check for validity using a function. Function checkMonth(UserString) As Boolean Dim temp As String checkMonth = False For i = 1 To 12 temp = DateSerial(Year(Date), i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkMonth = True Exit For End If Next i End Function OR, check a shorter period using a variation. For example, this check the text month entered by the user is in format MMM and is either the current month or one of the previous two months. Function checkThreeMonth(UserString) As Boolean Dim temp As String checkThreeMonth = False For i = 0 To 2 temp = DateSerial(Year(Date), Month(Date) - i, Day(Date)) If UCase(Format(temp, "MMM")) = UCase(UserString) Then checkThreeMonth = True Exit For End If Next i End Function Dir() will help you deal with a file that exists. : http://spreadsheetpage.com/index.php...vba_functions/ -- Steve "RogerM" wrote in message ... 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 |
Reply |
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 |