Remember where I came from? 1

[eti3nne]

When I click a button on sheet wk0, I go to a calc sheet where I eventually click a submit button to return to the original sheet (Wk0) and cell, setting the calculated value in the cell. This works OK with a single sheet because I hard code the sheetname (Wk0) for the return path, but I now have 4 sheets: Wk0,Wk16,Wk32,Wk48.

? How do I get excel to remember the sheet I came from. I have used the following to get the sheetname...

Sourcesheet = ActiveSheet.Name

...but I don't know how/where to force excel to remember the original location (1 of 4, now) when I move between sheets so that I can return to the original source sheet.

Any ideas?

Thanks for looking.

 

[PHV]

Have you tried to play with Application.Caller ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eti3nne]

?! How do I use Application.Caller to remember the previous sheet name?

 

[combo]

You can use global variable to store sheet's name. It can be set either by the code that jumps to target sheet or use workbook level event procedure for it:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
sPreviousSheetName = Sh.Name
End Sub

combo

 

[nnett]

Sourcesheet needs to be a public variable set at the end of you button click on the original sheet, you can then retun to to your original sheet with "Sheets(SourceSheet).Select

 

[eti3nne]

Thanks nnet

Do you mean like this?

private sub fred_click()
Sourcesheet = ActiveSheet.Name
etc
etc
Public Sourcesheet As String
End sub

I get a compile error "Invalid attribute in sub or function"

 

[PHV]

Public Sourcesheet As String
Private Sub fred_click()
Sourcesheet = ActiveSheet.Name
etc
etc
End sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eti3nne]

Thanks again nnet,
In the calculation sheet I have ...

Private Sub cmdBut_Return_to_Source_Click()
Dim Sourcesheet as String

MsgBox "Sourcesheet is : " & Sourcesheet
'Return to original source sheet...
Sheets(Sourcesheet).Select
etc etc
End sub

... but this results in MsgBox showing Null value for Sourcesheet; the sourcesheet variable still seeems to not be available to the calculation sheet?

 

[SkipVought]

In VBA HELP, check out Scope, and look for Understanding Scope and Visibility.

Then look at the examples posted previously for your edification and compare with your last code post.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Get rid of the Dim Sourcesheet as String in your procedure as the variable should already be defined as Public.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eti3nne]

Thanks Skip for the pointer on Scope (will read between posts)

PHV,
Have removed that line of code. I now have...

Private Sub cmdBut_Return_to_Source_Click()
MsgBox "Sourcesheet is : " & Sourcesheet
'Return to original source sheet...
Sheets(Sourcesheet).Select
etc etc
End sub


Still get a null value for Sourcesheet...

 

[SkipVought]

Where did you assign the Sourcesheet variable the value of the sheet name?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eti3nne]

SkipVought Q."Where did you assign the Sourcesheet variable the value of the sheet name?"

A. In the source sheet code, I used:

Public Sourcesheet As String
Private Sub cmdBut_Wk0_Click()
Sourcesheet = ActiveSheet.Name
etc etc
End sub

This picks up the sheetname OK
as,
MsgBox "Sourcesheet is; " & Sourcesheet
returns Wk0 - as expected.

 

[SkipVought]

Did you actually EXECUTE that command?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eti3nne]

Skip, EXECUTE as in :

I click on a button on sheet Wk0 to jump to the calc sheet and have a msgbox confirm the value of Sourcesheet (Wk0), before I actually jump.

The value doesn't get transfered/ become available upon arrival at the calc sheet.

 

[SkipVought]

You must put the Public declaration in a MODULE, not in your sheet object.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eti3nne]

Skip, Add patience to that nuance! *

Thanks to all for your help. Took a while to get there, but I think I've learned more in the process.

Thanks again.