If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If WB not there; give message: What am I doing wrong?
Sub DEC_NY_AutoShape22_Click()
' ' AutoShape21_Click Macro ' Macro recorded 8/31/2009 by Rick Green ' Application.ScreenUpdating = False ' 'Dim wb As Workbook 'On Error Resume Next 'Set wb = ActiveWorkbook.Worksheets("DEC") 'On Error GoTo 0 'If wb Is Nothing Then ' MsgBox "Year not active for this month." 'Else ChDir _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010" Workbooks.Open Filename:= _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\DEC.xls" Application.Run "DEC.xls!backtotop" 'End If End Sub I have a workbook named Months: 12 months and arrows which either refers to year 2009, 2010, or 2011. I have not work on 2011 and only started to work on few of the 2010. If these workbooks are not in the location specified by the macro, I want it to state that workbook for that year has not been created and go back to the workbook call "Months". I have searched various references and thought this maybe the correct code, apparently I am wrong.... Can someone please help me. Thanks, Rick |
#2
|
|||
|
|||
If WB not there; give message: What am I doing wrong?
First, it looks like the code you commented was really working with
worksheets--not workbooks. Assuming that you really meant workbooks: Dim wkbk as workbook dim wkbkName as string dim myPath as string wkbkname = "Dec.xls" 'include the extension 'include the trailing backslash on the path myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\" set wkbk = nothing on error resume next 'check to see if it's already open set wkbk = workbooks(wbname) on error goto 0 if wkbk is nothing then 'not open on error resume next 'no need to change path since it's included in the .open statement set wkbk = workbooks.open(filename:=mypath & wkbkname) on error goto 0 if wkbk is nothing then msgbox "File could not be opened" else msgbox "It's open now" end if else msgbox "It's already open!" end if if wkbk is nothing then exit sub??? else application.run "'" & wkbk.name & "'!backtotop" end if ========== This doesn't actually check to see if the file is there. It just checks to see if can be opened. If you only wanted to check for the existence of the file, you could use: Dim TestStr as string dim wkbkName as string dim myPath as string wkbkname = "Dec.xls" 'include the extension 'include the trailing backslash on the path myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\" teststr = "" on error resume next teststr = dir(mypath & wkbkname) on error goto 0 if teststr = "" then msgbox "it's not there" else msgbox "yes, it is!" end if ======= All untested, uncompiled. Watch for typos! RGreen wrote: Sub DEC_NY_AutoShape22_Click() ' ' AutoShape21_Click Macro ' Macro recorded 8/31/2009 by Rick Green ' Application.ScreenUpdating = False ' 'Dim wb As Workbook 'On Error Resume Next 'Set wb = ActiveWorkbook.Worksheets("DEC") 'On Error GoTo 0 'If wb Is Nothing Then ' MsgBox "Year not active for this month." 'Else ChDir _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010" Workbooks.Open Filename:= _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\DEC.xls" Application.Run "DEC.xls!backtotop" 'End If End Sub I have a workbook named Months: 12 months and arrows which either refers to year 2009, 2010, or 2011. I have not work on 2011 and only started to work on few of the 2010. If these workbooks are not in the location specified by the macro, I want it to state that workbook for that year has not been created and go back to the workbook call "Months". I have searched various references and thought this maybe the correct code, apparently I am wrong.... Can someone please help me. Thanks, Rick -- Dave Peterson |
#3
|
|||
|
|||
If WB not there; give message: What am I doing wrong?
Dave, I'm sorry not getting back to you earlier, my boss (WIFE), stated she
did not care if the file was not there or not. RIght now I am concentrating on a new macro. 1. I need to select a print areas and let her decide which print area she would like to print: Range("A1:F7").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$F$7" ActiveWindow.SelectedSheets.PrintPreview ActiveWindow.View = xlNormalView 'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Range("A1:G14").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$G$14" ActiveWindow.SelectedSheets.PrintPreview 'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True I need to pick and choose if not first then 2nd. I am trying to be creative by providing a snapshot of those print areas and let her choose which one. I been pursuing this for the last week or so. 2. She would like to see comment as she scrolls over certain cells to reflect back on cells which she entered data on. So if she scrolls over cell y29, she would like to see data which was entered in column a10:a34. if she scroll over z29 she would see the data which was entered in column b10:b34.... if she scrolls over y30 she would see the data entered in a35:a60 and so on. I am not sure how to even start with this macor... I was thinking with mouse over.... but I get confused with arrays and dynamic cells. If you can provide any assistance with this would be greatly appreciated. Thanks, Rick "Dave Peterson" wrote: First, it looks like the code you commented was really working with worksheets--not workbooks. Assuming that you really meant workbooks: Dim wkbk as workbook dim wkbkName as string dim myPath as string wkbkname = "Dec.xls" 'include the extension 'include the trailing backslash on the path myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\" set wkbk = nothing on error resume next 'check to see if it's already open set wkbk = workbooks(wbname) on error goto 0 if wkbk is nothing then 'not open on error resume next 'no need to change path since it's included in the .open statement set wkbk = workbooks.open(filename:=mypath & wkbkname) on error goto 0 if wkbk is nothing then msgbox "File could not be opened" else msgbox "It's open now" end if else msgbox "It's already open!" end if if wkbk is nothing then exit sub??? else application.run "'" & wkbk.name & "'!backtotop" end if ========== This doesn't actually check to see if the file is there. It just checks to see if can be opened. If you only wanted to check for the existence of the file, you could use: Dim TestStr as string dim wkbkName as string dim myPath as string wkbkname = "Dec.xls" 'include the extension 'include the trailing backslash on the path myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\" teststr = "" on error resume next teststr = dir(mypath & wkbkname) on error goto 0 if teststr = "" then msgbox "it's not there" else msgbox "yes, it is!" end if ======= All untested, uncompiled. Watch for typos! RGreen wrote: Sub DEC_NY_AutoShape22_Click() ' ' AutoShape21_Click Macro ' Macro recorded 8/31/2009 by Rick Green ' Application.ScreenUpdating = False ' 'Dim wb As Workbook 'On Error Resume Next 'Set wb = ActiveWorkbook.Worksheets("DEC") 'On Error GoTo 0 'If wb Is Nothing Then ' MsgBox "Year not active for this month." 'Else ChDir _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010" Workbooks.Open Filename:= _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\DEC.xls" Application.Run "DEC.xls!backtotop" 'End If End Sub I have a workbook named Months: 12 months and arrows which either refers to year 2009, 2010, or 2011. I have not work on 2011 and only started to work on few of the 2010. If these workbooks are not in the location specified by the macro, I want it to state that workbook for that year has not been created and go back to the workbook call "Months". I have searched various references and thought this maybe the correct code, apparently I am wrong.... Can someone please help me. Thanks, Rick -- Dave Peterson |
#4
|
|||
|
|||
If WB not there; give message: What am I doing wrong?
For #1, I would show her how to select a range and then print the selection--not
changing the print range. It's just an option on the print dialog. For #2, I'd show her how to use Window|new window and then Window|arrange. I know I wouldn't try a macro. There is no mouseover event in the worksheet. RGreen wrote: Dave, I'm sorry not getting back to you earlier, my boss (WIFE), stated she did not care if the file was not there or not. RIght now I am concentrating on a new macro. 1. I need to select a print areas and let her decide which print area she would like to print: Range("A1:F7").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$F$7" ActiveWindow.SelectedSheets.PrintPreview ActiveWindow.View = xlNormalView 'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Range("A1:G14").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$G$14" ActiveWindow.SelectedSheets.PrintPreview 'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True I need to pick and choose if not first then 2nd. I am trying to be creative by providing a snapshot of those print areas and let her choose which one. I been pursuing this for the last week or so. 2. She would like to see comment as she scrolls over certain cells to reflect back on cells which she entered data on. So if she scrolls over cell y29, she would like to see data which was entered in column a10:a34. if she scroll over z29 she would see the data which was entered in column b10:b34.... if she scrolls over y30 she would see the data entered in a35:a60 and so on. I am not sure how to even start with this macor... I was thinking with mouse over.... but I get confused with arrays and dynamic cells. If you can provide any assistance with this would be greatly appreciated. Thanks, Rick "Dave Peterson" wrote: First, it looks like the code you commented was really working with worksheets--not workbooks. Assuming that you really meant workbooks: Dim wkbk as workbook dim wkbkName as string dim myPath as string wkbkname = "Dec.xls" 'include the extension 'include the trailing backslash on the path myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\" set wkbk = nothing on error resume next 'check to see if it's already open set wkbk = workbooks(wbname) on error goto 0 if wkbk is nothing then 'not open on error resume next 'no need to change path since it's included in the .open statement set wkbk = workbooks.open(filename:=mypath & wkbkname) on error goto 0 if wkbk is nothing then msgbox "File could not be opened" else msgbox "It's open now" end if else msgbox "It's already open!" end if if wkbk is nothing then exit sub??? else application.run "'" & wkbk.name & "'!backtotop" end if ========== This doesn't actually check to see if the file is there. It just checks to see if can be opened. If you only wanted to check for the existence of the file, you could use: Dim TestStr as string dim wkbkName as string dim myPath as string wkbkname = "Dec.xls" 'include the extension 'include the trailing backslash on the path myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\" teststr = "" on error resume next teststr = dir(mypath & wkbkname) on error goto 0 if teststr = "" then msgbox "it's not there" else msgbox "yes, it is!" end if ======= All untested, uncompiled. Watch for typos! RGreen wrote: Sub DEC_NY_AutoShape22_Click() ' ' AutoShape21_Click Macro ' Macro recorded 8/31/2009 by Rick Green ' Application.ScreenUpdating = False ' 'Dim wb As Workbook 'On Error Resume Next 'Set wb = ActiveWorkbook.Worksheets("DEC") 'On Error GoTo 0 'If wb Is Nothing Then ' MsgBox "Year not active for this month." 'Else ChDir _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010" Workbooks.Open Filename:= _ "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\DEC.xls" Application.Run "DEC.xls!backtotop" 'End If End Sub I have a workbook named Months: 12 months and arrows which either refers to year 2009, 2010, or 2011. I have not work on 2011 and only started to work on few of the 2010. If these workbooks are not in the location specified by the macro, I want it to state that workbook for that year has not been created and go back to the workbook call "Months". I have searched various references and thought this maybe the correct code, apparently I am wrong.... Can someone please help me. Thanks, Rick -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|