Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making certain cells editable after protecting the sheet
Hi,
I have a Excel VBA application where on click of a command button ne cells are inserted at predefined positions and the cells below ar pushed down. I want to apply protection on the sheet such that the row that are present earlier (i.e. before button click) are uneditable an the new row that is inserted on each button click is only editable. I know that this is possible when one knows the rows that are to b marked as editable by ToolsProtectionAllow Users to Edit ranges. Is there a way to do this programatically, such that the new ro inserted is only editable and the remaining ones are still uneditable? Thanks, Samee -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making certain cells editable after protecting the sheet
Sameer,
Here is one approach. The code below will insert a new row and unlock it. It will remember what row was previously inserted (using a defined Name) so that when the next row is inserted it will lock the previously inserted row. Troy ================================================== === Sub Master() 'Logic to choose what RowNumber to insert next goes below... 'For example: Choose Row10. subInsertRow lngRow:=10 'For example: Choose Row7. subInsertRow lngRow:=7 'For example: Choose Row14. subInsertRow lngRow:=14 End Sub Sub subInsertRow(lngRow As Long) Dim sName As String Dim sRowPrev As String Dim lngRowPrev As Long 'Unprotect the sheet. ActiveSheet.Unprotect '---Lock the previously inserted row. sName = ActiveSheet.Name & "!nLastRowInserted" If fcnNameExists(sName) Then sRowPrev = ThisWorkbook.Names(sName).RefersTo If Len(sRowPrev) 1 Then sRowPrev = Mid(sRowPrev, 2, Len(sRowPrev)) If IsNumeric(sRowPrev) Then lngRowPrev = CLng(sRowPrev) If lngRowPrev 0 Then ActiveSheet.Rows(lngRowPrev).EntireRow.Locked = True End If End If End If End If 'Insert a new row. ActiveSheet.Rows(lngRow).EntireRow.Insert 'Change the entire row to Unlocked. ActiveSheet.Rows(lngRow).EntireRow.Locked = False 'Save the number of the row that was inserted. sName = ActiveSheet.Name & "!nLastRowInserted" ThisWorkbook.Names.Add Name:=sName, RefersTo:="=" & lngRow 'Protect the sheet. ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Function fcnNameExists(sName As String) As Boolean Dim lngLen As Long On Error Resume Next lngLen = Len(ThisWorkbook.Names(sName).Name) fcnNameExists = (Err.Number = 0) On Error GoTo 0 End Function ================================================== === Note: If you have a password on the sheet protection, you will need to change: ActiveSheet.Unprotect -to- ActiveSheet.Unprotect Password:="myPassword" "sameerce " wrote in message ... Hi, I have a Excel VBA application where on click of a command button new cells are inserted at predefined positions and the cells below are pushed down. I want to apply protection on the sheet such that the rows that are present earlier (i.e. before button click) are uneditable and the new row that is inserted on each button click is only editable. I know that this is possible when one knows the rows that are to be marked as editable by ToolsProtectionAllow Users to Edit ranges. Is there a way to do this programatically, such that the new row inserted is only editable and the remaining ones are still uneditable? Thanks, Sameer --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting formulas without protecting sheet so grouping still wor | Excel Discussion (Misc queries) | |||
Locking Cells ... Without Protecting Sheet | Excel Discussion (Misc queries) | |||
Locking Cells ... Without Protecting Sheet | Excel Discussion (Misc queries) | |||
Lock cells without protecting sheet? | Excel Worksheet Functions | |||
Need ability to leave certain cells of an worksheet editable. | Excel Discussion (Misc queries) |