CODEWITHSUNDEEP
excelvbabutton
ajafarov
ajafarov

Reputation: 115

VBA Excel - Call macro using buttons

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!

Upvotes: 1

Views: 2028

Answers (1)

VBasic2008
VBasic2008

Reputation: 54767

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

Upvotes: 3

Related Questions