Worksheet_Change trigger

[BobHunter]

I have the below code attached to a workseet which updates a text box with a figure when the user enters any figure in a cell :

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
If Sheets("DataSheet&quot.Range("F20&quot.Value = "False" Then
ActiveSheet.Unprotect Password:="r3s0urc3"
ActiveSheet.Shapes("txtHoursAvailable&quot.Select
Selection.Characters.Text = Range("D1&quot.Value & " Hours Available"
ActiveSheet.Protect Password:="r3s0urc3"
End If
End Sub

The problem occurs if the user copies/pastespecials a row of figures - Excel hangs until I right click on the taskbar and choose to close.

Then Excel seems to recover itself and asks me 'do I want to save changes' etc. I say No and everything is back to normal.

Any ideas ?

 

[RobBroekhuis]

By "Figure", do you mean number (as opposed to graphical object)? It seems like the only time you really need to do the update is if cell D1 changes. In that case, start your sub with

if target.address<>&quot;$D$1&quot; then exit sub

That will prevent the event from running if nothing of importance has changed.
Rob

 

[andrew299]

first impressions try turning on the screen updating and see if any boxes appear. It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[BobHunter]

The figure (Range(&quot;D1&quot.Value) is a summation of cells on the worksheet and is a number.

HEY I just removed the ScreenUpdating and it now is working ! I thought this was automatically set back to true after the sub has finished.

Seems that was the problem.

Thanks everyone....