Help !!! Deleting rows in protected document ?!?
 

i locked certain cells in my excel worksheet ... and for the locking to work
.... i need to protect the document ...

in the protect options ... i allowed deletion of rows ...

but then ... when i try to delete after protecting the document ... it will
say "cannot delete rows with locked cell"

anyone knows how to resolve ?!?!?!?!?!

Hi

I use a procedure which makes various adjustments on protected sheet. The
procedure is started by user from button on one worksheet, but the same code
can be implemented into workbooks Open event too.

On worksheet with data, in one column the user can mark rows to be deleted.
Whenever the adjustments procedure is running, all marked rows are deleted.
The example of code is below:
-------
Public Sub AdjustTables()

' remove sheets protection
Sheets("Sheetname").Unprotect Password:="password"
...
' Adjusting table MyTable
...
' Reading named constants
...
varDate0 = [Date0]
varFix = [Fix]
varHeaderRows = [HeaderRows]
varEntries=[Entries]
...
Worksheets("Sheetname").Activate

' sorting table
...
' removing rows marked for deleting, counting rows to be moved to
arvhive
' rows with is TRUE in column N are deleted
i = 1
varArhivate = 0
Do Until i varEntries
If Worksheets("Sheetname").Range("N" & (varHeaderRows + i)) Then
Worksheets("Sheetname").Rows((varHeaderRows + i) & ":" &
(varHeaderRows + i)).Delete Shift:=xlUp
varUsedRows = [UsedRows]
varTotalRows = [TotalRows ]
varEntries= [Entries]
Else
If Worksheets("Sheetname").Range("B" & (varHeaderRows +
i)).Value < varDate0 Then varArchivate = i
i = i + 1
End If
Loop
...
' replacing formulas from rows older as varFix days with values
...
' mowing rows older as varDate0 to archive
...
' adding new rows from last entry until today with default values
...
' adding or removing empty rows with prepared formulas at end of tablel
...
' redefining named range tblMyTable
...
' protecting sheets
...
Sheets("Sheetname").Protect Password:="password",
UserInterfaceOnly:=True
Sheets("Sheetname").EnableAutoFilter = True
...
End Sub
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Beisaikong" wrote in message
...
i locked certain cells in my excel worksheet ... and for the locking to
work
... i need to protect the document ...

in the protect options ... i allowed deletion of rows ...

but then ... when i try to delete after protecting the document ... it
will
say "cannot delete rows with locked cell"

anyone knows how to resolve ?!?!?!?!?!

hey ... thanks a lot ... tat was one of the method i tot b4 ... which is to
use coding ... thanks for the code

"Arvi Laanemets" wrote:

Hi

I use a procedure which makes various adjustments on protected sheet. The
procedure is started by user from button on one worksheet, but the same code
can be implemented into workbooks Open event too.

On worksheet with data, in one column the user can mark rows to be deleted.
Whenever the adjustments procedure is running, all marked rows are deleted.
The example of code is below:
-------
Public Sub AdjustTables()

' remove sheets protection
Sheets("Sheetname").Unprotect Password:="password"
...
' Adjusting table MyTable
...
' Reading named constants
...
varDate0 = [Date0]
varFix = [Fix]
varHeaderRows = [HeaderRows]
varEntries=[Entries]
...
Worksheets("Sheetname").Activate

' sorting table
...
' removing rows marked for deleting, counting rows to be moved to
arvhive
' rows with is TRUE in column N are deleted
i = 1
varArhivate = 0
Do Until i varEntries
If Worksheets("Sheetname").Range("N" & (varHeaderRows + i)) Then
Worksheets("Sheetname").Rows((varHeaderRows + i) & ":" &
(varHeaderRows + i)).Delete Shift:=xlUp
varUsedRows = [UsedRows]
varTotalRows = [TotalRows ]
varEntries= [Entries]
Else
If Worksheets("Sheetname").Range("B" & (varHeaderRows +
i)).Value < varDate0 Then varArchivate = i
i = i + 1
End If
Loop
...
' replacing formulas from rows older as varFix days with values
...
' mowing rows older as varDate0 to archive
...
' adding new rows from last entry until today with default values
...
' adding or removing empty rows with prepared formulas at end of tablel
...
' redefining named range tblMyTable
...
' protecting sheets
...
Sheets("Sheetname").Protect Password:="password",
UserInterfaceOnly:=True
Sheets("Sheetname").EnableAutoFilter = True
...
End Sub
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Beisaikong" wrote in message
...
i locked certain cells in my excel worksheet ... and for the locking to
work
... i need to protect the document ...

in the protect options ... i allowed deletion of rows ...

but then ... when i try to delete after protecting the document ... it
will
say "cannot delete rows with locked cell"

anyone knows how to resolve ?!?!?!?!?!

How do I use code like that? Paste it into a couple of cells?

"Arvi Laanemets" wrote:

Hi

I use a procedure which makes various adjustments on protected sheet. The
procedure is started by user from button on one worksheet, but the same code
can be implemented into workbooks Open event too.

On worksheet with data, in one column the user can mark rows to be deleted.
Whenever the adjustments procedure is running, all marked rows are deleted.
The example of code is below:
-------
Public Sub AdjustTables()

