A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Protecting sheets but allowing them to be accessible...



 
 
Thread Tools Display Modes
  #1  
Old August 24th, 2004, 10:22 PM
Colin Hayes
external usenet poster
 
Posts: n/a
Default Protecting sheets but allowing them to be accessible...

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
  #2  
Old August 24th, 2004, 10:51 PM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default

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


"Colin Hayes" wrote in message
...
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



  #3  
Old August 25th, 2004, 03:33 AM
Colin Hayes
external usenet poster
 
Posts: n/a
Default

In article , Trevor Shuttleworth
writes
Colin

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


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
  #4  
Old August 25th, 2004, 08:09 AM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default

Colin

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

Regards

Trevor


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

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


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



  #5  
Old August 25th, 2004, 01:54 PM
Colin Hayes
external usenet poster
 
Posts: n/a
Default

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

  #6  
Old August 25th, 2004, 07:45 PM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default

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


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



  #7  
Old August 26th, 2004, 01:21 AM
Colin Hayes
external usenet poster
 
Posts: n/a
Default

In article , Trevor Shuttleworth
writes
Colin

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


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




  #8  
Old August 26th, 2004, 07:03 PM
Colin Hayes
external usenet poster
 
Posts: n/a
Default

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





  #9  
Old August 26th, 2004, 08:35 PM
Trevor Shuttleworth
external usenet poster
 
Posts: n/a
Default

Colin

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

Regards

Trevor


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







  #10  
Old August 26th, 2004, 11:15 PM
Colin Hayes
external usenet poster
 
Posts: n/a
Default

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









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protecting Sheets within a workbook [email protected] Worksheet Functions 1 August 2nd, 2004 12:04 AM
Hi! Urgent pls: Protecting sheets so macros/buttons still work? StargateFanFromWork General Discussion 7 July 3rd, 2004 02:25 PM
protecting sheets steve Worksheet Functions 6 March 6th, 2004 10:18 PM
Password protecting multiple sheets Allison Worksheet Functions 1 February 27th, 2004 03:15 AM
Protecting Selected Sheets Steve Klenner Worksheet Functions 1 January 22nd, 2004 10:55 PM


All times are GMT +1. The time now is 02:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.