Newb needs macro help.

**S3NTYN3L** · 08-29-2006, 10:54 PM

Let's see, where to start...

Ok, I've got cells B5 thru B7 running different formulas based on data entered into cells B1 thru B3.
I've got each formula set to run only when the three cells contain data. (so if any one cell is blank, none of the formulas give a result).

Still with me? Good.

I've also got a "button" assigned with a macro that will clear the contents of B1:B3 when pressed.
I've got a second "button" that I'm WANTING to assign a macro that will do the following:

I want the formulas in cells B5:B7 to run ONLY when the "calculate" button is pressed. If the user enters data into B1, 2 and 3 then hits "calculate", the formulas should show their results. If the user enters different data without pressing "clear", I don't want the formulas to "auto run" like they would do by default.

I hope this make sense to you guys.
I'm going nuts trying to learn macros...

Attached is the file I'm working with so you can better visualize the stuff I've described above.

Thanks for all the help!

**davesexcel** · 08-29-2006, 11:20 PM

Place this in the worksheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
.Calculation = xlManual
End With
End Sub

This needs to go into your Clear Button

Sub Macro1()
Range("B1:B3").Select
Selection.ClearContents
Range("B1").Select
Calculate
End Sub

This needs to go into your Calculate button

Sub Rectangle4_Click()
Calculate

End Sub

in order to set calculation back to automatic you need to place this code in your workbook module before close event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.Calculation = xlAutomatic
End With

End Sub

Use excels help to find where these modules are

**S3NTYN3L** · 08-29-2006, 11:43 PM

Thanks so much davesexcel!

You're the best, that works perfectly!

I didn't find anything in Excel's help menu, but Google linked me to a site that explained where your codes shold go.

Thanks again!

**S3NTYN3L** · 08-30-2006, 11:26 AM

While the code davesexcel provided works perfectly, I was wondering...

Suppose I wanted that button macro to calculate only a certain range of cells and NOT the entire worksheet/book at one time?

What would I change to make the calculate macro work on only cells B5 B6 B7?

**davesexcel** · 09-03-2006, 06:35 AM

from cpearsons site
http://www.cpearson.com/excel/optimize.htm

Calculation Mode

Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed. This may cause your workbook to recalculate too often, which will slow down performance. You can prevent Excel from recalculating the workbook by using the statement:

Application.Calculation = xlCalculationManual

At the end of your code, you can set the calculation mode back to automatic with the statement:

Application.Calculation = xlCalculationAutomatic

Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells. If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).

**S3NTYN3L** · 09-03-2006, 04:08 PM

I put =a1+b1 into cell b3
I set the entire workbook to manual calculation.
I then insert the following code into the workbook module:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/3/2006 by S3NTYN3L
'
Application.Calculation = xlCalculationAutomatic
Range("a1:c1").Calculate
Application.Calculation = xlCalculationManual
'
End Sub

It should be calculating ONLY a1+b1 and leave the rest to be calculated manually. But the whole thing is manual.

What am I doing wrong?

**davesexcel** · 09-03-2006, 04:41 PM

maybe the range should be B3

**S3NTYN3L** · 09-03-2006, 06:51 PM

Oops, I meant c1 not b3.

**davesexcel** · 09-03-2006, 07:36 PM

'I put =a1+b1 into cell b3
I set the entire workbook to manual calculation.
I then insert the following code into the workbook module:'

You should have it in a regular module, or how are you going to be able to activate it?

**S3NTYN3L** · 09-06-2006, 10:58 AM

I stiil don't understand what I'm doing wrong.

I've got the code in a "normal" module but the whole workbook is manual calculation.

**davesexcel** · 09-06-2006, 09:16 PM

You need to place the one code in a worksheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("c1").Calculate

End Sub
the other code goes into the regular module

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/3/2006 by S3NTYN3L
'
Range("c2").Calculate
'
End Sub

Newb needs macro help.

LinkBack

Thread Tools

Rate This Thread

Display

Newb needs macro help.

Calculate a range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions