lock cells after entry
 

Hi, I'm currently making a program using excel. Currently, I've been able to
transfer data from userform to excel after clicking the submit button. Now, I
wanted to protect each entry by locking them after entry. Basically, I have 6
fields: Date, Name, Comments, Amount 1, Amount 2, Total Amount
The sixth field being a formula meant that it didn't need to be added to the
form.

The way I wanted it to be:
1. Fill up form
2. Submit
3. Form transfers data to worksheet
4. Form locks worksheet but available for viewing

I've already set the worksheet protection to not be able to do anything
besides viewing.

Here's my current "uncompleted" code which I hope is do-able.

Private Sub CommandButton1_Click()
Dim LastRow As Object


Set LastRow = Sheet1.Range("a65536").End(xlUp)
ThisWorkbook.Unprotect Password = "pwd"
LastRow.Offset(1, 0).Value = Now
LastRow.Locked = True
LastRow.Offset(1, 1).Value = TextBox1.Text
LastRow.Locked = True
LastRow.Offset(1, 2).Value = TextBox4.Text
LastRow.Locked = True
LastRow.Offset(1, 3).Value = TextBox2.Text
LastRow.Locked = True
LastRow.Offset(1, 4).Value = TextBox3.Text
LastRow.Locked = True
ThisWorkbook.Protect Password = "pwd"

MsgBox "Entry Added!"

response = MsgBox("New Entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox1.SetFocus

Else
Unload Me
End If

End Sub

Any suggestions or fixes for this would be greatly appreciated.

--
Message posted via http://www.officekb.com

lock cells after entry
 

Nvm. Did a little more experimenting and found the solution:

1. Changed [ThisWorkbook.Unprotect Password = "pwd"] to [Sheet1.Unprotect
Password = "pwd"]
2. Changed [LastRow.Locked = True] to [LastRow.Offset(1, X).Locked = True]
3. Changed [ThisWorkbook.Protect Password = "pwd"] to [Sheet1.Protect
Password = "pwd"]
4. Removed other sheets.

maximillan wrote:
Hi, I'm currently making a program using excel. Currently, I've been able to
transfer data from userform to excel after clicking the submit button. Now, I
wanted to protect each entry by locking them after entry. Basically, I have 6
fields: Date, Name, Comments, Amount 1, Amount 2, Total Amount
The sixth field being a formula meant that it didn't need to be added to the
form.

The way I wanted it to be:
1. Fill up form
2. Submit
3. Form transfers data to worksheet
4. Form locks worksheet but available for viewing

I've already set the worksheet protection to not be able to do anything
besides viewing.

Here's my current "uncompleted" code which I hope is do-able.

Private Sub CommandButton1_Click()
Dim LastRow As Object


Set LastRow = Sheet1.Range("a65536").End(xlUp)
ThisWorkbook.Unprotect Password = "pwd"
LastRow.Offset(1, 0).Value = Now
LastRow.Locked = True
LastRow.Offset(1, 1).Value = TextBox1.Text
LastRow.Locked = True
LastRow.Offset(1, 2).Value = TextBox4.Text
LastRow.Locked = True
LastRow.Offset(1, 3).Value = TextBox2.Text
LastRow.Locked = True
LastRow.Offset(1, 4).Value = TextBox3.Text
LastRow.Locked = True
ThisWorkbook.Protect Password = "pwd"

MsgBox "Entry Added!"

response = MsgBox("New Entry?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox1.SetFocus

Else
Unload Me
End If

End Sub

Any suggestions or fixes for this would be greatly appreciated.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1