Delay Calculation of A Cell

[Rob52]

I have a cell with the following calculation

=IF(AND(C62-C56>0,C70-C66>0),"Yes - Loan Should be
Approved",IF(AND(C62-C56<0,C70-C66<0),"No - Speak to your Advisor.","Possible
- Depending on Detailed Application, speak to your Banker"))

Now it works ok but I want to stop it calculating until the "user" requests
an answer.

The second part is how do i tell the spreadsheet to remove all data in
"unprotected cells"?

The idea the user opens the spreadsheet completes a series of cells then
selects answer to display then closes out without saving the data so the the
spreadsheet is blank on reopening.

 

[Simon Lloyd]

Perhaps something along these lines but i need more information fro
you



VBA Code:
--------------------


Private Sub Worksheet_SelectionChange(ByVal Target As Range
If Me.Range("A1").Value = "No" The
Application.Calculation = xlCalculationManua
Els
Application.Calculation = xlCalculationManua
End I
End Sub
--------------------




*How to Save a Worksheet Event Macro*
1. *Copy* the macro above placing the cursor to the left of the cod
box hold the *CTRL & Left Click,* then *Right Click* selected code an
*Copy.*
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*




I have a cell with the following calculatio

=IF(AND(C62-C56>0,C70-C66>0),"Yes - Loan Should b
Approved",IF(AND(C62-C56<0,C70-C66<0),"No - Speak to you
Advisor.","Possibl
- Depending on Detailed Application, speak to your Banker")

Now it works ok but I want to stop it calculating until the "user
request
an answer

The second part is how do i tell the spreadsheet to remove all data i
"unprotected cells"

The idea the user opens the spreadsheet completes a series of cell
the
selects answer to display then closes out without saving the data s
the th
spreadsheet is blank on reopening
-
Rob52


--
Simon Lloyd

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Simon Lloyd]

Sorry Rob that should have read


VBA Code:
--------------------


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("A1").Value = "No" Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomati
End If
End Sub
--------------------




Perhaps something along these lines but i need more information fro
you

VBA Code:
--------------------Private Sub Worksheet_SelectionChange(ByVal Target As Range
If Me.Range("A1").Value = "No" The
Application.Calculation = xlCalculationManua
Els
Application.Calculation = xlCalculationManua
End I
End Sub
--------------------

HOW TO SAVE A WORKSHEET EVENT MACRO[/B]

1. *COPY* THE MACRO ABOVE PLACING THE CURSOR TO THE LEFT OF THE COD
BOX HOLD THE *CTRL & LEFT CLICK,* THEN *RIGHT CLICK* SELECTED CODE AN
*COPY.*
2. OPEN YOUR WORKBOOK AND *RIGHT CLICK* ON THE *WORKSHEET'S NAME TAB
FOR THE WORKSHEET THE MACRO WILL RUN ON.
3. *LEFT CLICK* ON *VIEW CODE* IN THE POP UP MENU.
4. *PASTE* THE MACRO CODE USING *CTRL+V*
5. MAKE ANY CUSTOM CHANGES TO THE MACRO IF NEEDED AT THIS TIME.
6. *SAVE* THE MACRO IN YOUR WORKBOOK USING CTRL+S


--
Simon Lloyd

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Rob52]

Thank you for the code.

The equation I gave you sits in cell C76. I have a series of questions that
the user answers. I want them to answer the questions and once finished,
somehow have the answer displayed (but not before) perhaps click a button.
These are other calculations that are made during the course of answering the
questions - auto calculation of these is okay.

Perhaps hiding the cell or row until we click a button?

In regards to the second part of my question we can leave that out for now.


--
Rob52

Sorry Rob that should have read:

VBA Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("A1").Value = "No" Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If
End Sub Perhaps something along these lines but i need more information from
you:

VBA Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("A1").Value = "No" Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationManual
End If
End Sub

--------------------



HOW TO SAVE A WORKSHEET EVENT MACRO[/B]

1. *COPY* THE MACRO ABOVE PLACING THE CURSOR TO THE LEFT OF THE CODE
BOX HOLD THE *CTRL & LEFT CLICK,* THEN *RIGHT CLICK* SELECTED CODE AND
*COPY.*
2. OPEN YOUR WORKBOOK AND *RIGHT CLICK* ON THE *WORKSHEET'S NAME TAB*
FOR THE WORKSHEET THE MACRO WILL RUN ON.
3. *LEFT CLICK* ON *VIEW CODE* IN THE POP UP MENU.
4. *PASTE* THE MACRO CODE USING *CTRL+V*
5. MAKE ANY CUSTOM CHANGES TO THE MACRO IF NEEDED AT THIS TIME.
6. *SAVE* THE MACRO IN YOUR WORKBOOK USING CTRL+Sr


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180015

Microsoft Office Help

.

 

[Rob52]

I have now created a check box to hide row 70. The code was gleamed from
previous posting to this area.

Private Sub CheckBox1_Click()

If CheckBox1 = True Then
ActiveSheet.Cells.EntireRow("70").Hidden = False
Else
ActiveSheet.Cells.EntireRow("70").Hidden = True
End If

End Sub

This should work. Thanks for your help.


--
Rob52

Thank you for the code.

The equation I gave you sits in cell C76. I have a series of questions that
the user answers. I want them to answer the questions and once finished,
somehow have the answer displayed (but not before) perhaps click a button.
These are other calculations that are made during the course of answering the
questions - auto calculation of these is okay.

Perhaps hiding the cell or row until we click a button?

In regards to the second part of my question we can leave that out for now.


--
Rob52

Sorry Rob that should have read:

VBA Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("A1").Value = "No" Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If
End Sub Perhaps something along these lines but i need more information from
you:

VBA Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("A1").Value = "No" Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationManual
End If
End Sub

--------------------



HOW TO SAVE A WORKSHEET EVENT MACRO[/B]

1. *COPY* THE MACRO ABOVE PLACING THE CURSOR TO THE LEFT OF THE CODE
BOX HOLD THE *CTRL & LEFT CLICK,* THEN *RIGHT CLICK* SELECTED CODE AND
*COPY.*
2. OPEN YOUR WORKBOOK AND *RIGHT CLICK* ON THE *WORKSHEET'S NAME TAB*
FOR THE WORKSHEET THE MACRO WILL RUN ON.
3. *LEFT CLICK* ON *VIEW CODE* IN THE POP UP MENU.
4. *PASTE* THE MACRO CODE USING *CTRL+V*
5. MAKE ANY CUSTOM CHANGES TO THE MACRO IF NEEDED AT THIS TIME.
6. *SAVE* THE MACRO IN YOUR WORKBOOK USING CTRL+Sr


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180015

Microsoft Office Help

.

 

[Simon Lloyd]

Well you needn't hide an entire row, you can simply mask the cell lik
this (use under a button if you wish)


VBA Code:
--------------------


Sub Unhide_on_answer(
If ActiveSheet.Range("A1").Value = "Yes" The
Range("C76").NumberFormat = "
Els
Range("C76").NumberFormat = ";;;
End I
End Sub
--------------------


The above goes in a standard module, the below goes in the workshee
code module


VBA Code:
--------------------


Private Sub Worksheet_Activate(
Me.Range("C76").NumberFormat = ";;;
End Sub
--------------------


and to clear all unlocked cells on a PROTECTED (it must be protecte
for locked cells to be seen as such) the code would be


VBA Code:
--------------------


Sub d(
On Error Resume Nex
ActiveSheet.UsedRange = "
End Su

--------------------






I have now created a check box to hide row 70. The code was gleame
fro
previous posting to this area

Private Sub CheckBox1_Click(

If CheckBox1 = True The
ActiveSheet.Cells.EntireRow("70").Hidden = Fals
Els
ActiveSheet.Cells.EntireRow("70").Hidden = Tru
End I

End Su

This should work. Thanks for your help


-
Rob5

Thank you for the code

The equation I gave you sits in cell C76. I have a series o questions tha
the user answers. I want them to answer the questions and onc finished
somehow have the answer displayed (but not before) perhaps click button
These are other calculations that are made during the course o answering th
questions - auto calculation of these is okay

Perhaps hiding the cell or row until we click a button

In regards to the second part of my question we can leave that ou

for now

-
Rob5

Sorry Rob that should have read

VBA Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range
If Me.Range("A1").Value = "No" The
Application.Calculation = xlCalculationManua
Els
Application.Calculation = xlCalculationAutomati
End I
End Su
-------------------

Simon Lloyd;645896 Wrote

Perhaps something along these lines but i need more informatio fro
you

VBA Code
-------------------


Private Sub Worksheet_SelectionChange(ByVal Target As Range
If Me.Range("A1").Value = "No" The
Application.Calculation = xlCalculationManua
Els
Application.Calculation = xlCalculationManua
End I
End Su
-------------------

HOW TO SAVE A WORKSHEET EVENT MACRO[/B

1. *COPY* THE MACRO ABOVE PLACING THE CURSOR TO THE LEFT OF TH COD
BOX HOLD THE *CTRL & LEFT CLICK,* THEN *RIGHT CLICK* SELECTED COD AN
*COPY.
2. OPEN YOUR WORKBOOK AND *RIGHT CLICK* ON THE *WORKSHEET'S NAM TAB
FOR THE WORKSHEET THE MACRO WILL RUN ON
3. *LEFT CLICK* ON *VIEW CODE* IN THE POP UP MENU
4. *PASTE* THE MACRO CODE USING *CTRL+V
5. MAKE ANY CUSTOM CHANGES TO THE MACRO IF NEEDED AT THIS TIME
6. *SAVE* THE MACRO IN YOUR WORKBOOK USING CTRL+S

-
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help

Microsoft Office Discussion' (http://www.thecodecage.com)

-----------------------------------------------------------------------

Forums' (http://www.thecodecage.com/forumz/showthread.php?t=180015

--
Simon Lloyd

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com)