I need help with approval buttons. I've added 3 command buttons that when
clicked complete a name and date cell indicating the document is approved.
However, the data in those cells can be deleted by any user of the
spreadshet. I want to lock contents of only that cell as part of the command
button action.
Thanks for any help. (Keep in mind i'm a newbie at this)

Using this code to complete the name & date cells...
Private Sub Button2_Click()

End Sub

Private Sub CommandButton1_Click()
Range("A1").Value = Environ("UserName")
Range("B1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

Private Sub CommandButton2_Click()
Range("A2").Value = Environ("UserName")
Range("B2").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

Private Sub CommandButton3_Click()
Range("A3").Value = Environ("UserName")
Range("B3").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

hi,
add something like this at the end of each macro.
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Range("A1").Select
also read up of security and protection
http://office.microsoft.com/en-us/he...388541033.aspx

regards
FSt1
"KPWinOhio" wrote:

> I need help with approval buttons. I've added 3 command buttons that when
> clicked complete a name and date cell indicating the document is approved.
> However, the data in those cells can be deleted by any user of the
> spreadshet. I want to lock contents of only that cell as part of the command
> button action.
> Thanks for any help. (Keep in mind i'm a newbie at this)
>
> Using this code to complete the name & date cells...
> Private Sub Button2_Click()
>
> End Sub
>
> Private Sub CommandButton1_Click()
> Range("A1").Value = Environ("UserName")
> Range("B1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
> End Sub
>
> Private Sub CommandButton2_Click()
> Range("A2").Value = Environ("UserName")
> Range("B2").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
> End Sub
>
> Private Sub CommandButton3_Click()
> Range("A3").Value = Environ("UserName")
> Range("B3").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
> End Sub

That locks the entire worksheet. All I want to do is lock the cells A1 & B1
where the approver's name and date is entered. Two more people need to enter
approvals.

"FSt1" wrote:

> hi,
> add something like this at the end of each macro.
> Range("A1").Select
> ActiveSheet.Protect DrawingObjects:=True, _
> Contents:=True, _
> Scenarios:=True
> ActiveSheet.EnableSelection = xlNoSelection
> Range("A1").Select
> also read up of security and protection
> http://office.microsoft.com/en-us/he...388541033.aspx
>
> regards
> FSt1
> "KPWinOhio" wrote:
>
> > I need help with approval buttons. I've added 3 command buttons that when
> > clicked complete a name and date cell indicating the document is approved.
> > However, the data in those cells can be deleted by any user of the
> > spreadshet. I want to lock contents of only that cell as part of the command
> > button action.
> > Thanks for any help. (Keep in mind i'm a newbie at this)
> >
> > Using this code to complete the name & date cells...
> > Private Sub Button2_Click()
> >
> > End Sub
> >
> > Private Sub CommandButton1_Click()
> > Range("A1").Value = Environ("UserName")
> > Range("B1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
> > End Sub
> >
> > Private Sub CommandButton2_Click()
> > Range("A2").Value = Environ("UserName")
> > Range("B2").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
> > End Sub
> >
> > Private Sub CommandButton3_Click()
> > Range("A3").Value = Environ("UserName")
> > Range("B3").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
> > End Sub

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="justme"
With Range("A1")
.Value = Environ("UserName")
.Locked = True
End With
With Range("B1")
.Value = Format(Now, "dd mmm yyyy hh:mm:ss")
.Locked = True
End With
ActiveSheet.Protect Password:="justme"
End Sub

First CTRL + a to select all cells and format to unlocked.

Then hit your button to enter the username and date and lock A1 and B1

Now.........you're going to want to hide the password from prying eyes.

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-click>VBAProject
Properties>Protection>Lock for Viewing. Apply a password and save/close the
workbook.

When re-opened, code will be unviewable or uneditable.


Gord Dibben MS Excel MVP


On Thu, 14 Jun 2007 08:24:00 -0700, KPWinOhio
<(E-Mail Removed)> wrote:

>That locks the entire worksheet. All I want to do is lock the cells A1 & B1
>where the approver's name and date is entered. Two more people need to enter
>approvals.
>
>"FSt1" wrote:
>
>> hi,
>> add something like this at the end of each macro.
>> Range("A1").Select
>> ActiveSheet.Protect DrawingObjects:=True, _
>> Contents:=True, _
>> Scenarios:=True
>> ActiveSheet.EnableSelection = xlNoSelection
>> Range("A1").Select
>> also read up of security and protection
>> http://office.microsoft.com/en-us/he...388541033.aspx
>>
>> regards
>> FSt1
>> "KPWinOhio" wrote:
>>
>> > I need help with approval buttons. I've added 3 command buttons that when
>> > clicked complete a name and date cell indicating the document is approved.
>> > However, the data in those cells can be deleted by any user of the
>> > spreadshet. I want to lock contents of only that cell as part of the command
>> > button action.
>> > Thanks for any help. (Keep in mind i'm a newbie at this)
>> >
>> > Using this code to complete the name & date cells...
>> > Private Sub Button2_Click()
>> >
>> > End Sub
>> >
>> > Private Sub CommandButton1_Click()
>> > Range("A1").Value = Environ("UserName")
>> > Range("B1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
>> > End Sub
>> >
>> > Private Sub CommandButton2_Click()
>> > Range("A2").Value = Environ("UserName")
>> > Range("B2").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
>> > End Sub
>> >
>> > Private Sub CommandButton3_Click()
>> > Range("A3").Value = Environ("UserName")
>> > Range("B3").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
>> > End Sub