' remove sheets protection
Sheets("Sheetname").Unprotect Password:="password"
...
' Adjusting table MyTable
...
' Reading named constants
...
varDate0 = [Date0]
varFix = [Fix]
varHeaderRows = [HeaderRows]
varEntries=[Entries]
...
Worksheets("Sheetname").Activate

' sorting table
...
' removing rows marked for deleting, counting rows to be moved to
arvhive
' rows with is TRUE in column N are deleted
i = 1
varArhivate = 0
Do Until i varEntries
If Worksheets("Sheetname").Range("N" & (varHeaderRows + i)) Then
Worksheets("Sheetname").Rows((varHeaderRows + i) & ":" &
(varHeaderRows + i)).Delete Shift:=xlUp
varUsedRows = [UsedRows]
varTotalRows = [TotalRows ]
varEntries= [Entries]
Else
If Worksheets("Sheetname").Range("B" & (varHeaderRows +
i)).Value < varDate0 Then varArchivate = i
i = i + 1
End If
Loop
...
' replacing formulas from rows older as varFix days with values
...
' mowing rows older as varDate0 to archive
...
' adding new rows from last entry until today with default values
...
' adding or removing empty rows with prepared formulas at end of tablel
...
' redefining named range tblMyTable
...
' protecting sheets
...
Sheets("Sheetname").Protect Password:="password",
UserInterfaceOnly:=True
Sheets("Sheetname").EnableAutoFilter = True
...
End Sub
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Beisaikong" wrote in message
...
i locked certain cells in my excel worksheet ... and for the locking to
work
... i need to protect the document ...

in the protect options ... i allowed deletion of rows ...

but then ... when i try to delete after protecting the document ... it
will
say "cannot delete rows with locked cell"

anyone knows how to resolve ?!?!?!?!?!

Hi

With workbook opened, press Atl+F11 - VBA editor is activated.
To create a procedu
From menu, select Insert.Module (when you don't have one in your workbook
before - you can see modules in VBA Prooject window, which is letmost top
one in VBA editor. When you had the module, double-click on it to activate
it. Or create a new one - you can have several of them.
To create a new procedure/function, from menu select Insert.Procedure,
in Add Procedure wizard, check Sub (Function to create an UDF), enter the
name for procedure/function, and press OK. First and last rows of code (an
empty procedure - without any statements) are inserted.
Type in the rest of code.
To copy a procedure or function from elsewhere - simply copy the code
into module (in rightmost - the biggest - window of VBA editor
To create an worksheets Open event:
In VBA Project window, doubble-click on ThisWorkbook object.
In rightmost window, select Workbook instead of General in dropdown at top.
An empty Open event is created.
Copy statements from function (all between Public Sub ... End Sub rows) into
created event (between top and bottom rows), or simply type the code in.

Close the VBA editor. You are done!

NB! The function code I provided is given as an example. Very probably you
have to modify it to get it to work for you.


Arvi Laanemets


"raven" wrote in message
...
How do I use code like that? Paste it into a couple of cells?

"Arvi Laanemets" wrote:

Hi

I use a procedure which makes various adjustments on protected sheet.
The
procedure is started by user from button on one worksheet, but the same
code
can be implemented into workbooks Open event too.

On worksheet with data, in one column the user can mark rows to be
deleted.
Whenever the adjustments procedure is running, all marked rows are
deleted.
The example of code is below:
-------
Public Sub AdjustTables()

' remove sheets protection
Sheets("Sheetname").Unprotect Password:="password"
...
' Adjusting table MyTable
...
' Reading named constants
...
varDate0 = [Date0]
varFix = [Fix]
varHeaderRows = [HeaderRows]
varEntries=[Entries]
...
Worksheets("Sheetname").Activate

' sorting table
...
' removing rows marked for deleting, counting rows to be moved to
arvhive
' rows with is TRUE in column N are deleted
i = 1
varArhivate = 0
Do Until i varEntries
If Worksheets("Sheetname").Range("N" & (varHeaderRows + i)) Then
Worksheets("Sheetname").Rows((varHeaderRows + i) & ":" &
(varHeaderRows + i)).Delete Shift:=xlUp
varUsedRows = [UsedRows]
varTotalRows = [TotalRows ]
varEntries= [Entries]
Else
If Worksheets("Sheetname").Range("B" & (varHeaderRows +
i)).Value < varDate0 Then varArchivate = i
i = i + 1
End If
Loop
...
' replacing formulas from rows older as varFix days with values
...
' mowing rows older as varDate0 to archive
...
' adding new rows from last entry until today with default values
...
' adding or removing empty rows with prepared formulas at end of
tablel
...
' redefining named range tblMyTable
...
' protecting sheets
...
Sheets("Sheetname").Protect Password:="password",
UserInterfaceOnly:=True
Sheets("Sheetname").EnableAutoFilter = True
...
End Sub
-------

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets



"Beisaikong" wrote in message
...
i locked certain cells in my excel worksheet ... and for the locking
to
work
... i need to protect the document ...

in the protect options ... i allowed deletion of rows ...

but then ... when i try to delete after protecting the document ... it
will
say "cannot delete rows with locked cell"

anyone knows how to resolve ?!?!?!?!?!