VBA run macro on cell click in certain column

[Kmahraz]

I know it is possible to run a macro when clicking a specific cell in a worksheet. Is is possible to run a macro when click ANY cell in a specific column.
For now i have to run the code each time, and would like to have the code run when clicking on any cell in column A,

Private Sub cmdClose_Click()
  Unload Me
End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
   On Error Resume Next
   ActiveCell.Value = DateClicked
   Unload Me
End Sub
[code]

 

[chirayu]

The only way to do that is use the Selection Change event in the WorkSheet itself instead of a module.

 

[Kmahraz]

Thank you, can you please help with the code?

 

[chirayu]

Example Code - Modify as you need:

Open a New Workbook & paste the below code in Sheet1(Sheet1) part of the VBA. Every time in Sheet1 if you click on a cell in column C it brings up message box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Column = 3 Then
        MsgBox "Hi", vbOKOnly, ""
    Else
    End If
End Sub

 

[Kmahraz]

Thank you for the help!
the code provide work; but when i incorporate with the code below it doesn't function properly.

PrivateSub cmdClose_Click()
  Unload Me
EndSub

PrivateSub MonthView1_DateClick(ByVal DateClicked AsDate)
   OnErrorResumeNext
   ActiveCell.Value = DateClicked
   Unload Me
EndSub

can you please assist?
Thank you

 

[Kmahraz]

Vs using my code to have a Date picker i was thinking i can use Selection Change Event as you recommended. The date will be placed when a user click twice on any cell in column A. Can you please verify code below and let me know if there are any change required.
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = A Then
    Range("A" & Target.Row).Value = Date
  End If
End Sub

 

[Kmahraz]

Any help Please!

 

[Nebu]

Hi
The code you have given is wrong , use the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
    Range("A" & Target.Row).Value = Date
  End If
End Sub

Double click on the any cell in column A it will work.

BTW if it is just putting today's date in column A why can't you use the shortcut key
Control+; instead of macro.

Thanks

 

[Kmahraz]

I was hoping to let users to add any date they would like and changes, the idea is to use the date picker, please see file attached, but i wanted to have them click in the cell and then the date picker popup

I would like to see if possible to incorporate both code

PrivateSub cmdClose_Click()
  Unload Me
EndSub

PrivateSub MonthView1_DateClick(ByVal DateClicked AsDate)
   OnErrorResumeNext
   ActiveCell.Value = DateClicked
   Unload Me
EndSub

AND THE CODE THAT YOU PROVIDED BELOW

[CODE]
PrivateSub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Range("A" & Target.Row).Value = Date
EndIf
EndSub


Thank you for much for the help

 

[chirayu]

Where is the code for the date pick macro. sample file please

 

[Kmahraz]

Please find bellow the code, also it attached in the sample file.
Thank you so much for the help!

Private Sub cmdClose_Click()
  Unload Me
End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
   On Error Resume Next
   ActiveCell.Value = DateClicked
   Unload Me
End Sub

 

[Kmahraz]

Any help please!

 

[NoSparks]

Not exactly what you were using but may be of use.

Makes use of a user form calendar from Trevor Eyre. (Credits included with code)

Simply imported his user form and module then this as the SelectionChange event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim dte As Date
If Target.Column = 1 And Target.Count = 1 Then
  dte = CalendarForm.GetDate
  If dte <> "12:00:00 AM" Then
  Target.Value = dte
  End If
End If

End Sub

 

[Nebu]

Hi:

Just using what you already have.

Thanks

 

[Kmahraz]

Hello Nebu,
This is great! Thank you so much for the help.