Limiting access to a worksheet

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I'm struggling with a problem to which I suspect the
solution is really simple, at least to you experts out
there. So I would really appreciate your help, please.

I have a workbook with just two sheets. Most of the action
takes place in Sheet 1, but from time to time the user
will wish to go to Sheet 2 to view the data from Sheet 1
formatted in a different manner.

However, I would like to limit the user's access to Sheet
2 to just those times when the data therein is valid.

In the code for Sheet 1 I have tried using
'Worksheets(2).Visible = XlSheetVeryHidden'
to hide Sheet 2's tab. When I'm ready to allow the user
access to Sheet 2 I then use
'Worksheets(2).Visible = XlSheetVisible'

This works, but the interface is very clunky, in that
making Sheet 2 visible again actually acivates Sheet 2 for
a fraction of a second, which looks really messy. What I
really want is just for the tab for Sheet 2 to reappear so
that the user can access Sheet 2 when he or she so wishes.

What would be really neat instead of hiding and revealing
the tab for Sheet 2 would be simply to grey it out when
access is denied. Is this possible?

Any thoughts would be much appreciated.

Regards
Andrew
 
Do you use Application.ScreenUpdating?

Application.ScreenUpdating = False
Worksheets(2).Visible = XlSheetVeryHidden'
Application.ScreenUpdating = True

Application.ScreenUpdating = False
'Worksheets(2).Visible = XlSheetVisible'
Application.ScreenUpdating = True
 
What would be really neat instead of hiding and revealing
the tab for Sheet 2 would be simply to grey it out when
access is denied. Is this possible?
Click to expand...

Likely to be even more clunky. I don't know of an Enabled property for a
worksheet.
 
Back
Top