CODEWITHSUNDEEP
excelvba
Ruben0888
Ruben0888

Reputation: 3

VBA script or something else to automatically pushes a button

Is it possible to make a VBA script or something else that automatically pushes a button with a macro in it?

For example, if the cell (1:I) has WAAR in it, that the button (planning maken) is pushed automatically (see image)?

It doesn't have to be this way, if you know something else it's fine too.

enter image description here

Upvotes: 0

Views: 755

Answers (3)

Nacorid
Nacorid

Reputation: 793

Depending on which type of button it is you need to either call the Sub the button is connected to, or trigger the button_click event procedure.

Say you have a Forms button which is calling the Sub Test:

Public Sub Test()
    MsgBox "Forms Button clicked"
End Sub

In your Worksheet_Change() procedure your code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 9 And Target.Value = True Then
        Test
    End If
End Sub

If you have an ActiveX button your code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 9 And Target.Value = True Then
        Sheet1.CommandButton1.Value = True ' This triggers the click event
     End If
 End Sub

Upvotes: 0

Dominique
Dominique

Reputation: 17493

No, it is not possible to "push a button" automatically.

However, I imagine that the code behind the "push button" is something like:

Sub CommandButton_Click(Target as Range)
...
End Sub

What you can do, is create a macro that launches this procedure, but then you have the problem, when would anybody launch that macro? There, you have provided the answer (more or less) yourself: when cell "I1" gets a certain value. This, however, is not possible: you cannot declare a macro to be launched when a cell gets a certain value.
But: you can launch a macro when any cell is changed, it works as follows:

enter image description here

What does this all mean?

In the VBA project editor (left pane), you need to select your sheet, and in the source code editor, you need to go for "Worksheet" and "Change", like this you have the event which is called whenever some value changes in your worksheet.

The code itself looks as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 And Target.Column = 9 And Target.Value = True Then
    CommandButton_Click (...)
  End If
End Sub

This means that the macro will always be launched, but you only want something to happen when:

Target.Row    = 1    (which correspond with cell "I1")
Target.Column = 9    (which corresponds with cell "I1")
Target.Value  = True (which corresponds with value `WAAR`, I assume here that `WAAR` is just 
                      the Dutch translation for the boolean `True` and not some string value)

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36870

You can use Worksheet_Change event to trigger a macro. So, you have to make sub and call it from Worksheet_Change event. Sub will be same as Planning Button doing. Check below code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I1")) Is Nothing Then
    If Target = "WAAR" Then
        Call MyMacro
    End If
End If
End Sub


Sub MyMacro()
    MsgBox "Value is entered to I1 cell"
End Sub

Upvotes: 1

Related Questions