Before macro runs - check workbook name
 

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 isnt, 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

Before macro runs - check workbook name
 

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

Before macro runs - check workbook name
 

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 mo
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 mo
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"
wrote in message
news:terryvanduzee.2bw398_1154483119.8324@excelfor um-nospam.com...

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

Before macro runs - check workbook name
 

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 mo
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 mo
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"
wrote in message
news:terryvanduzee.2bw398_1154483119.8324@excelfor um-nospam.com...

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

Before macro runs - check workbook name
 

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 mo
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 mo
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"
wrote in message
news:terryvanduzee.2bw398_1154483119.8324@excelfor um-nospam.com...

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

Before macro runs - check workbook name
 

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 mo
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 mo
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"
wrote in message
news:terryvanduzee.2bw398_1154483119.8324@excelfor um-nospam.com...

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