EXCEL : which sheet did I come from ?

[chandlm]

Can anyone point me in the right direction as to display the sheet name that I have just come from when I change sheets.

Also does anyone know if you can set permissions for a single sheet for 2 certain users, so that only the permitted user can view the sheet.

 

[SkipVought]

Hi,
The Workbook_Sheet_Deactivate event...

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

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stoffel24]

Chandlm in answer to your first query, may I suggest that you try one of the following:
While on the sheet whose name you need..
Name = Activesheet.name

If you are on another sheet...
Name = Worksheets(x).name (where x = index number of the sheet)

The answer to question2 is one I will leave for some other more clued up individual...

 

[rmikesmith]

Hello,

A while back, I put together a demo workbook addressing this issue for another Tek-Tips poster. If you would like a copy, post your email. In the meantime, I'll dust it off and makes some notes on its use.


Regards,
Mike

 

[xlbo]

The 2nd part of the query can be handled with the sheet ACTIVATE event

If you are on an NT network, you can use
mUser = environ("username&quot to get the id of the current user so within the Sheet_Activate event, you could use something like:

Private Sub Worksheet_Activate()
mUser = Environ("username&quot
Select Case mUser
Case "user1", "user2"
Exit Sub
Case Else
MsgBox "You do not have permission to use this sheet"
Sheets(sht).Select
sheets(sht).Range("a1&quot.Select
End Select
End Sub

This would need to be set up with sht declared as a public variable (string) and this code in the sheet_deactivate code of all the other worksheets:

Private Sub Worksheet_Deactivate()
sht = Me.Name
End Sub


Skip - I may be being incredibly thick but how does the code you provided work - doesn't seem to be fired when the sheet is deactivated....

Stoffel24 - unfortunately, you cannot use activesheet.name as the sheet has been DEactivated - you tyherefore have to use Me.Name as the parent object is still the sheet that has just been deactivated Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

It does on v2002 -- I have not check it out on an earlier version. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

aaaaah - doesn't work on '97 :-( Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[rmikesmith]

Geoff,

On XL 2000 Skip's solution works. I have 97 at home and will try it there. I can't see what would have changed from 97 to 2000 regarding this event procedure to allow this to work where it hadn't before.

Mike

 

[SkipVought]

Hey Guys,

I just tried it on me 97...

and it worked!
??? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

oh my life !!! How embarrassed am I ?
I put it in the sheet module accidentally rather than the workbook module - no wonder it didn't run
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Been there -- done that P-) Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rmikesmith]

Geoff,

Ok, Ok. I admit it. I initially did the same thing but didn't mention it because I was sure it was my silliness only.


Mike

 

[xlbo]

LOL LOL LOL Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[chandlm]

Thanks xlbo That was just the push I needed.

Thanks to all others that have posted a response.

Thank god for

http://www.tek-tips.com

Humbly in your shadows.

Matt