| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calling a macro from Worksheet--Activate | Excel Discussion (Misc queries) | |||
| calling/running a macro on another worksheet | Excel Programming | |||
| calling worksheet function from a macro | Excel Worksheet Functions | |||
| Calling a macro in another worksheet | Excel Programming | |||
| Macro to return to previous worksheet | Excel Programming | |||
Linear Mode
