Username (multiple usage)

[Spraggs]

Good evening,
I've got a table C2:N7 where employess enter data. I want to record the last person to fill in each cell by entering their username on another grid Q2:AB7. (i.e. C2 data, username entered in cell Q2 etc.)
Is this feasable? If so, how. e.g. what would the code be and where and how do I enter it. I'm using vista & excel 2007.

Any help is greatly appreciated.
Thanks Jase

 

[wigi]

You should always disable events before writing new entries with Worksheet_Change to stop the code refiring ....

Cheers

Dave

And what if the code will never be outputting in the range c2:n7 ?

 

[brettdj]

You should still do it as the code will refire needlessly

Which in this particular situation may not cause a re-occurring loop, but if the scope of the code is enhanced, or this snippet is modified/used elsewhere by someoense else then an inefficiency may become a problem

 

[wigi]

And what if your code breaks down in the middle, when the events have been disabled? The events will not be turned on again automatically...

I take it that when the scope of the code is enhanced, one looks at the full code and not 1 line.

 

[brettdj]

Well in that case you are advocating never disabling events because the code may get halted. Do you take the same view on changing screenupdating/calculationmode/statusbar etc?

For mine allowing circular code techniques that have potential impacts ranging from unintended scope changes to Excel crashing is far more dangeous

Regards

Dave

 

[wigi]

Well in that case you are advocating never disabling events because the code may get halted. Do you take the same view on changing screenupdating/calculationmode/statusbar etc?

I do not know if you know this, but ScreenUpdating does not need to be returned to True. Do the test by executing (manually) after each other:

Sub test_generating_error()

    Application.ScreenUpdating = False
    
    MsgBox 1 / 0

End Sub

and

Sub test_screenupdating()

    MsgBox Application.ScreenUpdating

End Sub

It's not because you see this

Application.ScreenUpdating = True

line everywhere that you should use it.

The other 2 settings that you mention need decent error handling and are used as well by me (sparsely though since you need to limit interaction with sheets), error handling that is well beyond the scope of code written on forums.

For mine allowing circular code techniques that have potential impacts ranging from unintended scope changes to Excel crashing is far more dangeous

I take it that code from a message board is not just copy/pasted and used in a business/production environment without knowledge and administration. I refer to changing cell references in the change event. If not, it's the responsibility of the person(s) doing this and not of the coder. Documentation on the application can and should be included though.

Best regards,

Wigi

 

[brettdj]

I take it that code from a message board is not just copy/pasted and used in a business/production environment without knowledge and administration. I refer to changing cell references in the change event. If not, it's the responsibility of the person(s) doing this and not of the coder.

Well in my experience its's common practice for people take code from the forums and use it directly for work or personal user. Certainly other help forums - especially those with up front commercial models - see many repeat visits from the same people for this purpose

The other 2 settings that you mention need decent error handling and are used as well by me (sparsely though since you need to limit interaction with sheets), error handling that is well beyond the scope of code written on forums..

And to argue on one hand that techniques like those I mentioned below (even with a little error handling) lie outside the scope of the forums, yet accountability sits with end users to me sounds inconsistent.

I think we've burnt enough forum electrons on this, I'm happy to take this up further by PM if you wish to

Regards

Dave