Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay Calculation of A Cell
I have a cell with the following calculation
=IF(AND(C62-C560,C70-C660),"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. -- Rob52 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay Calculation of A Cell
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* 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+S* Rob52;645889 Wrote: I have a cell with the following calculation =IF(AND(C62-C560,C70-C660),"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. -- Rob52r -- 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/sh...d.php?t=180015 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay Calculation of A Cell
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 -------------------- Simon Lloyd;645896 Wrote: 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 [b]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/sh...d.php?t=180015 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay Calculation of A Cell
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 "Simon Lloyd" wrote: 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 -------------------- Simon Lloyd;645896 Wrote: 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 [b]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/sh...d.php?t=180015 Microsoft Office Help . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay Calculation of A Cell
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 "Rob52" wrote: 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 "Simon Lloyd" wrote: 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 -------------------- Simon Lloyd;645896 Wrote: 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 [b]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/sh...d.php?t=180015 Microsoft Office Help . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delay Calculation of A Cell
Well you needn't hide an entire row, you can simply mask the cell like this (use under a button if you wish): VBA Code: -------------------- Sub Unhide_on_answer() If ActiveSheet.Range("A1").Value = "Yes" Then Range("C76").NumberFormat = "" Else Range("C76").NumberFormat = ";;;" End If End Sub -------------------- The above goes in a standard module, the below goes in the worksheet 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 protected for locked cells to be seen as such) the code would be: VBA Code: -------------------- Sub d() On Error Resume Next ActiveSheet.UsedRange = "" End Sub -------------------- Rob52;646789 Wrote: 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 "Rob52" wrote: 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 "Simon Lloyd" wrote: 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 -------------------- Simon Lloyd;645896 Wrote: 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 [b]CTRL+Sr -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 1 View this thread: 'Delay Calculation of A Cell - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=180015) 'Microsoft Office Help' (http://www.thecodecage.com) . -- 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/sh...d.php?t=180015 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell update delay | Excel Worksheet Functions | |||
Delay when writing cell information to Excel from VBA Macro | Excel Programming | |||
I have a delay in formula results when I change one cell amount | Excel Discussion (Misc queries) | |||
Delay Calculation of Worksheet Functions | Excel Worksheet Functions | |||
Calculation Delay | Excel Discussion (Misc queries) |