Protecting sheets but allowing them to be accessible...

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

I have a workbook of 13 sheets. The first sheet is an entry page where
information is entered across the year. It isn't protected as people
need to have access to it to make their entries.

The other 12 sheets are one for each month of the year. They fill up
month by month as entries are made on sheet 1. They hold summaries of
the information entered on the first page. People don't need direct
access to them.

Here's the problem. If I protect sheets 2-13 , then when I enter
information on sheet 1 I get an access error saying that they cannot be
overwritten .

However , as nobody need to have direct access to the information on
these pages , I would rather protect them.

Is it possible to update these sheets when I'm sending data from sheet
one , but still have the sheets protected so that the data and coding
etc. cannot be overwritten by accessing them via the tabs?

Hope you can help.


Best Wishes


Drno
 
Colin

you don't say how you are updating sheets 2 to 13. If you are using VBA
code you could try the approach:

Unprotect ...
' your code
Protect ...

Look up the syntax for Unprotect and Protect in the Help or just record the
macro.

I often protect sheets with a blank password ... just to protect the formula
but to make it easy for me, or anyone else, to access the data when it needs
to be modified.

That would look something like:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
' your code
ActiveSheet.Unprotect

Regards

Trevor
 
Trevor Shuttleworth said:
Colin

you don't say how you are updating sheets 2 to 13. If you are using VBA
code you could try the approach:
Click to expand...

HI

Thanks for getting back.

I do use some VBA code to do the transfer of information from sheet one
to the correct month in 2 to 13.

If I'm to build in unprotect / protect into the code it would need to be
seamless and with no sign that it was doing it. That would be perfect.
I'm imaging that it would unprotect at the very beginning and the
protect at the very end of course.

I did try to put some code in , but it gives pop ups asking for
passwords etc. and brings the whole thing to a halt. Here's the VBA I
use. My password is '12071956' - any ideas how I could put in code to
protect and unprotect gracefully?


Sub copy()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Dim monthdate As String
monthdate = Format(Cells(50, 2), "mmmm")
Lr = LastRow(Sheets(monthdate)) + 1
Set sourceRange = Sheets("Receipt ").Rows("50:50")
Set destrange = Sheets(monthdate).Rows(Lr). _
Resize(sourceRange.Rows.Count)
destrange.Value = sourceRange.Value

'Print Receipt

ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True

End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Sub test()
Dim monthdate As String
Dim a As Integer
For a = 1 To 12
monthdate = Format(DateSerial(2003, a, 1), "mmmm")
MsgBox monthdate
Next
End Sub

' Format(Date, "dd-mm-yy")


Thanks

Drno
 
Colin

:
Sheets(monthdate).Unprotect Password:="12071956"
destrange.Value = sourceRange.Value
Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True,
Contents:=True, Scenarios:=True
:

Regards

Trevor
 
Trevor Shuttleworth said:
Colin

:
Sheets(monthdate).Unprotect Password:="12071956"
destrange.Value = sourceRange.Value
Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True,
Contents:=True, Scenarios:=True
:

Regards

Trevor
Click to expand...

Hi Trevor

OK thanks for that. I see the logic of what you suggest. I couldn't make
it work however , as I'm getting red errors in the coding. Maybe I'm
putting the code in the wrong place in the routine. You're plainly
better at this than I am - where would you put these lines in the code
so that they protect / unprotect at the right time and don't cause
errors?


Best Wishes


Colin
 
Colin

looks like you are suffering from "line wrap". The last line should all be
on one line.

Alternatively:

:
Sheets(monthdate).Unprotect Password:="12071956"
destrange.Value = sourceRange.Value
Sheets(monthdate).Protect _
Password:="12071956", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True
:

Note that the "_" is a line continuation flag. Makes for easier reading and
gets over the problem you have experienced.

So, your code would look like:

