EXCEL - Update cell content ONLY if the cell has changed and only do it once

[LeightonJames]

OK, I have a job log spreadsheet that I use in a number of schools (easiest way asall machines have excel installed and it doesn't require them to log into an 3rd party app) and I have been wanting to update it to do a few "fancy things". One of which is automatically record the users computername.

 

I have been able to do this with some code mangling from other sites however my issue now is that the code runs constantly until it hits the cell limit.

 

This is my test code:

 

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range
   Dim sHostName As String
       sHostName = Environ$("computername")
       Set KeyCells = Range("A1:A500")
   
   If Not Application.Intersect(KeyCells, Range(Target.Address)) _
          Is Nothing Then
       Target.Value = Target.Value & " - " & sHostName
       
   End If
End Sub

 

What this is does is continuously add the machine name to the edited cell. I want it so that the cell is only updated if the cell contents change from blank. Anyone got any ideas??

[RichCowell]

I always Google my codes so not too well-up on them, but could you add an if empty line before it...

If IsEmpty(Range("A1:A500").Value) = True Then

[howartp]

If I'm understanding your desired logic, I'd make use of Target.Column and Target.Row instead of intersect() and KeyCells.

 

Then I'd maintain a currentRow variable which holds the row number of the last row that's been filled in.

 

In rough code it would be:

 

sub worksheet_change(target)
{
If target.row > currentRow
{
currentRow = target.row
range(target.row,1) = sHostname
}
}

 

I'm on my phone so you'll need to define sHostname etc as you have above.

[LeightonJames]

Thanks guys, I'll have another play today.

 

EDIT:

 

Managed to get it working. This is the new code:

 

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim KeyCells As Range
   Dim sHostName As String
       sHostName = Environ$("computername")
       Set KeyCells = Range("A1:A500")

       If Target.Value <> "" Then
       Application.EnableEvents = False
       Target.Value = Target.Value & " - Computer Name: " & sHostName
       Application.EnableEvents = True
   End If
End Sub

 

Adding the Application.EnableEvents flag made the code run once only and changing the check condition works better than the previous one

 

Thanks for the help though guys

Edited July 17, 2017 by LeightonJames

[LeightonJames]

OK so I was wrong. Whilst the above code DOES work, it does not work as intended.

 

It adds the computer name to EVERY cell that gets edited. SMH.

 

Back to the drawing board

[Bedders]

Surely you need something that detects if the cell is within the KeyCells range?

 

Although, as howartp mentioned above, you could do something like

 

If Target.Column = 'A' Then

 

instead of using KeyCells.

[regp]

How to execute from vs

[Bedders]

How to execute from vs

 

Hi regp,

 

I'm not too sure I understand what you're asking - it may be better to start a new thread, and explaining your problem (for example "How do I export data into Excel using Visual Studio?").