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

[DAFJR]

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

 

[DAFJR]

Any takers on this? PLEASE HELP!!

 

[Scott Huish]

Perhaps:

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

 

[DAFJR]

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]

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]

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:

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:

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