Before macro runs - check workbook name

Jan · 08-01-2006, 09:30 PM

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: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** · 08-01-2006, 09:44 PM

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

Jan · 08-01-2006, 10:15 PM

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

"terryvanduzee" wrote:

>
> 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
>
>
> --
> terryvanduzee
> ------------------------------------------------------------------------
> terryvanduzee's Profile: http://www.excelforum.com/member.php...o&userid=37004
> View this thread: http://www.excelforum.com/showthread...hreadid=567286
>
>

NickHK · 08-01-2006, 10:50 PM

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]...
>
> 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
>
>
> --
> terryvanduzee
> ------------------------------------------------------------------------
> terryvanduzee's Profile:
http://www.excelforum.com/member.php...o&userid=37004
> View this thread: http://www.excelforum.com/showthread...hreadid=567286
>

Jan · 08-01-2006, 11:25 PM

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: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

"NickHK" wrote:

> 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]...
> >
> > 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
> >
> >
> > --
> > terryvanduzee
> > ------------------------------------------------------------------------
> > terryvanduzee's Profile:
> http://www.excelforum.com/member.php...o&userid=37004
> > View this thread: http://www.excelforum.com/showthread...hreadid=567286
> >
>
>
>

Die_Another_Day · 08-02-2006, 12:10 AM

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
Jan wrote:
> 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: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
>
> "NickHK" wrote:
>
> > 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]...
> > >
> > > 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
> > >
> > >
> > > --
> > > terryvanduzee
> > > ------------------------------------------------------------------------
> > > terryvanduzee's Profile:
> > http://www.excelforum.com/member.php...o&userid=37004
> > > View this thread: http://www.excelforum.com/showthread...hreadid=567286
> > >
> >
> >
> >

Jan · 08-02-2006, 12:55 AM

HTH,

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

Jan

"Die_Another_Day" wrote:

> 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
> Jan wrote:
> > 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: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
> >
> > "NickHK" wrote:
> >
> > > 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]...
> > > >
> > > > 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
> > > >
> > > >
> > > > --
> > > > terryvanduzee
> > > > ------------------------------------------------------------------------
> > > > terryvanduzee's Profile:
> > > http://www.excelforum.com/member.php...o&userid=37004
> > > > View this thread: http://www.excelforum.com/showthread...hreadid=567286
> > > >
> > >
> > >
> > >
>
>

Die_Another_Day · 08-02-2006, 09:30 AM

LCase by the way as in Lower Case.

Charles
xl Geek
Jan wrote:
> HTH,
>
> Thank you. That was the problem... active.workname and iCase. It appears
> to be working as needed.
>
> Jan
>
> "Die_Another_Day" wrote:
>
> > 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
> > Jan wrote:
> > > 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: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
> > >
> > > "NickHK" wrote:
> > >
> > > > 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]...
> > > > >
> > > > > 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
> > > > >
> > > > >
> > > > > --
> > > > > terryvanduzee
> > > > > ------------------------------------------------------------------------
> > > > > terryvanduzee's Profile:
> > > > http://www.excelforum.com/member.php...o&userid=37004
> > > > > View this thread: http://www.excelforum.com/showthread...hreadid=567286
> > > > >
> > > >
> > > >
> > > >
> >
> >