returning date and username if statement is true

lager28

New Member
Joined
Jul 30, 2019
Messages
9
I have an excel sheet that tracks documents that have been reviewed by project managers. When a PM reviews a document they select "Yes" from a drop down (only way i could figure out to limit data entry). What I am looking to do now is two seperate actions.
1. If "Yes" is in Cell D23 (the whole range is D23:D114 then I would like a current date entered into Cell E23 (whole range is E23:E114)
2. If "Yes" is in Cell D23 then I would like Excel to add the computer login (not the excel username) into Cell C23 (whole range is C23:C114)

I can get a date by using the following formula but it changes everytime the file is opened. I need the date to remain the same.

=IF(D25="Yes", NOW(),"")

I also managed to get a username to populate but I cannot find the forum where I got the instruction. What happens is that when I type =User in a cell it returns the username, so I've hidden "=USER" by making the text colour white in an empty cell where no input is likely to occur. However, it changes to the current system user each time the file is opened. I'd like it to remain the same after the first entry.

I'm new to VBA and still figuring out the power of formulas in excel so appreciate any and all help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Do you have any merged cells?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

lager28

New Member
Joined
Jul 30, 2019
Messages
9
Yes there are several tables in that range, that have a heading which is merged.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("D23:D114")) Is Nothing Then
      If Target.Value = "Yes" Then
         Me.Unprotect "Pword"
         Target.Offset(, 1).Value = Date
         Target.Offset(, -1).Value = Environ("username")
         Target.Offset(, -1).Resize(, 3).Locked = True
         Me.Protect "Pword"
      End If
   End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,041
Members
415,875
Latest member
Tarali

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