PDA

View Full Version : How do I disable a macro button if file name is a date?



Jchess
04-24-2015, 10:17 AM
I'm using code found on this site as listed below. I am using a button to run the macro. I would like for the macro to be disabled if the Excel spreadsheet is named a date (any date). The original file is named EOD Report version 3.0.xls. Thus, once the macro button changes the file name to a date, I'd like to prevent the macro button from functioning on any file that has already been named a date. Please note that cell A1 is always empty, so the macro uses only the date to save file.

Here is the code I'm using:
Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir "T:\EVERYONE\EXCEL\EOD Cash Worksheets"
ActiveWorkbook.SaveAs Filename:=newFile

End Sub

mperrah
04-24-2015, 03:31 PM
not tested but might be a start.

Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax

If ThisWorkbook.Name = "*##-##-####" Then ' not sure on the formating for testing if name is a date, but this is a start
MsgBox ("You are trying to run this macro from a previously saved version. The macro will end now."), vbOKOnly
End
End If

fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir "T:\EVERYONE\EXCEL\EOD Cash Worksheets"
ActiveWorkbook.SaveAs Filename:=newFile

End Sub

Jchess
04-27-2015, 02:08 PM
not tested but might be a start.

Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax

If ThisWorkbook.Name = "*##-##-####" Then ' not sure on the formating for testing if name is a date, but this is a start
MsgBox ("You are trying to run this macro from a previously saved version. The macro will end now."), vbOKOnly
End
End If

fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir "T:\EVERYONE\EXCEL\EOD Cash Worksheets"
ActiveWorkbook.SaveAs Filename:=newFile

End Sub

The code skips over the IF statement, so I guess that the formatting still needs something, but as you said, it gives me a place to start and appreciate the guidance very much. Thank you.

Jchess
04-27-2015, 03:02 PM
I'd like to thank mperrah for heading me in the right direction. With mperrah's input, I solved the problem by taking a different approach and checking to see if the original file name had been changed as follows:

Sub SvMe()
'Saves filename as value of A1 plus the current date

Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
If Not ThisWorkbook.Name = "EOD Report version 3.0.xlsm" Then
MsgBox ("You are trying to run this macro from a previously saved version. The macro will end now."), vbOKOnly
End
End If


fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir "T:\EVERYONE\EXCEL\EOD Cash Worksheets"
ActiveWorkbook.SaveAs Filename:=newFile


End Sub

mperrah
04-27-2015, 03:17 PM
I was searching for a way to check if the file name was in a date format by parsing the name and testing it.
It hadn't occurred to me that the original file name is an easier test, duh. good call.
(The code I wrote didn't skipped the If, it just never found a match the way I wrote it (bad syntax) so moved on.)
Silly how machines cant find something that doesn't exist... guess that's a human skill...
Glad to help you find a solution. :cloud9: