I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

How would the macro determine where to go based on the letter and column
combination.

assume the cell in column E would correspond to the cell selected in one of
columns F to P

set rng = Intersect(columns(5),ActiveCell.EntireRow)
sLetter = rng.Value

Now what?

--
Regards,
Tom Ogilvy









I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

Tom Ogilvy wrote:
How would the macro determine where to go based on the letter and column
combination.

assume the cell in column E would correspond to the cell selected in one of
columns F to P

set rng = Intersect(columns(5),ActiveCell.EntireRow)
sLetter = rng.Value

Now what?

--
Regards,
Tom Ogilvy

Tom, I am sorry that my desrciption is not concise and clear. Let me summuarize briefly:
1. I would create 20 "table"worksheets, each sheet having a particular
table.
2. On each row of the "main" worksheet, I would enter a code letter in
column E.
3. I would then go to cell in the same row column F, making F the
active column, and, if I need to refer to a table worksheet, I would
invoke a macro [Crlt-Shift Z for example], and the macro would do
something like...IF the value in col E = "M" and the active column = F,
THEN go to sheet 9.
4. After viewing the table in sheet 9, I would then want to press ENTER
or any other key and go back to the cell that was active when I invoked
the macro I could enter the value I viewed in the table.
As i mentioned before the column from which i invoke the macro could be
from col F to col P.
I hope this is more clear, and i apologize for not being able to be
more clean AND concise.
Thanks,
Billy







I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

for that approach you would need 11 columns x n rows of if statements. If
you have on 20 possible table sheets to go to, that doesn't make much sense.
If you have a rule that would be used, then you would want to code the rule.
Or you could set up another table on another sheet where you lookup the
letter and column number and return the sheet to go to.

--
Regards,
Tom Ogilvy


"Billy" wrote:


Tom Ogilvy wrote:
How would the macro determine where to go based on the letter and column
combination.

assume the cell in column E would correspond to the cell selected in one of
columns F to P

set rng = Intersect(columns(5),ActiveCell.EntireRow)
sLetter = rng.Value

Now what?

--
Regards,
Tom Ogilvy

Tom, I am sorry that my desrciption is not concise and clear. Let me summuarize briefly:
1. I would create 20 "table"worksheets, each sheet having a particular
table.
2. On each row of the "main" worksheet, I would enter a code letter in
column E.
3. I would then go to cell in the same row column F, making F the
active column, and, if I need to refer to a table worksheet, I would
invoke a macro [Crlt-Shift Z for example], and the macro would do
something like...IF the value in col E = "M" and the active column = F,
THEN go to sheet 9.
4. After viewing the table in sheet 9, I would then want to press ENTER
or any other key and go back to the cell that was active when I invoked
the macro I could enter the value I viewed in the table.
As i mentioned before the column from which i invoke the macro could be
from col F to col P.
I hope this is more clear, and i apologize for not being able to be
more clean AND concise.
Thanks,
Billy







I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make this
macro perfect. After the macro takes me to the derired sheet, i would
like to view it only, then press "Enter" to return to the original
sheet and active cell. Thak you again so very much!
Billy

I can show you how to use ENTER to go back to the calling sheet, but it is
not a very good way to do it. We need to assign a macro to the ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make this
macro perfect. After the macro takes me to the derired sheet, i would
like to view it only, then press "Enter" to return to the original
sheet and active cell. Thak you again so very much!
Billy

Well, first let me tell you that there will be many "calling" sheets in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back to
the keyboard would not be as fast as pressing Enter [or another key if
need be]. therefore I think I would prefer a keystroke rather than the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide again
when finished viewing. Can this be accomplished also. I hate to impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet, but it is
not a very good way to do it. We need to assign a macro to the ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make this
macro perfect. After the macro takes me to the derired sheet, i would
like to view it only, then press "Enter" to return to the original
sheet and active cell. Thak you again so very much!
Billy

I still think it is not a very good idea to alter the way Excel responds to
keys pressed just to "go back".
Anyway, here it is, copy this into the code section of your "main" sheet,
not in a module:



Dim sheets_stack(1) As Worksheet

Public Sub goBackToSrcSheet()
If Not sheets_stack(0) Is Nothing Then
Application.OnKey "~"
Application.OnKey "{ENTER}"
sheets_stack(1).Visible = xlSheetHidden
sheets_stack(0).Activate
Set sheets_stack(0) = Nothing
Set sheets_stack(1) = Nothing
End If
End Sub

Sub goToSheet()
On Error GoTo NO_SUCH_SHEET
Set sheets_stack(1) = Sheets(Cells(Selection.Row, "E").Value &
Selection.Column)
Set sheets_stack(0) = ActiveSheet
Application.OnKey "~", ActiveSheet.CodeName & ".goBackToSrcSheet"
Application.OnKey "{ENTER}", ActiveSheet.CodeName & ".goBackToSrcSheet"
sheets_stack(1).Visible = xlSheetVisible
sheets_stack(1).Activate
On Error GoTo 0
Exit Sub

NO_SUCH_SHEET:
Err.Clear
On Error GoTo 0
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
End Sub


--
Festina Lente


"Billy" wrote:

Well, first let me tell you that there will be many "calling" sheets in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back to
the keyboard would not be as fast as pressing Enter [or another key if
need be]. therefore I think I would prefer a keystroke rather than the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide again
when finished viewing. Can this be accomplished also. I hate to impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet, but it is
not a very good way to do it. We need to assign a macro to the ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make this
macro perfect. After the macro takes me to the derired sheet, i would
like to view it only, then press "Enter" to return to the original
sheet and active cell. Thak you again so very much!
Billy

I respect your opinion. it doesnt have to be "Enter". I use Ctrl-Shift
Z to acitvate this macro. Could I use Ctrl Shift Z again to go back to
the main sheet? Or Ctrl Shift X? as i said, there are many "main
sheets", close to 100. so i would have to post the code into every main
sheet? In this case, perhaps "Enter" will not work. Thank you for your
patience.

Billy
PapaDos wrote:
I still think it is not a very good idea to alter the way Excel responds to
keys pressed just to "go back".
Anyway, here it is, copy this into the code section of your "main" sheet,
not in a module:



Dim sheets_stack(1) As Worksheet

Public Sub goBackToSrcSheet()
If Not sheets_stack(0) Is Nothing Then
Application.OnKey "~"
Application.OnKey "{ENTER}"
sheets_stack(1).Visible = xlSheetHidden
sheets_stack(0).Activate
Set sheets_stack(0) = Nothing
Set sheets_stack(1) = Nothing
End If
End Sub

Sub goToSheet()
On Error GoTo NO_SUCH_SHEET
Set sheets_stack(1) = Sheets(Cells(Selection.Row, "E").Value &
Selection.Column)
Set sheets_stack(0) = ActiveSheet
Application.OnKey "~", ActiveSheet.CodeName & ".goBackToSrcSheet"
Application.OnKey "{ENTER}", ActiveSheet.CodeName & ".goBackToSrcSheet"
sheets_stack(1).Visible = xlSheetVisible
sheets_stack(1).Activate
On Error GoTo 0
Exit Sub

NO_SUCH_SHEET:
Err.Clear
On Error GoTo 0
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
End Sub


--
Festina Lente


"Billy" wrote:

Well, first let me tell you that there will be many "calling" sheets in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back to
the keyboard would not be as fast as pressing Enter [or another key if
need be]. therefore I think I would prefer a keystroke rather than the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide again
when finished viewing. Can this be accomplished also. I hate to impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet, but it is
not a very good way to do it. We need to assign a macro to the ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make this
macro perfect. After the macro takes me to the derired sheet, i would
like to view it only, then press "Enter" to return to the original
sheet and active cell. Thak you again so very much!
Billy

The problem is not which key is used.

The functionality for returning on a key pressed is not running from the
macro that select the sheets. That macro is assigning another macro to a key
and exits.
That other macro "de-assign" the key and select the original sheet. But
nothing is forcing the execution of that macro, if you don't press that key
when expected, the second madro will not run and the "assigned" key(s) are
not "de-assign". Their behavior will stay altered until they are pressed and
the user will be puzzled by the actions or error messages...

If you put a button or a "goback" link on every "pointed to" sheets, it will
be automatically re-created on a new sheet if you create it by copying an
existing sheet...

An alternative approach is to assign the "returning" macro to a key
combination manually (as you did for the first macro). You can then delete
the lines containing "Application.OnKey... " from the 2 macros...

--
Festina Lente


"Billy" wrote:

I respect your opinion. it doesnt have to be "Enter". I use Ctrl-Shift
Z to acitvate this macro. Could I use Ctrl Shift Z again to go back to
the main sheet? Or Ctrl Shift X? as i said, there are many "main
sheets", close to 100. so i would have to post the code into every main
sheet? In this case, perhaps "Enter" will not work. Thank you for your
patience.

Billy
PapaDos wrote:
I still think it is not a very good idea to alter the way Excel responds to
keys pressed just to "go back".
Anyway, here it is, copy this into the code section of your "main" sheet,
not in a module:



Dim sheets_stack(1) As Worksheet

Public Sub goBackToSrcSheet()
If Not sheets_stack(0) Is Nothing Then
Application.OnKey "~"
Application.OnKey "{ENTER}"
sheets_stack(1).Visible = xlSheetHidden
sheets_stack(0).Activate
Set sheets_stack(0) = Nothing
Set sheets_stack(1) = Nothing
End If
End Sub

Sub goToSheet()
On Error GoTo NO_SUCH_SHEET
Set sheets_stack(1) = Sheets(Cells(Selection.Row, "E").Value &
Selection.Column)
Set sheets_stack(0) = ActiveSheet
Application.OnKey "~", ActiveSheet.CodeName & ".goBackToSrcSheet"
Application.OnKey "{ENTER}", ActiveSheet.CodeName & ".goBackToSrcSheet"
sheets_stack(1).Visible = xlSheetVisible
sheets_stack(1).Activate
On Error GoTo 0
Exit Sub

NO_SUCH_SHEET:
Err.Clear
On Error GoTo 0
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
End Sub


--
Festina Lente


"Billy" wrote:

Well, first let me tell you that there will be many "calling" sheets in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back to
the keyboard would not be as fast as pressing Enter [or another key if
need be]. therefore I think I would prefer a keystroke rather than the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide again
when finished viewing. Can this be accomplished also. I hate to impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet, but it is
not a very good way to do it. We need to assign a macro to the ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value & Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row, "E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on first, the
letter I would input in col. E, and then second, the column that the
curser is in, (which could be any or all cols. from F - P) and then,
when i invoke it, displays a corresponding sheet that will contain a
table I will make that relates to that letter/column combo. I dont know
if it would be an If-Then type procedure or what, but is is certainly
beyond my capability to do. Please not that this letter/column scenario
is repeated on row after row in my spreadsheet, and not just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make this
macro perfect. After the macro takes me to the derired sheet, i would
like to view it only, then press "Enter" to return to the original
sheet and active cell. Thak you again so very much!
Billy

Just as an idea, if you use hyperlinks, either in a cell or the
..FollowHyperlink method and show the Web toolbar, the use has a built in
method of go forwards and backwards through the history of selection.
Suitable ?

NickHK

"PapaDos" wrote in message
...
The problem is not which key is used.

The functionality for returning on a key pressed is not running from the
macro that select the sheets. That macro is assigning another macro to a
key
and exits.
That other macro "de-assign" the key and select the original sheet. But
nothing is forcing the execution of that macro, if you don't press that
key
when expected, the second madro will not run and the "assigned" key(s) are
not "de-assign". Their behavior will stay altered until they are pressed
and
the user will be puzzled by the actions or error messages...

If you put a button or a "goback" link on every "pointed to" sheets, it
will
be automatically re-created on a new sheet if you create it by copying an
existing sheet...

An alternative approach is to assign the "returning" macro to a key
combination manually (as you did for the first macro). You can then delete
the lines containing "Application.OnKey... " from the 2 macros...

--
Festina Lente


"Billy" wrote:

I respect your opinion. it doesnt have to be "Enter". I use Ctrl-Shift
Z to acitvate this macro. Could I use Ctrl Shift Z again to go back to
the main sheet? Or Ctrl Shift X? as i said, there are many "main
sheets", close to 100. so i would have to post the code into every main
sheet? In this case, perhaps "Enter" will not work. Thank you for your
patience.

Billy
PapaDos wrote:
I still think it is not a very good idea to alter the way Excel
responds to
keys pressed just to "go back".
Anyway, here it is, copy this into the code section of your "main"
sheet,
not in a module:



Dim sheets_stack(1) As Worksheet

Public Sub goBackToSrcSheet()
If Not sheets_stack(0) Is Nothing Then
Application.OnKey "~"
Application.OnKey "{ENTER}"
sheets_stack(1).Visible = xlSheetHidden
sheets_stack(0).Activate
Set sheets_stack(0) = Nothing
Set sheets_stack(1) = Nothing
End If
End Sub

Sub goToSheet()
On Error GoTo NO_SUCH_SHEET
Set sheets_stack(1) = Sheets(Cells(Selection.Row, "E").Value &
Selection.Column)
Set sheets_stack(0) = ActiveSheet
Application.OnKey "~", ActiveSheet.CodeName & ".goBackToSrcSheet"
Application.OnKey "{ENTER}", ActiveSheet.CodeName &
".goBackToSrcSheet"
sheets_stack(1).Visible = xlSheetVisible
sheets_stack(1).Activate
On Error GoTo 0
Exit Sub

NO_SUCH_SHEET:
Err.Clear
On Error GoTo 0
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
End Sub


--
Festina Lente


"Billy" wrote:

Well, first let me tell you that there will be many "calling" sheets
in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back
to
the keyboard would not be as fast as pressing Enter [or another key
if
need be]. therefore I think I would prefer a keystroke rather than
the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide
again
when finished viewing. Can this be accomplished also. I hate to
impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet,
but it is
not a very good way to do it. We need to assign a macro to the
ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your
sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value &
Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very
muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on
first, the
letter I would input in col. E, and then second, the
column that the
curser is in, (which could be any or all cols. from F - P)
and then,
when i invoke it, displays a corresponding sheet that
will contain a
table I will make that relates to that letter/column
combo. I dont know
if it would be an If-Then type procedure or what, but is
is certainly
beyond my capability to do. Please not that this
letter/column scenario
is repeated on row after row in my spreadsheet, and not
just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make
this
macro perfect. After the macro takes me to the derired sheet, i
would
like to view it only, then press "Enter" to return to the
original
sheet and active cell. Thak you again so very much!
Billy

this works great! thanks Festina, and all others who offered your
expert help! i really cannot say "thank you" enough!

billy
NickHK wrote:
Just as an idea, if you use hyperlinks, either in a cell or the
.FollowHyperlink method and show the Web toolbar, the use has a built in
method of go forwards and backwards through the history of selection.
Suitable ?

NickHK

"PapaDos" wrote in message
...
The problem is not which key is used.

The functionality for returning on a key pressed is not running from the
macro that select the sheets. That macro is assigning another macro to a
key
and exits.
That other macro "de-assign" the key and select the original sheet. But
nothing is forcing the execution of that macro, if you don't press that
key
when expected, the second madro will not run and the "assigned" key(s) are
not "de-assign". Their behavior will stay altered until they are pressed
and
the user will be puzzled by the actions or error messages...

If you put a button or a "goback" link on every "pointed to" sheets, it
will
be automatically re-created on a new sheet if you create it by copying an
existing sheet...

An alternative approach is to assign the "returning" macro to a key
combination manually (as you did for the first macro). You can then delete
the lines containing "Application.OnKey... " from the 2 macros...

--
Festina Lente


"Billy" wrote:

I respect your opinion. it doesnt have to be "Enter". I use Ctrl-Shift
Z to acitvate this macro. Could I use Ctrl Shift Z again to go back to
the main sheet? Or Ctrl Shift X? as i said, there are many "main
sheets", close to 100. so i would have to post the code into every main
sheet? In this case, perhaps "Enter" will not work. Thank you for your
patience.

Billy
PapaDos wrote:
I still think it is not a very good idea to alter the way Excel
responds to
keys pressed just to "go back".
Anyway, here it is, copy this into the code section of your "main"
sheet,
not in a module:



Dim sheets_stack(1) As Worksheet

Public Sub goBackToSrcSheet()
If Not sheets_stack(0) Is Nothing Then
Application.OnKey "~"
Application.OnKey "{ENTER}"
sheets_stack(1).Visible = xlSheetHidden
sheets_stack(0).Activate
Set sheets_stack(0) = Nothing
Set sheets_stack(1) = Nothing
End If
End Sub

Sub goToSheet()
On Error GoTo NO_SUCH_SHEET
Set sheets_stack(1) = Sheets(Cells(Selection.Row, "E").Value &
Selection.Column)
Set sheets_stack(0) = ActiveSheet
Application.OnKey "~", ActiveSheet.CodeName & ".goBackToSrcSheet"
Application.OnKey "{ENTER}", ActiveSheet.CodeName &
".goBackToSrcSheet"
sheets_stack(1).Visible = xlSheetVisible
sheets_stack(1).Activate
On Error GoTo 0
Exit Sub

NO_SUCH_SHEET:
Err.Clear
On Error GoTo 0
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
End Sub


--
Festina Lente


"Billy" wrote:

Well, first let me tell you that there will be many "calling" sheets
in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back
to
the keyboard would not be as fast as pressing Enter [or another key
if
need be]. therefore I think I would prefer a keystroke rather than
the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide
again
when finished viewing. Can this be accomplished also. I hate to
impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet,
but it is
not a very good way to do it. We need to assign a macro to the
ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your
sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value &
Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very
muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on
first, the
letter I would input in col. E, and then second, the
column that the
curser is in, (which could be any or all cols. from F - P)
and then,
when i invoke it, displays a corresponding sheet that
will contain a
table I will make that relates to that letter/column
combo. I dont know
if it would be an If-Then type procedure or what, but is
is certainly
beyond my capability to do. Please not that this
letter/column scenario
is repeated on row after row in my spreadsheet, and not
just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make
this
macro perfect. After the macro takes me to the derired sheet, i
would
like to view it only, then press "Enter" to return to the
original
sheet and active cell. Thak you again so very much!
Billy

LOL

My name is Luc, not not Festina...
--
Festina Lente


"Billy" wrote:

this works great! thanks Festina, and all others who offered your
expert help! i really cannot say "thank you" enough!

billy
NickHK wrote:
Just as an idea, if you use hyperlinks, either in a cell or the
.FollowHyperlink method and show the Web toolbar, the use has a built in
method of go forwards and backwards through the history of selection.
Suitable ?

NickHK

"PapaDos" wrote in message
...
The problem is not which key is used.

The functionality for returning on a key pressed is not running from the
macro that select the sheets. That macro is assigning another macro to a
key
and exits.
That other macro "de-assign" the key and select the original sheet. But
nothing is forcing the execution of that macro, if you don't press that
key
when expected, the second madro will not run and the "assigned" key(s) are
not "de-assign". Their behavior will stay altered until they are pressed
and
the user will be puzzled by the actions or error messages...

If you put a button or a "goback" link on every "pointed to" sheets, it
will
be automatically re-created on a new sheet if you create it by copying an
existing sheet...

An alternative approach is to assign the "returning" macro to a key
combination manually (as you did for the first macro). You can then delete
the lines containing "Application.OnKey... " from the 2 macros...

--
Festina Lente


"Billy" wrote:

I respect your opinion. it doesnt have to be "Enter". I use Ctrl-Shift
Z to acitvate this macro. Could I use Ctrl Shift Z again to go back to
the main sheet? Or Ctrl Shift X? as i said, there are many "main
sheets", close to 100. so i would have to post the code into every main
sheet? In this case, perhaps "Enter" will not work. Thank you for your
patience.

Billy
PapaDos wrote:
I still think it is not a very good idea to alter the way Excel
responds to
keys pressed just to "go back".
Anyway, here it is, copy this into the code section of your "main"
sheet,
not in a module:



Dim sheets_stack(1) As Worksheet

Public Sub goBackToSrcSheet()
If Not sheets_stack(0) Is Nothing Then
Application.OnKey "~"
Application.OnKey "{ENTER}"
sheets_stack(1).Visible = xlSheetHidden
sheets_stack(0).Activate
Set sheets_stack(0) = Nothing
Set sheets_stack(1) = Nothing
End If
End Sub

Sub goToSheet()
On Error GoTo NO_SUCH_SHEET
Set sheets_stack(1) = Sheets(Cells(Selection.Row, "E").Value &
Selection.Column)
Set sheets_stack(0) = ActiveSheet
Application.OnKey "~", ActiveSheet.CodeName & ".goBackToSrcSheet"
Application.OnKey "{ENTER}", ActiveSheet.CodeName &
".goBackToSrcSheet"
sheets_stack(1).Visible = xlSheetVisible
sheets_stack(1).Activate
On Error GoTo 0
Exit Sub

NO_SUCH_SHEET:
Err.Clear
On Error GoTo 0
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
End Sub


--
Festina Lente


"Billy" wrote:

Well, first let me tell you that there will be many "calling" sheets
in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back
to
the keyboard would not be as fast as pressing Enter [or another key
if
need be]. therefore I think I would prefer a keystroke rather than
the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide
again
when finished viewing. Can this be accomplished also. I hate to
impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet,
but it is
not a very good way to do it. We need to assign a macro to the
ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your
sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value &
Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very
muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on
first, the
letter I would input in col. E, and then second, the
column that the
curser is in, (which could be any or all cols. from F - P)
and then,
when i invoke it, displays a corresponding sheet that
will contain a
table I will make that relates to that letter/column
combo. I dont know
if it would be an If-Then type procedure or what, but is
is certainly
beyond my capability to do. Please not that this
letter/column scenario
is repeated on row after row in my spreadsheet, and not
just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make
this
macro perfect. After the macro takes me to the derired sheet, i
would
like to view it only, then press "Enter" to return to the
original
sheet and active cell. Thak you again so very much!
Billy

Oh, sorry then Luc. and thanks again
PapaDos wrote:
LOL

My name is Luc, not not Festina...
--
Festina Lente


"Billy" wrote:

this works great! thanks Festina, and all others who offered your
expert help! i really cannot say "thank you" enough!

billy
NickHK wrote:
Just as an idea, if you use hyperlinks, either in a cell or the
.FollowHyperlink method and show the Web toolbar, the use has a built in
method of go forwards and backwards through the history of selection.
Suitable ?

NickHK

"PapaDos" wrote in message
...
The problem is not which key is used.

The functionality for returning on a key pressed is not running from the
macro that select the sheets. That macro is assigning another macro to a
key
and exits.
That other macro "de-assign" the key and select the original sheet. But
nothing is forcing the execution of that macro, if you don't press that
key
when expected, the second madro will not run and the "assigned" key(s) are
not "de-assign". Their behavior will stay altered until they are pressed
and
the user will be puzzled by the actions or error messages...

If you put a button or a "goback" link on every "pointed to" sheets, it
will
be automatically re-created on a new sheet if you create it by copying an
existing sheet...

An alternative approach is to assign the "returning" macro to a key
combination manually (as you did for the first macro). You can then delete
the lines containing "Application.OnKey... " from the 2 macros...

--
Festina Lente


"Billy" wrote:

I respect your opinion. it doesnt have to be "Enter". I use Ctrl-Shift
Z to acitvate this macro. Could I use Ctrl Shift Z again to go back to
the main sheet? Or Ctrl Shift X? as i said, there are many "main
sheets", close to 100. so i would have to post the code into every main
sheet? In this case, perhaps "Enter" will not work. Thank you for your
patience.

Billy
PapaDos wrote:
I still think it is not a very good idea to alter the way Excel
responds to
keys pressed just to "go back".
Anyway, here it is, copy this into the code section of your "main"
sheet,
not in a module:



Dim sheets_stack(1) As Worksheet

Public Sub goBackToSrcSheet()
If Not sheets_stack(0) Is Nothing Then
Application.OnKey "~"
Application.OnKey "{ENTER}"
sheets_stack(1).Visible = xlSheetHidden
sheets_stack(0).Activate
Set sheets_stack(0) = Nothing
Set sheets_stack(1) = Nothing
End If
End Sub

Sub goToSheet()
On Error GoTo NO_SUCH_SHEET
Set sheets_stack(1) = Sheets(Cells(Selection.Row, "E").Value &
Selection.Column)
Set sheets_stack(0) = ActiveSheet
Application.OnKey "~", ActiveSheet.CodeName & ".goBackToSrcSheet"
Application.OnKey "{ENTER}", ActiveSheet.CodeName &
".goBackToSrcSheet"
sheets_stack(1).Visible = xlSheetVisible
sheets_stack(1).Activate
On Error GoTo 0
Exit Sub

NO_SUCH_SHEET:
Err.Clear
On Error GoTo 0
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
End Sub


--
Festina Lente


"Billy" wrote:

Well, first let me tell you that there will be many "calling" sheets
in
the workbook, of course only 1 active at the time. I would like to
avoid using the mouse if possible, as most of the work is keying in,
and getting the mouse, moving the cursor, selecting, and going back
to
the keyboard would not be as fast as pressing Enter [or another key
if
need be]. therefore I think I would prefer a keystroke rather than
the
mouse. Also, there will be a bunch of "goto" sheets. I would like to
have them hidden, then unhide the one selected to view, and hide
again
when finished viewing. Can this be accomplished also. I hate to
impose,
but this is way beyond my ability. You have no idea how much this is
helping me in this project!
Billy
PapaDos wrote:
I can show you how to use ENTER to go back to the calling sheet,
but it is
not a very good way to do it. We need to assign a macro to the
ENTER key and
that is done at the Application level...

A probably better way would be to add a link or a button on your
sheets to
go back.

Let me know which method you want to use...
--
Festina Lente


"Billy" wrote:


Billy wrote:
PapaDos wrote:
Sub gotoSheet()
On Error GoTo no_sheet
Sheets(Cells(Selection.Row, "E").Value &
Selection.Column).Activate
Exit Sub

no_sheet:
MsgBox "Error activating sheet: " & Cells(Selection.Row,
"E").Value &
Selection.Column
Err.Clear
On Error GoTo 0
End Sub

--
Festina Lente


"Billy" wrote:
PapaDos,

Thats works! I can make that do what i want. Thanks so very
muhcto you,
and to Tom, for all your help!!
Billy


I need a macro that sort of performs a lookup, based on
first, the
letter I would input in col. E, and then second, the
column that the
curser is in, (which could be any or all cols. from F - P)
and then,
when i invoke it, displays a corresponding sheet that
will contain a
table I will make that relates to that letter/column
combo. I dont know
if it would be an If-Then type procedure or what, but is
is certainly
beyond my capability to do. Please not that this
letter/column scenario
is repeated on row after row in my spreadsheet, and not
just one row.
Thank you very much,

Billy

I hate to ask this PapaDos, but I need one other thing to make
this
macro perfect. After the macro takes me to the derired sheet, i
would
like to view it only, then press "Enter" to return to the
original
sheet and active cell. Thak you again so very much!
Billy