ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Unable to set the Locked property of the range class (https://www.excelbanter.com/excel-programming/302458-unable-set-locked-property-range-class.html)

Stuart[_5_]

Unable to set the Locked property of the range class
 
Here's the code:

Option Explicit
Private Sub Workbook_Open()

Dim i As Integer, C As Range
With Workbooks("MasterOrder.xls").Worksheets("Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
i = .Range("I10").Value
.Range("I10").Value = i + 1
For Each C In .Range("A1:N61")
With C
If Not C.Interior.ColorIndex = 34 Then
C.Locked = True
End If
End With
Next
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With
End Sub

Q1: why that error message please?
Q2: can I improve the code please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004



Ron de Bruin

Unable to set the Locked property of the range class
 
Try this Stuart

For Each C In .Range("A1:N61")
If C.Interior.ColorIndex < 34 Then
C.Locked = True
End If
Next


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stuart" wrote in message ...
Here's the code:

Option Explicit
Private Sub Workbook_Open()

Dim i As Integer, C As Range
With Workbooks("MasterOrder.xls").Worksheets("Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
i = .Range("I10").Value
.Range("I10").Value = i + 1
For Each C In .Range("A1:N61")
With C
If Not C.Interior.ColorIndex = 34 Then
C.Locked = True
End If
End With
Next
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With
End Sub

Q1: why that error message please?
Q2: can I improve the code please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004





Stuart[_5_]

Unable to set the Locked property of the range class
 
Ron,

Manythanks ....but same error message.

BTW this is Excel 2000 under Win 2K.

Regards and thanks.

"Ron de Bruin" wrote in message
...
Try this Stuart

For Each C In .Range("A1:N61")
If C.Interior.ColorIndex < 34 Then
C.Locked = True
End If
Next


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stuart" wrote in message

...
Here's the code:

Option Explicit
Private Sub Workbook_Open()

Dim i As Integer, C As Range
With Workbooks("MasterOrder.xls").Worksheets("Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
i = .Range("I10").Value
.Range("I10").Value = i + 1
For Each C In .Range("A1:N61")
With C
If Not C.Interior.ColorIndex = 34 Then
C.Locked = True
End If
End With
Next
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With
End Sub

Q1: why that error message please?
Q2: can I improve the code please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004



Stuart[_5_]

Unable to set the Locked property of the range class
 
It seems to run, but then fail on the same cell each time,
namely "K12". This cell is merged with "L12".

Could merged cells be the problem?

Regards.

"Ron de Bruin" wrote in message
...
Try this Stuart

For Each C In .Range("A1:N61")
If C.Interior.ColorIndex < 34 Then
C.Locked = True
End If
Next


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stuart" wrote in message

...
Here's the code:

Option Explicit
Private Sub Workbook_Open()

Dim i As Integer, C As Range
With Workbooks("MasterOrder.xls").Worksheets("Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
i = .Range("I10").Value
.Range("I10").Value = i + 1
For Each C In .Range("A1:N61")
With C
If Not C.Interior.ColorIndex = 34 Then
C.Locked = True
End If
End With
Next
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With
End Sub

Q1: why that error message please?
Q2: can I improve the code please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004



Ron de Bruin

Unable to set the Locked property of the range class
 
Stuart

Yes this is your problem

Don't Merge cells, Always trouble

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stuart" wrote in message ...
It seems to run, but then fail on the same cell each time,
namely "K12". This cell is merged with "L12".

Could merged cells be the problem?

Regards.

"Ron de Bruin" wrote in message
...
Try this Stuart

For Each C In .Range("A1:N61")
If C.Interior.ColorIndex < 34 Then
C.Locked = True
End If
Next


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stuart" wrote in message

...
Here's the code:

Option Explicit
Private Sub Workbook_Open()

Dim i As Integer, C As Range
With Workbooks("MasterOrder.xls").Worksheets("Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
i = .Range("I10").Value
.Range("I10").Value = i + 1
For Each C In .Range("A1:N61")
With C
If Not C.Interior.ColorIndex = 34 Then
C.Locked = True
End If
End With
Next
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With
End Sub

Q1: why that error message please?
Q2: can I improve the code please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004





Stuart[_5_]

Unable to set the Locked property of the range class
 
Many thanks, and for the advice.
Regards.

"Ron de Bruin" wrote in message
...
Stuart

Yes this is your problem

Don't Merge cells, Always trouble

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stuart" wrote in message

...
It seems to run, but then fail on the same cell each time,
namely "K12". This cell is merged with "L12".

Could merged cells be the problem?

Regards.

"Ron de Bruin" wrote in message
...
Try this Stuart

For Each C In .Range("A1:N61")
If C.Interior.ColorIndex < 34 Then
C.Locked = True
End If
Next


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stuart" wrote in message

...
Here's the code:

Option Explicit
Private Sub Workbook_Open()

Dim i As Integer, C As Range
With Workbooks("MasterOrder.xls").Worksheets("Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
i = .Range("I10").Value
.Range("I10").Value = i + 1
For Each C In .Range("A1:N61")
With C
If Not C.Interior.ColorIndex = 34 Then
C.Locked = True
End If
End With
Next
.Protect Password:="SGB"
.EnableSelection = xlUnlockedCells
End With
End Sub

Q1: why that error message please?
Q2: can I improve the code please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004




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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com