Before macro runs - check workbook name

[Guest]

Hello All,
I am using Excel 97 and need some help with VBA as I don't know it all that
well.

Before the below macro is run, the end user needs to save the workbook as
CSRreport.xls. However, some users forget to save the workbook under the new
name before running the macro, which then causes a problem with the original
file (workbook).

I need the macro (below) to first check that the workbook is named
“CSRreport.xls†before the rest of the code continues. How can I add to the
following code at the beginning to check that the workbook name is
“CSRreport.xls†and if it isn’t, display a message box informing the user to
save the file as “CSRreport.xls and try the macro again.


Sub CSRreport()
' CSRreport Macro
' Macro recorded 5/10/2006
'
If Worksheets("PastDue").Visible = True Then
Worksheets("PastDue").Visible = False
End If

Worksheets("Label Number").Select
Range("U3").Select
CSRFilter
CSRPrintarea
Range("A:A,D,F:G,J:M,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA").Select
Range("AA2").Activate
Selection.EntireColumn.Hidden = True
CSRTitle

End Sub

TIA
Jan

 

[terryvanduzee]

One way may be: Im only a newby and someone else may have a better
solution.

Add this code before the rest of your code.

If Not ThisWorkbook.Name = "CSRReport" & ".xls" Then
MsgBox "Please save this workbook as CSRReport.xls"
Else
'your code goes here
End If

Terry

 

[Guest]

Terry,

Thanks for responsding. When I try the code you provided, I keep on getting
the message box even if the file has been saved with the new name and the
rest of the code (my original) doesn't run.

Jan

 

[NickHK]

Nearly, but you need add a little more:
Const REQUIREDFILENAME as string="CSRReport.xls"
If Not ThisWorkbook.Name = REQUIREDFILENAME Then
MsgBox "Please save this workbook as " & REQUIREDFILENAME
Exit Sub
Else
'your code goes here
End If

But you help out the user a bit more:
Const REQUIREDFILENAME as string="CSRReport.xls"
Dim Msg as String
If Not ThisWorkbook.Name = REQUIREDFILENAME Then
Msg="This workbook must be saved as " & REQUIREDFILENAME & " before
running the routine." & vbNewLine
Msg=Msg & "Do you want save it now and continue with the routine ?"
If MsgBox (Msg,vbExclamation+vbYesNo)=vbYes
'Add the path if necessary, or Thisworkbook.Path
Thisworkbook.SaveAs REQUIREDFILENAME
Else
Exit Sub
End If
Else
'your code goes here
End If

NickHK

"terryvanduzee" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

Hi Nick,

As I mentioned previously, I know VBA very little. I choose to use the
first part of your suggestion and not the more helpful one. However, even
when the file is saved as CSRReport.xls before running the code. I receive
the message box to save the file. Below is the code that I adapted per your
suggestion. What am I doing wrong. TIA.

Sub CSRReport()
' CSRreport Macro
' Macro recorded 5/10/2006 by Administrator
'
Const REQUIREDFILENAME As String = "CSRReport.xls"

If Not ThisWorkbook.Name = REQUIREDFILENAME Then
MsgBox "Please save this workbook as " & REQUIREDFILENAME
Exit Sub
Else
If Worksheets("PastDue").Visible = True Then
Worksheets("PastDue").Visible = False
End If

Worksheets("Label Number").Select
Range("U3").Select
CSRFilter
CSRPrintarea
Range("A:A,D,F:G,J:M,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA").Select
Range("AA2").Activate
Selection.EntireColumn.Hidden = True
CSRTitle
End If
End Sub

Nearly, but you need add a little more:
Const REQUIREDFILENAME as string="CSRReport.xls"
If Not ThisWorkbook.Name = REQUIREDFILENAME Then
MsgBox "Please save this workbook as " & REQUIREDFILENAME
Exit Sub
Else
'your code goes here
End If

But you help out the user a bit more:
Const REQUIREDFILENAME as string="CSRReport.xls"
Dim Msg as String
If Not ThisWorkbook.Name = REQUIREDFILENAME Then
Msg="This workbook must be saved as " & REQUIREDFILENAME & " before
running the routine." & vbNewLine
Msg=Msg & "Do you want save it now and continue with the routine ?"
If MsgBox (Msg,vbExclamation+vbYesNo)=vbYes
'Add the path if necessary, or Thisworkbook.Path
Thisworkbook.SaveAs REQUIREDFILENAME
Else
Exit Sub
End If
Else
'your code goes here
End If

NickHK

"terryvanduzee" <[email protected]>
wrote in message

 

[Die_Another_Day]

I'm just guessing but the macro being ran isn't in CSRreport.xls is it?
if not then use ActiveWorkbook. otherwise it could be a case sensitive
thing.
If Not lCase(ActiveWorkbook.Name) = lCase(REQUIREDFILENAME) Then

lCase forces all letters to lower case bypassing case sensitivity.
ActiveWorkbook looks at the book that is active while Thisworkbook
looks at the book with the code.

HTH

Die_Another_Day

Hi Nick,

As I mentioned previously, I know VBA very little. I choose to use the
first part of your suggestion and not the more helpful one. However, even
when the file is saved as CSRReport.xls before running the code. I receive
the message box to save the file. Below is the code that I adapted per your
suggestion. What am I doing wrong. TIA.

Sub CSRReport()
' CSRreport Macro
' Macro recorded 5/10/2006 by Administrator
'
Const REQUIREDFILENAME As String = "CSRReport.xls"

If Not ThisWorkbook.Name = REQUIREDFILENAME Then
MsgBox "Please save this workbook as " & REQUIREDFILENAME
Exit Sub
Else
If Worksheets("PastDue").Visible = True Then
Worksheets("PastDue").Visible = False
End If

Worksheets("Label Number").Select
Range("U3").Select
CSRFilter
CSRPrintarea
Range("A:A,D,F:G,J:M,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA").Select
Range("AA2").Activate
Selection.EntireColumn.Hidden = True
CSRTitle
End If
End Sub

 

[Guest]

HTH,

Thank you. That was the problem... active.workname and iCase. It appears
to be working as needed.

Jan

 

[Die_Another_Day]

LCase by the way as in Lower Case.

Charles
xl Geek