urgent, on excel programming
 

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

--
Message posted from http://www.ExcelForum.com

urgent, on excel programming
 

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"shirley " wrote in message
...
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 column
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 to
insert new rows in a protected column but what about excel 2000?


---
Message posted from http://www.ExcelForum.com/

urgent, on excel programming
 

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

--
Message posted from http://www.ExcelForum.com

urgent, on excel programming
 

Insert a Form button and use this macro.

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"shirley " wrote in message
...
i understand what you mean but am not sure how to code it. could u help
me out with it?


---
Message posted from http://www.ExcelForum.com/

urgent, on excel programming
 

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


---
Message posted from http://www.ExcelForum.com/

urgent, on excel programming
 

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


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


---
Message posted from http://www.ExcelForum.com/

urgent, on excel programming
 

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

--
Message posted from http://www.ExcelForum.com