Read only - Password entered conditions :)

[pedie]

If I code the book to hide sheet1 when I close the sheet, I want the code not to show sheet1 when the workbook is opened in read only mode but show sheet1 only when users enter the book by entering the password.

Not sure if this is possible

Thanks for helping!

 

[pedie]

Is this possible?

Sorry for bumping!

 

[VoG]

Maybe this in the ThisWorkbook module

Private Sub Workbook_Open()
If Me.ReadOnly Then Sheets("Sheet1").Visible = xlSheetVisible
End Sub

 

[pedie]

I am using it this way...& it does not work...
I mean when I open in read only it does not show sheet1, however when i log in entering the password it does not show sheet1

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Sheet1.Visible = xlSheetVeryHidden
End Sub
Private Sub Workbook_Open()
  If Me.ReadOnly Then Sheets("Sheet1").Visible = xlSheetVisible
End Sub

 

[gardnertoo]

Could be this: in order for the VeryHidden to take effect, you have to save the sheet. But it's read-only, so it can't save. Not sure off the top of my head how to get around that.

 

[pedie]

Okay. lemmi try again..thanks.

 

[gardnertoo]

Try this instead:

Private Sub Workbook_Open()
  If ActiveWorkbook.ReadOnly = True Then Sheets("Sheet1").Visible = xlSheetVeryHidden
End Sub

This only acts on the Workbook Open transition, so avoids the "can't save with sheet VeryHidden while workbook is read-only" issue. Also, the decision to hide or not hide was written backwards (if read-only, then visible should be NO, not YES)

The code above works in my test on Excel 2003, no idea about '07 or '10

 

[pedie]

Vog, if you're still around please advice again.
I tried by hiding the sheet first then opening in read only....I also tried by keeping sheet1 visible but still code does not impact the sheet from staying hidden even when i enter in read only mode....

Private Sub Workbook_Open()
  If Me.ReadOnly Then Sheets("Sheet1").Visible = xlSheetVisible
End Sub

I also tried this way...but does not hide sheet1 when i open in read only mode....if you guys have some idea bout this pls help!!!
Private Sub Workbook_Open()
  If Me.ReadOnly Then Sheets("Sheet1").Visible = False
  
End Sub

 

[meldoc]

Hello Gardnertoo.

That method works for me.

 

[pedie]

Gardnertoo, thank you so much! this worked perfectly. I think we cross posted I didn't recognize it untill Brian commented on ur code!

Thanks Brain, once again thank you Gardnertoo!

Hey Vog! thanks to you too!

Have a GREAT Weekend guys!