Auto name entry and auto date enrty from a cell

[TheDonRules]

I have searched the forum and have found TONS of awesome info but not exactly what I need. The following is my question:

I am creating a sheet that I need to have the date automatically inserted into a particular cell and the name of the user making the entry into another cell. This needs to be performed when the user checks a checkbox in one particular cell. How do I do this?

I hope this is specific enough. Please let me know if more info is needed. If possible, I found a post on how to create a macro that auto-populated fields with checkboxes and changes the color of the cells when a box is checked. Can someone modify this code to perform what I need? ALSO...I would like the boxes to be in 3D.

If someone can help me with this, I will PAY!! Really, I will.

Many thanks in advance for any assistance!

Sincerely,

Don

Here is the code:

Sub chckbxmkr()

On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub

 

[TheDonRules]

I have searched the forum and have found TONS of awesome info but not exactly what I need. The following is my question:

I am creating a sheet that I need to have the date automatically inserted into a particular cell and the name of the user making the entry into another cell. This needs to be performed when the user checks a checkbox in one particular cell. How do I do this?

I hope this is specific enough. Please let me know if more info is needed. If possible, I found a post on how to create a macro that auto-populated fields with checkboxes and changes the color of the cells when a box is checked. Can someone modify this code to perform what I need? ALSO...I would like the boxes to be in 3D.

If someone can help me with this, I will PAY!! Really, I will.

Many thanks in advance for any assistance!

Sincerely,

Don

Here is the code:

Sub chckbxmkr()

On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub

To be more specific with an example, let's say the user checks a checkbox in cell C7. What I need to happen is have the date automatically enter into cell F2 and the name of the person into cell B7. I only need the date to enter once from having this particular cell checked.

Thanks for any assistance.

-Don

 

[TheDonRules]

OK...for those viewing this...and all those who HAVE viewed this...THANKS for the response. Obviously no one knows how to do this. Someone would and should have posted something by now, with all the views already.

I bet you will post to this if I say:

IF YOU CAN'T SOLVE THIS THEN STOP READING THIS POST NOW!

Bet you will. **** you. **** you very much.

-D

 

[njimack]

I think you're taking a bit of a liberty expecting your problem to be solved within an hour, especially as most of America doesn't wake up for a couple of hours.

I'm not going to rewrite your code, but the following will insert todays date into a cell:

Range("F2") = Date

To retrieve the users name, I usually use the following:

Dim myOlApp, myNameSpace As Object
        Dim Full_name, First_Name, Surname, User As String
        Set myOlApp = CreateObject("Outlook.Application")
        Set myNameSpace = myOlApp.GetNameSpace("MAPI")
        
        Full_name = myNameSpace.CurrentUser.Name
        First_Name = Right(Full_name, Len(Full_name) - WorksheetFunction.Search(",", Full_name) - 1)
        Surname = Left(Full_name, WorksheetFunction.Search(",", Full_name) - 1)
    
        User = First_Name & " " & Surname

 

[TheDonRules]

I think you're taking a bit of a liberty expecting your problem to be solved within an hour, especially as most of America doesn't wake up for a couple of hours.

I'm not going to rewrite your code, but the following will insert todays date into a cell:

Range("F2") = Date

To retrieve the users name, I usually use the following:

Dim myOlApp, myNameSpace As Object
        Dim Full_name, First_Name, Surname, User As String
        Set myOlApp = CreateObject("Outlook.Application")
        Set myNameSpace = myOlApp.GetNameSpace("MAPI")
        
        Full_name = myNameSpace.CurrentUser.Name
        First_Name = Right(Full_name, Len(Full_name) - WorksheetFunction.Search(",", Full_name) - 1)
        Surname = Left(Full_name, WorksheetFunction.Search(",", Full_name) - 1)
    
        User = First_Name & " " & Surname

First, several people use this account where I work. I found the person who made the shameful post and I have changed the password. He'll have to get his own account if he wants to post. I apologize for his remarks.

Lastly, thank you for your response. I will be putting this to test in my spreadsheet. I'll keep you posted.

Many thanks.

Don