automatically activating a button when a cell changes its value

[HUNTSSK51]

Is it possible to automatically activate or call a button when a certain cell value changes

Example

when cell A1=2 when its value changes to more than 5 ,then "commandbutton1" should get activated automatically

Thanks in advance

 

[JoeMo]

Maybe easier to put the code for the button in a standard module that can be called from a change_event macro when your desired conditions are met. If you want some help with that, post your button code (or the code assigned to the button) and tell us what cell or cells you want to monitor for changes and for each cell what change criteria you want to apply before executing the button code.

 

[HUNTSSK51]

WHEN CELL A1 REACHES MORE THAN VALUE 5 THEN THIS BUTTON SHOULD GET ACTIVATED


Sub RoundedRectangle1_Click()
ThisWorkbook.Sheets("sheet1").Range("B1:B5").ClearContents
End Sub

 

[My Aswer Is This]

A few questions.

What causes Range "A1" to change.
Is this a manual change or as the result of a formula?

And then what would cause range "A1" to go back to less then 5 ?

Here is a script which would work if you manually change the value in Range ("A1")
And it will reset Range("A1") back to Zero

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Range("A1").Value > 5 Then
        Range("B1:B5").ClearContents
        Range("A1").Value = 0
    End If
End If
End Sub