SaveProtect&Close and Lockcells

[NeedHelp69]

I have two macros that work fine individually, but I can't seem to put the Lock Cells one into the protectsaveclose one and get it to work. What I have is a worksheet where I have a button set up doing the ProtectSave&Close which managers use and when we want to send it to people to use, however there are these Ranges where we don't want people to edit data because they've been abusing this worksheet. So if I do the locked cells and then manually protect the sheet, save&close, when I open it up those ranges are still locked, however if I put the bottom macro into the top macro, either when I re-open the file I can type into those ranges, or depending where I put it I get a compilier error that tells me "Unable to set the locked property of the range class". Any help would be greatly appreciated.




Sub ProtectSaveClose()
'
' ProtectSaveClose Macro
' Macro recorded 3/4/2005 by XXXXX
'

'

Range("A1").Select
Dim wSheet As Worksheet
For Each wSheet In Worksheets
If wSheet.ProtectContents = False Then
wSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
End If
Next wSheet
For Each wSheet In Worksheets
If wSheet.ProtectContents = False Then
UserForm3.Show
End If
Next wSheet
Application.DisplayAlerts = False
ThisWorkbook.Close savechanges:=True


End Sub


Sub Lock_Cells()
'
' Lock_Cells Macro
' Macro recorded 7/28/2006 by XXXXX
'
' Keyboard Shortcut: Ctrl+c
'
Range("Rates").Select
ActiveWindow.SmallScroll Down:=9
Range("Rates,Factors_Applied,Our_Cost").Select
Range("Our_Cost").Activate
Selection.Locked = True
'Selection.ReadOnly
Selection.FormulaHidden = False

End Sub

[[email protected]]

Do you have an example of how you "put the bottom macro into the top
macro" because there is more than one way and depending on how you do
it depends on the behaviour of some macros
NeedHelp69 wrote:

> I have two macros that work fine individually, but I can't seem to put
> the Lock Cells one into the protectsaveclose one and get it to work.
> What I have is a worksheet where I have a button set up doing the
> ProtectSave&Close which managers use and when we want to send it to
> people to use, however there are these Ranges where we don't want
> people to edit data because they've been abusing this worksheet. So if
> I do the locked cells and then manually protect the sheet, save&close,
> when I open it up those ranges are still locked, however if I put the
> bottom macro into the top macro, either when I re-open the file I can
> type into those ranges, or depending where I put it I get a compilier
> error that tells me "Unable to set the locked property of the range
> class". Any help would be greatly appreciated.
>
>
>
>
> Sub ProtectSaveClose()
> '
> ' ProtectSaveClose Macro
> ' Macro recorded 3/4/2005 by XXXXX
> '
>
> '
>
> Range("A1").Select
> Dim wSheet As Worksheet
> For Each wSheet In Worksheets
> If wSheet.ProtectContents = False Then
> wSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
> End If
> Next wSheet
> For Each wSheet In Worksheets
> If wSheet.ProtectContents = False Then
> UserForm3.Show
> End If
> Next wSheet
> Application.DisplayAlerts = False
> ThisWorkbook.Close savechanges:=True
>
>
> End Sub
>
>
> Sub Lock_Cells()
> '
> ' Lock_Cells Macro
> ' Macro recorded 7/28/2006 by XXXXX
> '
> ' Keyboard Shortcut: Ctrl+c
> '
> Range("Rates").Select
> ActiveWindow.SmallScroll Down:=9
> Range("Rates,Factors_Applied,Our_Cost").Select
> Range("Our_Cost").Activate
> Selection.Locked = True
> 'Selection.ReadOnly
> Selection.FormulaHidden = False
>
> End Sub
>
>
> --
> NeedHelp69
> ------------------------------------------------------------------------
> NeedHelp69's Profile: http://www.excelforum.com/member.php...o&userid=36959
> View this thread: http://www.excelforum.com/showthread...hreadid=566794

[NeedHelp69]

Well I've tryed it a few different ways manually, now both of these run however when I open the sheets the cells are no longer locked, maybe I need to use a different code to lock the cells, I don't know?:
Sub ProtectSaveClose()
> '
> ' ProtectSaveClose Macro
> ' Macro recorded 3/4/2005 by XXXXX
> '
>
> '
>Range("Rates").Select
> ActiveWindow.SmallScroll Down:=9
> Range("Rates,Factors_Applied,Our_Cost").Select
> Range("Our_Cost").Activate
> Selection.Locked = True
> 'Selection.ReadOnly
> Selection.FormulaHidden = False
> Range("A1").Select
> Dim wSheet As Worksheet
> For Each wSheet In Worksheets
> If wSheet.ProtectContents = False Then
> wSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
> End If
> Next wSheet
> For Each wSheet In Worksheets
> If wSheet.ProtectContents = False Then
> UserForm3.Show
> End If
> Next wSheet
> Application.DisplayAlerts = False
> ThisWorkbook.Close savechanges:=True
>
>
> End Sub

and I also tryed:

Sub ProtectSaveClose()
> '
> ' ProtectSaveClose Macro
> ' Macro recorded 3/4/2005 by XXXXX
> '
>
> '
>Call SubLockedCells

> Range("A1").Select
> Dim wSheet As Worksheet
> For Each wSheet In Worksheets
> If wSheet.ProtectContents = False Then
> wSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
> End If
> Next wSheet
> For Each wSheet In Worksheets
> If wSheet.ProtectContents = False Then
> UserForm3.Show
> End If
> Next wSheet
> Application.DisplayAlerts = False
> ThisWorkbook.Close savechanges:=True
>
>
> End Sub
>
>
> Sub Lock_Cells()
> '
> ' Lock_Cells Macro
> ' Macro recorded 7/28/2006 by XXXXX
> '
> ' Keyboard Shortcut: Ctrl+c
> '
> Range("Rates").Select
> ActiveWindow.SmallScroll Down:=9
> Range("Rates,Factors_Applied,Our_Cost").Select
> Range("Our_Cost").Activate
> Selection.Locked = True
> 'Selection.ReadOnly
> Selection.FormulaHidden = False
>
> End Sub