urgent, on excel programming

[shirley]

i need urgent help. i want to protect a column from being over-written
but at the same time, i want to be able to insert rows into the colum
and be able to add values in. how do i go about doing it?

btw, i'm working on excel 2000 and i know only 2002 has the ability t
insert new rows in a protected column but what about excel 2000

 

[Rob van Gelder]

Custom written "Insert Row" button. The code behind it unprotects, inserts
row, re-protects.

 

[shirley]

i understand what you mean but am not sure how to code it. could u hel
me out with it

 

[Rob van Gelder]

Insert a Form button and use this macro.

Sub Button1_Click()
ActiveSheet.Unprotect
ActiveCell.EntireRow.Insert
ActiveSheet.Protect
End Sub

 

[shirley]

Hi, thx for ur help. the code works. however, i face a problem.
wat happened was i added in this line
ActiveCell.Locked = False
so that i can edit the cell when i add a new line in.
but when i set the whole spreadsheet as protected and i run the
spreadsheet for the first time, if i press the button i get this error
msg, "unable to set the Locked property of the range class". what
should i do?

i need the spreadsheet to be protected right from the start when the
spreadsheet is opened.

pls advice me. thx

 

[Rob van Gelder]

ActiveCell.Locked should be run before you protect the worksheet.

 

[shirley]

below is what i tried out. the idea is to protect the spreadsheet righ
from the time it is opened. when the user inserts a new line, he click
on button 1. he is able to insert data into the new row but not to th
current rows. next, he clicks button 2. when he clicks button 2, i wan
to be able to save his record and at the same time, disallow him t
change certain cells. currently, i'm unable to achieve that. pls advic
me where i've gone wrong. also, the prob with this code is that anyon
who knows excel can jus go to the "protection" and unprotect th
worksheet. it defeats the purpose of locking up cells.


Sub Auto_Open()
'ActiveSheet.Protect
ActiveSheet.Protect

End Sub

Sub Button1_Click()
'
' Button1_Click Macro
' Macro recorded 09/02/2004 by Bates Singapore Pte Ltd
'
ActiveSheet.unprotect:="1234"
ActiveCell.EntireRow.insert
ActiveCell.EntireRow.Locked = False
ActiveSheet.Protect password:="1234"
'
End Sub

Sub Button2_Click()
'
' Button2_Click Macro
' Macro recorded 09/02/2004 by Bates Singapore Pte Ltd
'
ActiveCell.EntireRow.Locked = True
ActiveSheet.Protect

'
End Su