Multiple sheets selection Error~

[kaiten_nage]

Hi, sorry to be such a rush cos I found I am in death meat within 2 more days to go before deathline, beside have a last try on here. I have a run time error 1004 while doing this... in Excel (Version 2000 (9.0.4402 SR 1) runing VB.


1) If clicked on ¡®Shift¡¯ and highlight > 1 worksheets then clicked on any cell, the Runtime Error 1004 appeared

The error is from :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'JL 19/08/02: Added for SIR-2226
If Target.Column <= 5 Then
ThisWorkbook.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
ThisWorkbook.ActiveSheet.Unprotect
End If
End Sub

However, I need this to protect the first 5 column for read only.

2) Whenever trying to save the repayment schedule onto the local desktop, the following error message is prompted and the excel will be killed off. Not able to save repayment schedule. Tested that this error message only get prompted if file is saved as the default ¡®Ms Excel 97-2000 & 5.0/95 Workbook¡¯.
If changed file type to ¡®Ms Excel Workbook¡¯ then no error is prompted.

Your expertise and lending a helping hand a much much appreciate. Any guidance and advices are welcome too. Thanks again.

Cheers,
Allan
[email protected]

[WorkHorse]

1) Hmm...Without looking too much, it seems to be a bug, or at least something MS didn't expect. When the user selects multiple sheets, Protect Sheet is disabled. Even though through code you know which sheet is active, the Protect property is disabled so trying to set it will cause an error. As long as you don't mind unselecting the user's multiple sheet selections, a simple workaround would be to ThisWorkbook.ActiveSheet.Select before setting the Protect property.

2) I don't think I have enough info to know what you are doing that causes the problem.

Hope this helps.