Protecting sheets but allowing them to be accessible...

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


"Colin Hayes" wrote in message
...
In article , Trevor Shuttleworth
writes
Colin

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

Regards

Trevor


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







"Colin Hayes" wrote in message
...
In article , Colin Hayes
writes
In article , Trevor Shuttleworth
writes
Colin

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


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


"Colin Hayes" wrote in message
...
In article , Trevor
Shuttleworth
writes
Colin

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

Regards

Trevor

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



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")

In article , Colin Hayes
writes
In article , Trevor Shuttleworth
writes
Colin

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

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









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







"Colin Hayes" wrote in message
...
In article , Colin Hayes
writes
In article , Trevor Shuttleworth
writes
Colin

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


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


"Colin Hayes" wrote in message
...
In article , Trevor
Shuttleworth
writes
Colin

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

Regards

Trevor

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



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")

In article , Trevor Shuttleworth
writes
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


"Colin Hayes" wrote in message
...
In article , Trevor Shuttleworth
writes
Colin

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

Regards

Trevor


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







"Colin Hayes" wrote in message
...
In article , Colin Hayes
writes
In article , Trevor
Shuttleworth
writes
Colin

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


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


"Colin Hayes" wrote in message
...
In article , Trevor
Shuttleworth
writes
Colin

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

Regards

Trevor

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



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")














Are you aware that we also buy CDs, Vinyl and DVDs? Send your lists of
unwanted items to 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