VBA Excel - Call macro using buttons

Question

I have written the following macro that calls a Private Sub Worksheet_Calculate() if I confirm that I want to run it. Please find below the first part of the code:

Sub test()
Dim result As VbMsgBoxResult

result = MsgBox("Run Macro?", vbYesNo, "Excel VBA")

If result = vbYes Then
    Call Worksheet_Calculate
End If

End Sub

The second code, which is Private Sub Worksheet_Calculate(), shall copy the result of the calculation performed in B2 and paste it in C2 as well as add a timestamp to D2. Columns C and D are populated as values in B2 change, so that I get a list of results and timestamps in C3 and D3, C4 and D4 etc. Here is the code:

Private Sub Worksheet_Calculate()
Dim lastrow As Long

lastrow = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row

    With Worksheets(1).Cells(lastrow, 2)
        .Offset(1, 0) = Cells(2, 1).Value
        .Offset(1, 1) = FormatDateTime(Now, vbLongTime)
    End With

End Sub

The problem that I am facing is related to the fact that my Public Sub Worksheet_Calculate() is called only once and whenever I recalculate the value of B2 nothing happens.

Is there a way to a) keep second code activated or b) have a button(s) or a tickbox that would activate/deactivate the second code?

Hope it makes sense, thanks in advance!

Where do you have the Worksheet_Calculate code, in a sheet code module or regular code module? — BigBen
– BigBen, Commented Dec 12, 2018 at 22:53
@BigBen, I have both codes in regular code module as I wanted my code to be available in any workbook I open. My intention was to have a button in the ribbon. Thanks! — ajafarov
– ajafarov, Commented Dec 12, 2018 at 22:57
@BigBen, I have never used it before but I will do some research and try to understand the idea behind it, thank you! — ajafarov
– ajafarov, Commented Dec 12, 2018 at 23:18
By "activate the second code", you want the second code to execute upon workbook calculation, right? — dcromley
– dcromley, Commented Dec 13, 2018 at 1:30

VBasic2008 · Accepted Answer · 2018-12-17 13:35:54Z

Worksheet Calculate Event

This is what you put into Module1, or what ever you will call your Module. This is a normal module created with Add Module. I've left the name of the test program intact, but I changed the other because it is used for worksheet events, particularly the Worksheet Calculate event. The test program and the variable blnCalculation enable or disable the whole thing, so in the beginning nothing will be happening before you enable it. You can create a button for it and in its Click event just add Module1.test or just run it from Macros.

Option Explicit

Public TargetValue As Variant
Public blnCalculation As Boolean

Sub test()
    Dim result As VbMsgBoxResult
    result = MsgBox("Run Macro?", vbYesNo, "Excel VBA")
    If result = vbYes Then
        blnCalculation = True
        ActiveSheet.Calculate
      Else
        blnCalculation = False
    End If
End Sub

Sub SheetCalculate()
    Dim lastrow As Long
    With ActiveSheet
    lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
        With .Cells(lastrow, 2)
            .Offset(1, 0) = .Parent.Cells(2, 1).Value
            .Offset(1, 1) = FormatDateTime(Now, vbLongTime)
        End With
    End With
End Sub

And the following is what you put into each sheet code aka Object module, which you get by double-clicking on it in VBE. The Worksheet Calculate event happens each time Excel 'decides' to calculate a worksheet, which is in your case probably guaranteed because you say that there is a formula in B2, so the event will happen every time B2 gets calculated. But it won't run the SheetCalculate program unless the value has changed which is checked with the TargetValue variable. The Worksheet Activate event happens on a worksheet e.g. each time when you select it in the tab. In this case it is used to pass the new B2 value from the newly selected worksheet to the TargetValue variable.

Option Explicit

Private Sub Worksheet_Activate()
    If Module1.blnCalculation Then _
    Module1.TargetValue = Me.Range("B2").Value
End Sub

Private Sub Worksheet_Calculate()
    If Module1.blnCalculation Then
        If Me.Range("B2").Value <> Module1.TargetValue Then
            Module1.SheetCalculate
        End If
    End If
End Sub

thank you so much! Sorry, I haven't been online in the last couple of days, I will review your solution and try to understand it and will definitely let you know, thanks again!

Collectives™ on Stack Overflow

VBA Excel - Call macro using buttons

1 Answer 1

Worksheet Calculate Event

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Worksheet Calculate Event

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related