|  | Controlling Excel Automatic Calculation??? | | |
| | | ChrisAmies@gmail.com |  |
|  Posted: Wed Sep 03, 2008 10:40 am Post subject: Controlling Excel Automatic Calculation??? |  |
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. |
| |
| | | Guest | |
| | Posted: Wed Sep 03, 2008 10:53 am Post subject: Re: Controlling Excel Automatic Calculation??? | |
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@gmail.com | |
| | Posted: Wed Sep 03, 2008 10:59 am Post subject: Re: Controlling Excel Automatic Calculation??? | |
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 | |
| | Posted: Wed Sep 03, 2008 2:29 pm Post subject: Re: Controlling Excel Automatic Calculation??? | |
| |  | |
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)
<ChrisAmies@gmail.com> wrote in message
news:bacce0df-ab1f-4ac5-b2cb-d9cc3c1b04c4@e39g2000hsf.googlegroups.com...
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 | |
| | Posted: Fri Sep 05, 2008 1:10 pm Post subject: Re: Controlling Excel Automatic Calculation??? | |
| | | |
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)
<ChrisAmies@gmail.com> wrote in message
news:4032c9f1-8a11-433a-8b79-490bc07ab4f1@e39g2000hsf.googlegroups.com...
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 |
| |
| | | ChrisAmies@gmail.com | |
| | Posted: Fri Sep 05, 2008 2:45 pm Post subject: Re: Controlling Excel Automatic Calculation??? | |
| | | |
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 |
| |
|
|
