Macro for calling the previous worksheet
 

I've been using the function below that I found in another post. However, I
am having a problem that if I have another workbook open at the same time,
then it sometimes grabs data from Sheets(n-1) from that workbook. I added
Application.Volatile to the function to have the function get the new data as
soon as the pointed to cell changes. Could this be what is causing the
workbook confusion?

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Macro for calling the previous worksheet
 

Try

Function PrevSheet(rg As Range)
With Application.Caller.Parent

n = .Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(.Parent.Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = .Parent.Sheets(n - 1).Range(rg.Address).Value
End If
End With
End Function


--
__________________________________
HTH

Bob

"Balkar" wrote in message
...
I've been using the function below that I found in another post. However,
I
am having a problem that if I have another workbook open at the same time,
then it sometimes grabs data from Sheets(n-1) from that workbook. I added
Application.Volatile to the function to have the function get the new data
as
soon as the pointed to cell changes. Could this be what is causing the
workbook confusion?

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Macro for calling the previous worksheet
 

So far it looks good. Why would Application.Caller.Parent.Parent.Sheet...
work better than Application.Caller.Parent..Sheet...?

"Bob Phillips" wrote:

Try

Function PrevSheet(rg As Range)
With Application.Caller.Parent

n = .Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(.Parent.Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = .Parent.Sheets(n - 1).Range(rg.Address).Value
End If
End With
End Function


--
__________________________________
HTH

Bob

"Balkar" wrote in message
...
I've been using the function below that I found in another post. However,
I
am having a problem that if I have another workbook open at the same time,
then it sometimes grabs data from Sheets(n-1) from that workbook. I added
Application.Volatile to the function to have the function get the new data
as
soon as the pointed to cell changes. Could this be what is causing the
workbook confusion?

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Macro for calling the previous worksheet
 

Because the parent is the sheet, so you have to go up one to the
parent.parent, the book, which has sheet properties.

--
__________________________________
HTH

Bob

"Balkar" wrote in message
...
So far it looks good. Why would Application.Caller.Parent.Parent.Sheet...
work better than Application.Caller.Parent..Sheet...?

"Bob Phillips" wrote:

Try

Function PrevSheet(rg As Range)
With Application.Caller.Parent

n = .Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(.Parent.Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = .Parent.Sheets(n - 1).Range(rg.Address).Value
End If
End With
End Function


--
__________________________________
HTH

Bob

"Balkar" wrote in message
...
I've been using the function below that I found in another post.
However,
I
am having a problem that if I have another workbook open at the same
time,
then it sometimes grabs data from Sheets(n-1) from that workbook. I
added
Application.Volatile to the function to have the function get the new
data
as
soon as the pointed to cell changes. Could this be what is causing the
workbook confusion?

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function