Sub copy()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Dim monthdate As String
monthdate = Format(Cells(50, 2), "mmmm")
Lr = LastRow(Sheets(monthdate)) + 1
Set sourceRange = Sheets("Receipt ").Rows("50:50")
Set destrange = Sheets(monthdate).Rows(Lr). _
Resize(sourceRange.Rows.Count)

Sheets(monthdate).Unprotect _
Password:="12071956"
destrange.Value = sourceRange.Value
Sheets(monthdate).Protect _
Password:="12071956", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'Print Receipt

ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True

End Sub

Regards

Trevor
 
Trevor Shuttleworth said:
Colin

looks like you are suffering from "line wrap". The last line should all be
on one line.
Click to expand...

Hi Trevor

OK that works a treat now. Thanks - I'm grateful for your trouble and
for your expertise. You've really helped me out.


Best Wishes


Colin
 
Trevor

BTW , do you know if it's possible to hide a tab or tabs in a workbook
from view?

Maybe I could run a macro from a key press to hide / unhide certain
worksheet tabs in a workbook.


Best Wishes


Colin



Hi Trevor

OK that works a treat now. Thanks - I'm grateful for your trouble and
for your expertise. You've really helped me out.


Best Wishes


Colin




Alternatively:

:
Sheets(monthdate).Unprotect Password:="12071956"
destrange.Value = sourceRange.Value
Sheets(monthdate).Protect _
Password:="12071956", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True
:

Note that the "_" is a line continuation flag. Makes for easier reading and
gets over the problem you have experienced.

So, your code would look like:

Sub copy()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Dim monthdate As String
monthdate = Format(Cells(50, 2), "mmmm")
Lr = LastRow(Sheets(monthdate)) + 1
Set sourceRange = Sheets("Receipt ").Rows("50:50")
Set destrange = Sheets(monthdate).Rows(Lr). _
Resize(sourceRange.Rows.Count)

Sheets(monthdate).Unprotect _
Password:="12071956"
destrange.Value = sourceRange.Value
Sheets(monthdate).Protect _
Password:="12071956", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'Print Receipt

ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True

End Sub

Regards

Trevor
Click to expand...
Click to expand...
 
Colin

Sheets("Sheet3").Visible = True
MsgBox "now you see it"
Sheets("Sheet3").Visible = False
MsgBox "now you don't"

Regards

Trevor


Colin Hayes said:
Trevor

BTW , do you know if it's possible to hide a tab or tabs in a workbook
from view?

Maybe I could run a macro from a key press to hide / unhide certain
worksheet tabs in a workbook.


Best Wishes


Colin
Click to expand...
 
Trevor Shuttleworth said:
Colin

Sheets("Sheet3").Visible = True
MsgBox "now you see it"
Sheets("Sheet3").Visible = False
MsgBox "now you don't"

Regards

Trevor
Click to expand...

Hi Trevor

Thanks for that. It works a treat - up to a point!

I made two macros - one to hide the tabs :


Sub Hide_Tabs()
'
' Hide_Tabs Macro

' Keyboard Shortcut: Ctrl+q
'
Sheets("January").Visible = False
Sheets("February").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
Sheets("May").Visible = False
Sheets("June").Visible = False
Sheets("July").Visible = False
Sheets("August").Visible = False
Sheets("September").Visible = False
Sheets("October").Visible = False
Sheets("November").Visible = False
Sheets("December").Visible = False

End Sub


and one to Unhide them :



Sub Unhide_Tabs()
'
' Unhide_Tabs Macro
'
' Keyboard Shortcut: Ctrl+a

Sheets("January").Visible = True
Sheets("February").Visible = True
Sheets("March").Visible = True
Sheets("April").Visible = True
Sheets("May").Visible = True
Sheets("June").Visible = True
Sheets("July").Visible = True
Sheets("August").Visible = True
Sheets("September").Visible = True
Sheets("October").Visible = True
Sheets("November").Visible = True
Sheets("December").Visible = True

'
End Sub



The Hide macro works perfectly and hides all the named sheets. When I
first ran the unhide macro it worked and showed them all again.

