Stop a user navigating certain sheets

[Sandy]

I have two worksheets, namely "Current Round" and "Current Round Detailed"
along with 6 others that are visible. What I would like to do is contain the
user to the two sheets above until a command button has been clicked.

The command button is "SaveAmendments", and becomes hidden once the attached
code is run.

I suppose the layman's way would be something like


If Worksheets("Current Round").Buttons("SaveAmendment").Visible = True Then

Disable moving to any sheet other than "Current Round" and "Current
Round Detailed" (or maybe better to just hide the other sheets?)
Prevent user closing application

End If

Your thoughts on the above are appreciated
Sandy

 

[Susan]

it all depends how savvy your users are. personally, mine wouldn't
even begin to know how to unhide a sheet. you can make a sheet hidden
with VBA (below) or using the Format Sheet Hide command from the
toolbar.

activeworkbook.worksheets("sheet1").visible = xlHidden

which can be unhidden using VBA or using the Format Sheet Unhide
command from the toolbar. or you can make a worksheet VERY hidden
(which you can only unhide by VBA or using the VB editor):

activeworkbook.worksheets("sheet1").Visible = xlVeryHidden

if i were you i would use a workbook_open macro to hide xlVeryHidden
all the sheets except the two you have listed until that command
button has been clicked. then, as part of the SameAmendments_click
macro you could unhide the other sheets:

activeworkbook.Worksheets("sheet1").Visible = True

hope i've got it right, and hope it helps you!

susan

 

[JLGWhiz]

Hi Susan, just wanted to add that the Worksheet_Activate event can also be
used in the sheets that Sandy wants to keep out of view of the user until
certain criteria is met. If it is a matter of completing certain cells with
data you could:

Private Sub Worksheet_Activate()
If IsEmpty(Worksheets(1).Cells(27, 15)) Then
Worksheets(1).Activate
End If
End sub

This would continue to send the user back to sheet 1 until something was
entered into the key cell. That does not guarantee that the data entered is
correct.

 

[Sandy]

Susan, JLGWhiz
I opted to hide sheets in the Workbook_BeforeClose and then conditionally
open them in the Workbook_Open depending on the visibility of the button in
the "current round" sheet.

Works fine.

Thank you both for your assistance
Sandy