VBA code

[noelmus]

Hi,
I have the code below which one has to run it manually. I need that if cell B9 is populated, it runs automatically.


Sub insertusername()
Range("A1").Value = Application.UserName
End Sub


Thanks in advance

 

[Tim_Excel_]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, ThisWorkbook.Sheets("REFERENCE").Range("B9")) Is Nothing Then
[COLOR=#333333]    [/COLOR]ThisWorkbook.Sheets("REFERENCE").[COLOR=#333333]Range("A1").Value = Application.UserName[/COLOR]
End If
Application.EnableEvents = True
End Sub

Place this in the sheet you want this to happen in

 

[noelmus]

Hi,
It gave me Compile error: Invalid or unqualified reference and .Range became highlighted.

Thanks

 

[Tim_Excel_]

Have you checked if the references are correct?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B9")) Is Nothing Then
   Range("A1").Value = Application.UserName
End If
Application.EnableEvents = True
End Sub

 

[noelmus]

Yes they are correct.
The worksheet is empty and I just put a number in cell B9.

Thanks

 

[noelmus]

Thanks Fluff it worked perfect. Just another question to tell you that in cell B9 I'm going to have this formula =TODAY(). Does that code work or I have to do something else?
Thanks

 

[Fluff]

No that code will only work if you enter a value manually.
You can move it to a calculate event, but as your formula is volatile, I would advise against it.

 

[noelmus]

Thanks a lot, I will change B9 to another cell that I need to enter manually.

 

[noelmus]

Sorry Fluff,
I have a problem that when I clear the contents in B9, cell A1 remains populated. Is there a way so that when I clear contents in B9, cell A1 becomes unpopulated?

Thanks in advance