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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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,136,425
Messages
5,675,770
Members
419,585
Latest member
popsin

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