Controlling Excel Automatic Calculation???

[ChrisAmies]

Hi

I’m trying to make a simple routine in excel VB so, when a command
button is pressed, the whole workbook is calculated (I have automatic
calculation set to manual). I also want to display a warning message
to the end user when input data is changed but the “calculate” button
has not been pressed.

Can anyone point me in the right direction?

Any help would be very much appreciated.

 

[ricardo.rietveld]

It is never a good idea to set calculation to manual. nevertheless,
this is a normal setting. Put the following code in the Command Button
Click function

With Application
.Calculation = xlAutomatic
End With

Gr,
Ricardo

 

[ChrisAmies]

Could you give me any pointers on how to display a warning message if
the “calculate” button has not be pressed but input data has
changed??

 

[Rick Rothstein]

I would consider changing the color of the text in the CommandButton as a
warning to the user that the sheet needs to be recalculated (for example,
red text warns the user the sheet need calculating, black text means it
doesn't). To do this, set the text to black in the CommandButton's Click
event and set the text to red in the worksheet's Change event. How you set
the color depends on where the CommandButton came from.

From the Forms toolbar
========================
With Worksheets("Sheet1")
.Shapes("Button 1").TextFrame.Characters.Font.Color = vbRed
End With

From the Control Toolbox toolbar
==================================
With Worksheets("Sheet1")
.CommandButton1.ForeColor = vbRed
End With

You can use the predefined constant vbBlack to make the text black again.

--
Rick (MVP - Excel)


Could you give me any pointers on how to display a warning message if
the “calculate” button has not be pressed but input data has
changed??

 

[ChrisAmies]

Hi

I’ve put together the following routine. It basically does what I
originally wanted. I’ve also added code to make sure the “warning”
sub routine is only run on the 1st change of the monitored range of
cells.

I’m having problems when a group of cells are changed in one go (i.e.
a selection). This results in the “warning” sub routine being run for
however many cells are originally selected and changed.

I’m really new to Excel VBA, can anyone offer any suggestions?

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

 

[Rick Rothstein]

I think this will do what you want... remove the warning from inside the
loop and add this code immediately before the For..Next statement (so it run
before the loop starts)...

If Error_run <> "YES" And Not Intersect(Target, VRange) Is Nothing Then
' Run Warning Sub
warning
End If

Also, if I understand it correctly, this line...

If Union(cell, VRange).Address = VRange.Address Then

is usually written like this...

If Not Intersect(cell, VRange) Is Nothing Then

--
Rick (MVP - Excel)


Hi

I’ve put together the following routine. It basically does what I
originally wanted. I’ve also added code to make sure the “warning”
sub routine is only run on the 1st change of the monitored range of
cells.

I’m having problems when a group of cells are changed in one go (i.e.
a selection). This results in the “warning” sub routine being run for
however many cells are originally selected and changed.

I’m really new to Excel VBA, can anyone offer any suggestions?

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