I saved and closed excel.

Now when i open the program again and run the Unhide macro , it doesn't
work! All my sheets are now hidden and refuse to be revealed. I can't
see anything wrong with the macro - it's simple enough - any ideas?

Excel can be very mysterious at times!


Best Wishes


Colin
 
Works for me. Sure you've got macros enabled ?

Have you tried running it through Tools | Macro | Macros | Run ? Does that
work ? Do you get any error message ?

I didn't try your short cuts. Ctrl-A normally selects all the cells.

Regards

Trevor
 
Hi Trevor

OK , panic over!

I managed to fix the problem. I'd assigned ctrl a to the Unhide macro. I
discovered looking through other macros that I'd actually already
assigned this to another macro. They were plainly clashing and once I
re-assigned them it all works beautifully....

Thanks again


Best Wishes


Colin
 
Trevor Shuttleworth said:
Works for me. Sure you've got macros enabled ?

Have you tried running it through Tools | Macro | Macros | Run ? Does that
work ? Do you get any error message ?

I didn't try your short cuts. Ctrl-A normally selects all the cells.

Regards

Trevor
Click to expand...

Hi Trevor

OK , panic over!

I managed to fix the problem. I'd assigned ctrl a to the Unhide macro. I
discovered looking through other macros that I'd actually already
assigned this to another macro. They were plainly clashing and once I
re-assigned them it all works beautifully....

Thanks again


Best Wishes


Colin
Colin Hayes said:
Trevor Shuttleworth said:
Colin

Sheets("Sheet3").Visible = True
MsgBox "now you see it"
Sheets("Sheet3").Visible = False
MsgBox "now you don't"

Regards

Trevor
Click to expand...

Hi Trevor

Thanks for that. It works a treat - up to a point!

I made two macros - one to hide the tabs :


Sub Hide_Tabs()
'
' Hide_Tabs Macro

' Keyboard Shortcut: Ctrl+q
'
Sheets("January").Visible = False
Sheets("February").Visible = False
Sheets("March").Visible = False
Sheets("April").Visible = False
Sheets("May").Visible = False
Sheets("June").Visible = False
Sheets("July").Visible = False
Sheets("August").Visible = False
Sheets("September").Visible = False
Sheets("October").Visible = False
Sheets("November").Visible = False
Sheets("December").Visible = False

End Sub


and one to Unhide them :



Sub Unhide_Tabs()
'
' Unhide_Tabs Macro
'
' Keyboard Shortcut: Ctrl+a

Sheets("January").Visible = True
Sheets("February").Visible = True
Sheets("March").Visible = True
Sheets("April").Visible = True
Sheets("May").Visible = True
Sheets("June").Visible = True
Sheets("July").Visible = True
Sheets("August").Visible = True
Sheets("September").Visible = True
Sheets("October").Visible = True
Sheets("November").Visible = True
Sheets("December").Visible = True

'
End Sub



The Hide macro works perfectly and hides all the named sheets. When I
first ran the unhide macro it worked and showed them all again.

I saved and closed excel.

Now when i open the program again and run the Unhide macro , it doesn't
work! All my sheets are now hidden and refuse to be revealed. I can't
see anything wrong with the macro - it's simple enough - any ideas?

Excel can be very mysterious at times!


Best Wishes


Colin








Are you aware that we also buy CDs, Vinyl and DVDs? Send your lists of
unwanted items to (e-mail address removed) and we’ll quote you a price…


You can browse and buy direct from my full list of items at these addresses :


http://www.chayesmusic.com

or :

http://www.netsoundsmusic.com/chayes

or:

http://chayes.musicstack.com



To DOWNLOAD the full catalogue click here :


http://www.chayes.demon.co.uk/chayes_full_catalogue.exe



Best Wishes ,

Colin Hayes.



TEL / FAX : (UK) (0)208 804 9181
Click to expand...
Click to expand...
 
Back
Top