How to run macro automatically from range of cells

[Guest]

Hi,
I'm trying to run a macro automatically as soon as I enter specific text in
any cell of column A.

I have managed to run following code which works fine but it activates
macro as soon as I click cell in column A.

But I like to activate macro only if I add text "PAID" in any cell of column
A. Could someone please help. Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:A3000")) Is Nothing Then
Call OpenCalendar
End If
End Sub

Thanks in advance
Kevin

 

[Dave Peterson]

First, you wouldn't use the worksheet_selection change event.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

If UCase(Target.Value) = UCase("PAID") Then
Call OpenCalendar
End If
End Sub

 

[Guest]

Use Change rather than SelectionChange

 

[Guest]

Hi Dave,
Thanks a Lot, that's perfect exactly what I wanted.
There is little more help needed please:

This macro I'm intending to run is to insert date as soon as I type "PAID"
in column A. That part is ok now.

But macro is programmed to insert Date in "Active Cell" means it overwrites
"PAID" wording with date.

My question is How can I add date next to the "PAID" column instead of
overwriting active cell. e.g. If I type "PAID" in Cell A1 it activates
calendar macro and once I select date from calendar it should add date in
Cell B1 and leave PAID wording intact.

Here's the macro code I'm using:

=====================================

Private Sub cmdClose_Click()
' Close the UserForm
Unload Me
End Sub

Private Sub UserForm_Initialize()
' Check if active cell contains a date. If 'yes' show
' same date on calendar. If 'no' show today's date.
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

Private Sub Calendar1_Click()
' Transfer date selected on calendar to active cell
' and close UserForm.
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub

===================================

Thanks once again,
Kevin

 

[Dave Peterson]

If you're typing Paid, then I'd use this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

If UCase(Target.Value) = UCase("PAID") Then
application.enableevents = false
with target.offset(0,1)
.numberformat = "mm/dd/yyyy"
.value = date
end with
End If
End Sub

=======
If you're using the userform to get the date, then keep the original
Worksheet_Change event and modify your userform procedure:

Private Sub Calendar1_Click()
' Transfer date selected on calendar to active cell
' and close UserForm.
application.enableevents = false
with activecell.offset(0,1)
.numberformat = "mm/dd/yyyy"
.value = calendar1.value
end with
application.enableevents = true
Unload Me
End Sub

(Untested!)

The "application.enableevents = false" lines stop excel from firing that event
procedure when the code makes a change to the worksheet.

 

[Dave Peterson]

I left out a line!!!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

If UCase(Target.Value) = UCase("PAID") Then
application.enableevents = false
with target.offset(0,1)
.numberformat = "mm/dd/yyyy"
.value = date
end with
application.enableevents = True '<--added this line!
End If
End Sub

 

[Guest]

Thanks Dave,
I used "untested" version you sent and it works like a charm.
I really appreciate for helping out to solve this puzzle.
Cheers
Kevin,

I left out a line!!!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

If UCase(Target.Value) = UCase("PAID") Then
application.enableevents = false
with target.offset(0,1)
.numberformat = "mm/dd/yyyy"
.value = date
end with
application.enableevents = True '<--added this line!
End If
End Sub