Need help with IF/THEN statement for username and date/time.

DAFJR

New Member
Joined
Sep 20, 2006
Messages
4
I am new to VB commands...still learning! I have a spreadsheet in which I need the active username (Windows logon) and the date placed in adjacent cells when a particular cell is populated with a value of TRUE.

If someone could translate this into code I would greatly appreciate it. This is basically what I need to have the spreadsheet do:

If cell D7 = TRUE then cell B7 = USERNAME and cell C7 = Date/Time.

Does this make sense? Please let me know if I need to be more specific.

Many thanks for any/all assistance!

-DAF
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps:

Code:
Sub test()
If Range("D7") Then
    Range("B7") = Environ("USERNAME")
    Range("C7") = Now
End If
End Sub
 

DAFJR

New Member
Joined
Sep 20, 2006
Messages
4
Thank you for your reply. This did not seem to do the trick. I think there might be something wrong with the code that I am using to run this off of.

I have a spreadsheet with a code that creates checkboxes and places a value of TRUE in the cell when the box is checked. 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 = 11 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 11 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub

I am trying to get your code to work off of this but am unable to.

Any suggestions?

Many thanks for any assistance.

-DAF
 

DAFJR

New Member
Joined
Sep 20, 2006
Messages
4
Can anyone help me with this? I really need help getting this to work. Any assistance at all would be greatly appreciated.

Thanks,

DAF
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, perhaps this, although I would highly advise against naming your checkboxes the same as cell addresses, that's likely to cause problems later, I changed it to be chk and the cell address:

Code:
Sub chckbxmkr()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange
    With ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height)
        .LinkedCell = c.Address
        .Characters.Text = ""
        .Name = "chk" & c.Address
    End With

With c
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, _
        Formula1:="=" & c.Address & "=TRUE"
    .FormatConditions(1).Font.ColorIndex = 11 'change for other color when ticked
    .FormatConditions(1).Interior.ColorIndex = 11 'change for other color when ticked
    .Font.ColorIndex = 2 'cell background color = White
    .Offset(, 1).Formula = _
        "=IF(" & c.Address & ",GetUserName()," & Chr(34) & Chr(34) & ")"
    .Offset(, 2).Formula = "=IF(" & c.Address & ",now()," & Chr(34) & Chr(34) & ")"
    .Offset(, 2).NumberFormat = "m/d/yyyy h:mm AM/PM"
End With
Next
myRange.Select
End Sub

and in a standard module:

Code:
Public Function GetUserName() As String
GetUserName = Environ("USERNAME")
End Function
 

Forum statistics

Threads
1,176,079
Messages
5,901,274
Members
434,884
Latest member
ares0027

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