Worksheet Change Help Needed

[ninja1980]

I’m basically trying to create a spreadsheet to model the temperature of pipe work as low temperature gas travels down it (during blowdown). I’ve set up an Input worksheet which end users will input their specific data and then a graph is drawn below summarising the results.

The input data is then used in a hidden worksheet to calculate the change in temperature with respect to time. As the time intervals and pipe segments are very small, there are several thousand cells that re-calculate upon changes to the main Input worksheet

To speed up the input of data, I have disabled Excel auto-calculate. Instead, I have inserted a command button called “calculate” which the end user will press once all input data has been entered. This method is quite dangerous in the fact that changes to the input data are not transposed to results section until the user hits “calculate”. To remedy this problem I have attempted to create a macro to hide the results when the input data changes before the user hits “calculate” (at the end of the post).

As some of the input data can be several thousand cells I tried to speed up the macro by only hiding the results and displaying a warning message the 1st time data changes. This works fine for initial single cell changes but for initial multiple cell changes (e.g. copy and pasting several cells to the Input worksheet) I get the warning macro running for each of the selected cells.

I’m totally stuck on this one (I thought I was doing quite well considering I have virtually no VB knowledge ?)

Thanks for your help


CODE:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Dim Error_run As String
Dim myRange As Range

Error_run = Range("$Z$1").Value
Set myRange = ActiveCell
Set VRange = Range("custom_pipe")

'Check for sheet changes
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then
If Error_run = "YES" Then

Exit Sub

Else

'Run Warning Sub
warning

Sheets("pipe data").Activate
Range("$Z$1").Value = "YES"
myRange.Select

End If
End If
Next cell

Sheets("pipe data").Activate
myRange.Select

End Sub

 

[PHV]

Change this:
If Error_run = "YES" Then
to this:
If Range("$Z$1").Value = "YES" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ninja1980]

Thanks, that worked perfectly. I can see the problem now

On a slightly unrelated matter. When operating a macro, is there any way to stop it stealing the focus of the screen i.e. say the macro was formatting data on several sheets. I would prefer if I could make the screen stay on the original worksheet until the macro has finished

 

[PHV]

Application.ScreenUpdating = False
'your code here
Application.ScreenUpdating = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886