| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA macro runs fine, but freezes if I try to do ANYTHING else whileit runs | Setting up and Configuration of Excel | |||
| how to create a macro that runs other macro in the same workbook | Excel Programming | |||
| Macro runs in source , but not when in Personal Macro Workbook | Excel Programming | |||
| Excel Workbook automatically (by design) runs a macro | Excel Programming | |||
| Excel Workbook automatically (by design) runs a macro | Excel Programming | |||
Linear Mode
