Auto name entry and auto date enrty from a cell

TheDonRules

New Member
Joined
Sep 17, 2006
Messages
6
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

TheDonRules

New Member
Joined
Sep 17, 2006
Messages
6
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

New Member
Joined
Sep 17, 2006
Messages
6
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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:
Code:
Range("F2") = Date

To retrieve the users name, I usually use the following:
Code:
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

New Member
Joined
Sep 17, 2006
Messages
6
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:
Code:
Range("F2") = Date

To retrieve the users name, I usually use the following:
Code:
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
 

Forum statistics

Threads
1,136,616
Messages
5,676,831
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top