Just wondering if there is a way to load info into a worksheet, then pause
for a few moments, and then apply protection to a specific sheet so users
cant delete rows in this sheet. I cant import the information with
protection on the sheet. I looked though many helpful topics on this DG, but
the answer is eluding me.


I have code in a module that loads the data into the sheet. I placed a
Private Sub behind the worksheet itself. Below is the code that I was
mulling over; doesnt seem to do what I wanted it to do. Does anyone have
any ideas as to how to proceed? Excel may not permit this activity. I can't
tell for sure...


Private Sub my_Procedure()

Application.OnTime Now + TimeValue("00:00:10"), "my_Procedure"

ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True


End Sub


--
RyGuy

maybe something like this

Private Sub my_Procedure()

'wait for 5 seconds
Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5)

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub




--
Hope that helps.

Vergel Adriano


"ryguy7272" wrote:

Just wondering if there is a way to load info into a worksheet, then pause
for a few moments, and then apply protection to a specific sheet so users
cant delete rows in this sheet. I cant import the information with
protection on the sheet. I looked though many helpful topics on this DG, but
the answer is eluding me.


I have code in a module that loads the data into the sheet. I placed a
Private Sub behind the worksheet itself. Below is the code that I was
mulling over; doesnt seem to do what I wanted it to do. Does anyone have
any ideas as to how to proceed? Excel may not permit this activity. I can't
tell for sure...


Private Sub my_Procedure()

Application.OnTime Now + TimeValue("00:00:10"), "my_Procedure"

ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True


End Sub


--
RyGuy

Great suggestion Vergel. I tried something similar yesterday, but it didnt
work. I just tried your version today and this didnt work either. I keep
getting the same error at this line:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

As far as I can tell, it is failing because the worksheet is
password-protected (immediately), and thus no changes can be made. I was
hoping to find a workaround that delays the protection from being applied for
like 2 seconds, or five seconds, or some such thing, so I can capture
information relevant to the last time the file was viewed, changed, modified,
whatever (this info. is recorded immediately after the file is opened). I am
trying to record this information when the user exits, and save it in a
(historical) list displaying info. relevant to everyone that has accessed the
file. Right now it works, without password-protection, but someone can €˜game
the system by deleting rows containing information of the users who accessed
the file.

Maybe this cant be done in Excel€¦ If anyone knows, and can offer a
suggestion, Id really appreciate it.

Regards,
Ryan---

--
RyGuy


"Vergel Adriano" wrote:

maybe something like this

Private Sub my_Procedure()

'wait for 5 seconds
Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5)

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub




--
Hope that helps.

Vergel Adriano


"ryguy7272" wrote:

Just wondering if there is a way to load info into a worksheet, then pause
for a few moments, and then apply protection to a specific sheet so users
cant delete rows in this sheet. I cant import the information with
protection on the sheet. I looked though many helpful topics on this DG, but
the answer is eluding me.


I have code in a module that loads the data into the sheet. I placed a
Private Sub behind the worksheet itself. Below is the code that I was
mulling over; doesnt seem to do what I wanted it to do. Does anyone have
any ideas as to how to proceed? Excel may not permit this activity. I can't
tell for sure...


Private Sub my_Procedure()

Application.OnTime Now + TimeValue("00:00:10"), "my_Procedure"

ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True


End Sub


--
RyGuy

After playing around with it for just a bit longer, I figured it out:

I used ActiveSheet.Unprotect, right after Sub Auto_Open(), then Excel did
the operation, and then, just before exiting the Sub, I reapplied Worksheet
protection.

PS, thanks to those who submitted the ideas for the =LSDate(),and the
=DocProps("last author"), and the =DocProps("last save time"), which I found
on earlier submissions to the DG.


See the code below...


Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 '1 Second
Public Const cRunWhat = "Sub Locking"



Function LSDate()
LSDate = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
End Function



Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


'and enter in a cell such as
'=DocProps ("last author")
'or
'=DocProps ("last save time")


Sub Auto_Open()

ActiveSheet.Unprotect

Application.ScreenUpdating = False

Sheets("Last Saved").Select

Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("A3:C3").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Range("A1").Select

Range("A2").Select
ActiveCell.FormulaR1C1 = "=DocProps(""last author"")"


Range("B2").Select
ActiveCell.FormulaR1C1 = "=LSDate()"


Range("C2").Select
ActiveCell.FormulaR1C1 = "=DocProps(""last save time"")"


Range("B2").Select
Selection.NumberFormat = "m/d/yyyy;@"
Range("C2").Select
Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Range("A1").Select
'-------------------------------------

Columns("A:C").Select

Selection.Sort Key1:=Range("B2"), Order1:=xlDescending,
Key2:=Range("C2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select
'-------------------------------------
Application.ScreenUpdating = True

Range("A1").Activate

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True


End Sub


--
RyGuy


"ryguy7272" wrote:

Great suggestion Vergel. I tried something similar yesterday, but it didnt
work. I just tried your version today and this didnt work either. I keep
getting the same error at this line:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

As far as I can tell, it is failing because the worksheet is
password-protected (immediately), and thus no changes can be made. I was
hoping to find a workaround that delays the protection from being applied for
like 2 seconds, or five seconds, or some such thing, so I can capture
information relevant to the last time the file was viewed, changed, modified,
whatever (this info. is recorded immediately after the file is opened). I am
trying to record this information when the user exits, and save it in a
(historical) list displaying info. relevant to everyone that has accessed the
file. Right now it works, without password-protection, but someone can €˜game
the system by deleting rows containing information of the users who accessed
the file.

Maybe this cant be done in Excel€¦ If anyone knows, and can offer a
suggestion, Id really appreciate it.

Regards,
Ryan---

--
RyGuy


"Vergel Adriano" wrote:

maybe something like this

Private Sub my_Procedure()

'wait for 5 seconds
Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5)

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

End Sub




--
Hope that helps.

Vergel Adriano


"ryguy7272" wrote:

Just wondering if there is a way to load info into a worksheet, then pause
for a few moments, and then apply protection to a specific sheet so users
cant delete rows in this sheet. I cant import the information with
protection on the sheet. I looked though many helpful topics on this DG, but
the answer is eluding me.


I have code in a module that loads the data into the sheet. I placed a
Private Sub behind the worksheet itself. Below is the code that I was
mulling over; doesnt seem to do what I wanted it to do. Does anyone have
any ideas as to how to proceed? Excel may not permit this activity. I can't
tell for sure...


Private Sub my_Procedure()

Application.OnTime Now + TimeValue("00:00:10"), "my_Procedure"

ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True


End Sub


--
RyGuy