Run macro at specific time when check box is checked(manual calculation)
Multi tool use
Run macro at specific time when check box is checked(manual calculation)
I'm trying to run a macro in excel at a specific time if check box is checked.
It is working as it should, only problem is that it works only when
workbook is set to automatic calculations and I need workbook to be set to manual calculations.
Here is a VBA code I'm using in that file:
sheet1:
Private Sub Worksheet_Calculate()
Dim time_dt As Date
time_dt = Cells(1, 7)
If Range("C1").Value = "YES" Then
Application.OnTime TimeValue(time_dt), "FillFirstColumn"
End If
End Sub
and here is a Module 1 code:
Sub FillFirstColumn()
Range("A1:A20").Value = "YES"
End Sub
When I click on check box, cell D1 is changing from FALSE to TRUE. In a cell C1 I have this formula =IF(D1=TRUE,"YES","NO"). And C1 is not recalculating unless a workbook is set to automatic.
Based on advice from a comment below I've added this VBA code to sheet1:
Private Sub CheckBox1_Click()
ThisWorkbook.Worksheets("Sheet1").Cells(1, "C").Calculate
End Sub
but formula in C1 is still not recalculating.
1 Answer
1
You can Calculate all open workbooks, one workbook with many worksheets, one worksheet, a range of cells in a worksheet or a single cell.
thisworkbook.worksheets("sheet1").cells(2, "B").calculate
The above will recalculate a single cell (Sheet1!B2) in the workbook that the sub procedure was running in.
Basically the problem is that when i click on check box I need to recalculate a specific cell in order to run a macro at specific time.It workd while the workbook is set to automatic calculations but it is not working when it is set to manual calculations
– John
Jul 2 at 4:03
Yes, I understood that. My response was intended to suggest that whatever needs to be recalculated can be recalculated without xlCalculationAutomatic directly through the sub procedure that is run on the timer.
– Jeeped
Jul 2 at 4:07
I know about recalculation command you mentioned above, the problem is that I don't know how to activate recalculation of a cell when I click on a check box.
– John
Jul 2 at 4:20
Perhaps within
Private Sub CheckBox1_Click() ?– Jeeped
Jul 2 at 4:24
Private Sub CheckBox1_Click()
I've tried it but for some reason it doesn't work. When I click on check box, cell D1 is changing from FALSE to TRUE. In a cell C1 I have this formula =IF(D1=TRUE,"YES","NO"). And C1 is not recalculating unless a workbook is set to automatic.
– John
Jul 2 at 4:41
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
You might try a publicly shared link to a reputable file share (Google drive, Dropbox, etc) that allows redistribution of macro-enabled workbooks but people are generally reluctant (and rightfully so) to open macro enabled workbooks downloaded from the interweb.
– Jeeped
Jul 2 at 3:38