how to call command button click event based on cell value?

Question

i have a shipment tracking table with many records, each represent a shipment and in a different sheet i have an "Arrival Notification" template , with a command button to update template values and send automatic email to client once their shipment arrives

back to the table, column "Z" has the shipment arrival date column "AH" for the arrival notification .. it can have 2 values only (SEND, SENT) .. SEND for shipments with no notification sent yet , and SENT for notifications already sent

WHAT I WANT IS : for each cell in col "AH" with the value "SEND" only, once clicked to run the code CommandButton1_Click from the second sheet and then change cell value to "SENT"

*what i did was as below , but not working

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = Range("AH1").Column Then
Call CommandButton1_Click
Else
Range("AH" & Target.Row).Value = "SENT"
  End If
End Sub

Use the `Worksheet_SelectionChange` event and check the address of the `Target` argument. — braX, Jan 18 '20 at 09:45
hey @braX i'm a total beginner btw .. i guess i need explanation for your comments , sorry — Mohamed Rizk, Jan 22 '20 at 19:12
I added an answer that hopefully explains it well enough for you. — braX, Jan 22 '20 at 21:27

score 0 · Answer 1 · answered Jan 22 '20 at 21:27

First, find the SelectionChange event for the Worksheet (this is important, as you cannot just place this code anywhere... it has to be in that event.

Then paste this code into it.

If Target.Column = 26 Then ' column Z
  CommandButton1_Click
  Range("AH" & Target.Row).Value = "SENT"
End If

When you are done, it should look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = 26 Then ' column Z
    CommandButton1_Click
    Range("AH" & Target.Row).Value = "SENT"
  End If
End Sub

how to call command button click event based on cell value?

1 Answers1