If WB not there; give message: What am I doing wrong?

[RGreen]

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]

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]

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]

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.