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

 

[Fluff]

Try

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

 

[noelmus]

Thanks a lot Fluff.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Tim_Excel_]

Yes they are correct.

I highly doubt this is the case. Fluff's code is equal to mine other than that he has removed the Workbook and Worksheet references.

I always try to refer to both the workbook and worksheet, as not doing so may cause changes to happen in the wrong wb/ws depending on which is activated. This is especially the case during debugging.

 

[Fluff]

As it's a change event the ranges automatically refer to the sheet being changed & therefore there is no need to use workbook, or worksheet references.

 

[Tim_Excel_]

Yes, but I've made it a custom to always refer to wb/ws. Always refer to them --> never get an error related to that.
Regardless, my code ought to work

 

[Fluff]

There is nothing wrong with your code, and should work as long as the sheet names are correct.
If they aren't then the OP should have got a run time error 9, not a compile error, so there was something else going on.