Copy Cell Statically

[majime01]

Greetings All,

I am trying to copy cell (A2) statically to (D2) on the same sheet (workbook attached) if certain criteria are met. What I want to happen is this: User inputs their name in cell (A2). Next, if they toggle the checkbox (B2) to turn (C2=TRUE) and click the Input Button, then their name will copy to (D2). If the checkbox is not toggled (C2=FALSE), then MSGBOX appears asking them to check the box. However I only want the copy function to happen once...if they change the value in (A2) and do the process again, cell (D2) will retain the original value and not update.

I hope I have explained this well enough...as usual, in my mind it make perfect sense.

As always, your help and guidance is appreciated.

Ken

[skywriter]

This seems pretty simple which leads me to the question of where do you really want to get with this?

A lot of times people ask for something like what you have and when they get it they say well my data doesn't exactly look like that or how do I make it work with all the sheets in my workbook?

They keep adding details later.

The coder and the requester go back and forth many times when it could have been done all in one shot if the requester had laid it all out in the beginning.

[majime01]

Hey Skywriter,

Thank you for the interest. I am trying to assign one student to one workbook by way of their name which will show up on the sheet. I don't want the user to input data and then copy the workbook for a friend who can then pass off the work as their own. I am trying to prevent plagiarism and have each student do their own work.

If you have any other ideas on how best to accomplish this, I am absolutely open to ideas and suggestions...this was my first thought on a solution.

[skywriter]

It sounds more like you should lock all the cells on the workbook until they enter a name and then unlock all the cells except where they put the name.
You would also want to lock the code so they can't access it.
Excel from what I understand is not very secure. I'm not a hacker, but I've heard there are programs available that can do it, maybe easily, but like I said I'm not a hacker so I may be completely wrong.
If they have to logon as a certain user there are ways via code to put their name into the spreadsheet. Maybe in a sheet hidden by code that they don't have access to?
There are more advanced users than I here, hopefully someone else will chime in with some ideas.

[majime01]

Skywriter,

Any input on code for my initial idea?

Thank you.

[skywriter]

I marked all the cells as unlocked with the exception of D2. The worksheet was locked and the only option box checked in the protect sheet box was allow the user to select unlocked cells. The password to unlock the sheet is 123, you will see that in the code.

When the user uses the sheet they will not be able to click into cell D2.

If they leave A2 blank or fail to check the box they get a message.

Once D2 has a value in it then the input button will appear to do nothing. In actuality the code is always checking to see if D2 has a value or not. Once there is a value the code will always exit immediately after discovering cell D2 already has a value. D2 is always locked to a user, regardless of whether it is empty or not.

You should also lock the code.
While in the code window choose the tools drop down menu, options, protection tab, check the box and enter a password.

Good Luck.

Sub Test()
    With Worksheets("Sheet1")
        If .Range("D2") <> "" Then Exit Sub
            If .Range("C2") = True And .Range("A2") <> "" Then
                .Unprotect Password:=123
                .Range("D2").Value = .Range("A2").Value
               .Protect Password:=123
            Else
                MsgBox "You must enter your name and check the box."
            End If
    End With
End Sub

[majime01]

works great, thank you Skywriter