Automatically protect cells after updating information

[Guest]

Hi,
I've got a user form through which I am accepting various inputs & when I
click on the 'add record' button the data gets copied onto rows everytime. Is
there anything that i can do to automatically ensure the rows (after
updating) gets protected ?

I cant protect the sheet directly in this case, cos the user feeds thru the
form will not get copied to the database table then.

This is how my code currently looks.
*****************************
Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data_Dump")

'Find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

' Check for incomplete entries before 'Adding Recod'

If Trim(Me.CboStaffName.Value) = "" Then
Me.CboStaffName.SetFocus
MsgBox "Please enter Staff Name"
Exit Sub
End If

' To Copy the data to the database
ws.Cells(iRow, 1).Value = Me.Txtdate.Value
ws.Cells(iRow, 2).Value = Me.Txttime.Value

' Clear the data
Me.Txtdate.Value = Format(Date, "dd-mmm-yy")
Me.Txttime.Value = Format(Time, "hh:mm:ss")
Me.CboStaffName.SetFocus
Me.txtVlu.Visible = False
Me.Label7.Visible = False
Me.Label8.Visible = False
Me.CboICreferer.Visible = False

End Sub
**********************
Please help.

Thx
Matthew

 

[Guest]

If the sheet isn't protected, then none of the cells are protected. So I am
not sure what you are after?

 

[Paul B]

Matts, if all information in entered when you click the cmdAdd_Click button
how about adding a line of code to unprotect the sheet at the start and
protect it at the end, would that work?

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Hello Paul,
I'm quite new to excel programming & I'm basically replicating a lot of
ideas/codes as found on numerous sample user forms.

If what your are advising is the right way to go about it, could you please
advise on what the code should be & where should it be inserted

Thx
Matts

 

[Paul B]

Matts, see if this will do what you want,

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data_Dump")

'This is the password for the sheet
Const PW As String = "123" '****Change Password Here******
'change "123" to "" if you don't want a password

'Find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

' Check for incomplete entries before 'Adding Recod'

If Trim(Me.CboStaffName.Value) = "" Then
Me.CboStaffName.SetFocus
MsgBox "Please enter Staff Name"
Exit Sub
End If

'unprotect the sheet
Sheets("Data_Dump").Unprotect Password:=PW

' To Copy the data to the database
ws.Cells(iRow, 1).Value = Me.Txtdate.Value
ws.Cells(iRow, 2).Value = Me.Txttime.Value

'protects the sheet
Sheets("Data_Dump").Protect Password:=PW


' Clear the data
Me.Txtdate.Value = Format(Date, "dd-mmm-yy")
Me.Txttime.Value = Format(Time, "hh:mm:ss")
Me.CboStaffName.SetFocus
Me.txtVlu.Visible = False
Me.Label7.Visible = False
Me.Label8.Visible = False
Me.CboICreferer.Visible = False

End Sub

You may also want to protect the VBA project to keep someone from seeing the
password in it,

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is at the top-left of the spreadsheet
this will open the VBA editor, in Project Explorer right click on your
workbook name, if you don't see it press CTRL + r to open the Project
Explorer then select VBA project properties, protection, check lock project
for viewing and set a password. Press Alt and Q to close this window and go
back to your workbook and save and close the file. Be aware that this
password can be broken by third party software.


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Thanks Paul,Dind know it was this simple.
Thx again
Matts

 

[Guest]

You probably should have left this confusing statement out of your post:

I cant protect the sheet directly in this case, cos the user feeds thru the

form will not get copied to the database table then.

And you might have gotten the answer you needed the first time.