I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

Steve wrote:
I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions


Hi Steve,

maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, "A1") Is Nothing Then
If Target.Value = "Integrity Remodeling" Then Call MyMacro
End If
End Sub

To use this code right click the tab of the sheet in which you need to use,
select View code and copy and past the above code. You need to change A1
with your target cell.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson

I suspect that MACRO200 is 'out of scope', meaning that the code in the
worksheet module cannot see Macro200 where ever it is.

But you can pin it down more and be su when the error occurs, choose the
[Debug] option and see what line of code is highlighted - that's the line of
code with the error in it. If it is as I suspect, then there's a simple fix
- cut and paste the code from Macro200, all the way from it's Sub Macro200()
definition to End Sub from where ever it is at and paste it into the
Worksheet code module below the end of the _Change event End Sub. Although
this might cause a problem if the code in Macro200 is referencing anything
else on another worksheet or workbook. Then you have to specifically direct
those references to them.

As I said earlier, if this is the only place that is using Macro200, you
could even replace the Call Macro200 statement with the code within it.

Also, the Option Explicit statement forces a requirement to declare all
variables before they are used via a Dim statement. Macro200 is not a
variable, should be the name of the macro you're calling, so it's 'exempt'
from that rule. But you may have undeclared variables in the Macro itself?

If you want me to take a look at it this evening, email the .xls file as an
attachment to jlatham @ jlathamsite.com (no spaces).

"Steve" wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

Dave,
There were a couple of basic problems with things, causing it not to work -
he and I are working them out via email now. Just some VB Editor/module
confusion.

I do like the suggestion for
If Target.Cells.Count 1 then Exit Sub

Along those lines, I'll ask a question here. He's making a choice from a
list but that's in a merged cell group: C1:I1 on a sheet named MASTER.
That's not triggering a _Change() event for the worksheet. Do you know if
there's a workaround for that or not? I haven't researched yet, but suspect
not. I've never done that in any of my projects, always using a list in a
single cell.

For the moment I'm triggering off of the sheet's _Change() event and at that
point just examining the contents of C1 to see if a .Find works with whatever
is in there if it's not empty and if it is not same as seen before (based on
a Static variable).

Any helpful hint(s) in that area would be gratefully accepted.


"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson

So the cell with data|validation is merged (C1:I1)?

My first recommendation would be to drop the merged cells--they can cause lots
of trouble.

===
If my list was C1:Ixx and it was merged across each row, then excel 2003 stopped
me from using that list in the data|validation dialog. I could only use one
column.

I could create a one column list, then do some merge across within that
range--and even merge across each row after data|validation was applied--but in
xl2003, the _change event still fired.

But there have been lots of problems with merged cells in different
versions--each new version seems to be better working with merged cells.

If excel doesn't fire, then common work-arounds are using a button near that
cell and training the user to click the button to invoke the code.

Or just using a dropdown or combobox and using code associated with that object.

===

And if that OP is using xl97, take a look at Debra Dalgleish's notes. There's a
problem with data|validation and event firing when the list is on a worksheet.

http://contextures.com/xlDataVal08.html#Change

(Maybe it's not the merged cells at all????)

JLatham wrote:

Dave,
There were a couple of basic problems with things, causing it not to work -
he and I are working them out via email now. Just some VB Editor/module
confusion.

I do like the suggestion for
If Target.Cells.Count 1 then Exit Sub

Along those lines, I'll ask a question here. He's making a choice from a
list but that's in a merged cell group: C1:I1 on a sheet named MASTER.
That's not triggering a _Change() event for the worksheet. Do you know if
there's a workaround for that or not? I haven't researched yet, but suspect
not. I've never done that in any of my projects, always using a list in a
single cell.

For the moment I'm triggering off of the sheet's _Change() event and at that
point just examining the contents of C1 to see if a .Find works with whatever
is in there if it's not empty and if it is not same as seen before (based on
a Static variable).

Any helpful hint(s) in that area would be gratefully accepted.

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson


--

Dave Peterson

Thank you very much for the suggestions. Confirms what I suspected - in XL
2003, the _Change event isn't firing. I think I'm going to have to stick
with the work around that I came up for him using the _SelectionChange event.
The worksheet is already laid out and one they've been using for several
years and the columns that are merged in the one row are used for data
further down the sheet, so we'd have to do a complete rebuild of the sheet to
get the data validation into a single cell and have room to display it across
the sheet where it is now.

I think I'm going to leave things as they are and see if the way things are
working now is acceptable.

Again, thank you for your time and your thoughts.

"Dave Peterson" wrote:

So the cell with data|validation is merged (C1:I1)?

My first recommendation would be to drop the merged cells--they can cause lots
of trouble.

===
If my list was C1:Ixx and it was merged across each row, then excel 2003 stopped
me from using that list in the data|validation dialog. I could only use one
column.

I could create a one column list, then do some merge across within that
range--and even merge across each row after data|validation was applied--but in
xl2003, the _change event still fired.

But there have been lots of problems with merged cells in different
versions--each new version seems to be better working with merged cells.

If excel doesn't fire, then common work-arounds are using a button near that
cell and training the user to click the button to invoke the code.

Or just using a dropdown or combobox and using code associated with that object.

===

And if that OP is using xl97, take a look at Debra Dalgleish's notes. There's a
problem with data|validation and event firing when the list is on a worksheet.

http://contextures.com/xlDataVal08.html#Change

(Maybe it's not the merged cells at all????)

JLatham wrote:

Dave,
There were a couple of basic problems with things, causing it not to work -
he and I are working them out via email now. Just some VB Editor/module
confusion.

I do like the suggestion for
If Target.Cells.Count 1 then Exit Sub

Along those lines, I'll ask a question here. He's making a choice from a
list but that's in a merged cell group: C1:I1 on a sheet named MASTER.
That's not triggering a _Change() event for the worksheet. Do you know if
there's a workaround for that or not? I haven't researched yet, but suspect
not. I've never done that in any of my projects, always using a list in a
single cell.

For the moment I'm triggering off of the sheet's _Change() event and at that
point just examining the contents of C1 to see if a .Find works with whatever
is in there if it's not empty and if it is not same as seen before (based on
a Static variable).

Any helpful hint(s) in that area would be gratefully accepted.

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson


--

Dave Peterson

Hmmm.

I use xl2003 and I could set up a merged cell with data|validation that used a
named range from another sheet as my list.

The _Change event fired in my simple tests.

Maybe you could post the exact steps you used where it didn't fire.

JLatham wrote:

Thank you very much for the suggestions. Confirms what I suspected - in XL
2003, the _Change event isn't firing. I think I'm going to have to stick
with the work around that I came up for him using the _SelectionChange event.
The worksheet is already laid out and one they've been using for several
years and the columns that are merged in the one row are used for data
further down the sheet, so we'd have to do a complete rebuild of the sheet to
get the data validation into a single cell and have room to display it across
the sheet where it is now.

I think I'm going to leave things as they are and see if the way things are
working now is acceptable.

Again, thank you for your time and your thoughts.

"Dave Peterson" wrote:

So the cell with data|validation is merged (C1:I1)?

My first recommendation would be to drop the merged cells--they can cause lots
of trouble.

===
If my list was C1:Ixx and it was merged across each row, then excel 2003 stopped
me from using that list in the data|validation dialog. I could only use one
column.

I could create a one column list, then do some merge across within that
range--and even merge across each row after data|validation was applied--but in
xl2003, the _change event still fired.

But there have been lots of problems with merged cells in different
versions--each new version seems to be better working with merged cells.

If excel doesn't fire, then common work-arounds are using a button near that
cell and training the user to click the button to invoke the code.

Or just using a dropdown or combobox and using code associated with that object.

===

And if that OP is using xl97, take a look at Debra Dalgleish's notes. There's a
problem with data|validation and event firing when the list is on a worksheet.

http://contextures.com/xlDataVal08.html#Change

(Maybe it's not the merged cells at all????)

JLatham wrote:

Dave,
There were a couple of basic problems with things, causing it not to work -
he and I are working them out via email now. Just some VB Editor/module
confusion.

I do like the suggestion for
If Target.Cells.Count 1 then Exit Sub

Along those lines, I'll ask a question here. He's making a choice from a
list but that's in a merged cell group: C1:I1 on a sheet named MASTER.
That's not triggering a _Change() event for the worksheet. Do you know if
there's a workaround for that or not? I haven't researched yet, but suspect
not. I've never done that in any of my projects, always using a list in a
single cell.

For the moment I'm triggering off of the sheet's _Change() event and at that
point just examining the contents of C1 to see if a .Find works with whatever
is in there if it's not empty and if it is not same as seen before (based on
a Static variable).

Any helpful hint(s) in that area would be gratefully accepted.

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Give me a day or two to build up an example of both the code I was using -
and the setup and I'll post here in response to your last posting above and
maybe you'll uncover my problem. In the meantime, Steve has found the
current situation acceptable, so at least there's no immediate rush to
actually get it working properly.

"Dave Peterson" wrote:

Hmmm.

I use xl2003 and I could set up a merged cell with data|validation that used a
named range from another sheet as my list.

The _Change event fired in my simple tests.

Maybe you could post the exact steps you used where it didn't fire.

JLatham wrote:

Thank you very much for the suggestions. Confirms what I suspected - in XL
2003, the _Change event isn't firing. I think I'm going to have to stick
with the work around that I came up for him using the _SelectionChange event.
The worksheet is already laid out and one they've been using for several
years and the columns that are merged in the one row are used for data
further down the sheet, so we'd have to do a complete rebuild of the sheet to
get the data validation into a single cell and have room to display it across
the sheet where it is now.

I think I'm going to leave things as they are and see if the way things are
working now is acceptable.

Again, thank you for your time and your thoughts.

"Dave Peterson" wrote:

So the cell with data|validation is merged (C1:I1)?

My first recommendation would be to drop the merged cells--they can cause lots
of trouble.

===
If my list was C1:Ixx and it was merged across each row, then excel 2003 stopped
me from using that list in the data|validation dialog. I could only use one
column.

I could create a one column list, then do some merge across within that
range--and even merge across each row after data|validation was applied--but in
xl2003, the _change event still fired.

But there have been lots of problems with merged cells in different
versions--each new version seems to be better working with merged cells.

If excel doesn't fire, then common work-arounds are using a button near that
cell and training the user to click the button to invoke the code.

Or just using a dropdown or combobox and using code associated with that object.

===

And if that OP is using xl97, take a look at Debra Dalgleish's notes. There's a
problem with data|validation and event firing when the list is on a worksheet.

http://contextures.com/xlDataVal08.html#Change

(Maybe it's not the merged cells at all????)

JLatham wrote:

Dave,
There were a couple of basic problems with things, causing it not to work -
he and I are working them out via email now. Just some VB Editor/module
confusion.

I do like the suggestion for
If Target.Cells.Count 1 then Exit Sub

Along those lines, I'll ask a question here. He's making a choice from a
list but that's in a merged cell group: C1:I1 on a sheet named MASTER.
That's not triggering a _Change() event for the worksheet. Do you know if
there's a workaround for that or not? I haven't researched yet, but suspect
not. I've never done that in any of my projects, always using a list in a
single cell.

For the moment I'm triggering off of the sheet's _Change() event and at that
point just examining the contents of C1 to see if a .Find works with whatever
is in there if it's not empty and if it is not same as seen before (based on
a Static variable).

Any helpful hint(s) in that area would be gratefully accepted.

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Just keep track of the steps you did when you created the workbook that had the
problem.

(plain text is better than an attachment)

JLatham wrote:

Give me a day or two to build up an example of both the code I was using -
and the setup and I'll post here in response to your last posting above and
maybe you'll uncover my problem. In the meantime, Steve has found the
current situation acceptable, so at least there's no immediate rush to
actually get it working properly.

"Dave Peterson" wrote:

Hmmm.

I use xl2003 and I could set up a merged cell with data|validation that used a
named range from another sheet as my list.

The _Change event fired in my simple tests.

Maybe you could post the exact steps you used where it didn't fire.

JLatham wrote:

Thank you very much for the suggestions. Confirms what I suspected - in XL
2003, the _Change event isn't firing. I think I'm going to have to stick
with the work around that I came up for him using the _SelectionChange event.
The worksheet is already laid out and one they've been using for several
years and the columns that are merged in the one row are used for data
further down the sheet, so we'd have to do a complete rebuild of the sheet to
get the data validation into a single cell and have room to display it across
the sheet where it is now.

I think I'm going to leave things as they are and see if the way things are
working now is acceptable.

Again, thank you for your time and your thoughts.

"Dave Peterson" wrote:

So the cell with data|validation is merged (C1:I1)?

My first recommendation would be to drop the merged cells--they can cause lots
of trouble.

===
If my list was C1:Ixx and it was merged across each row, then excel 2003 stopped
me from using that list in the data|validation dialog. I could only use one
column.

I could create a one column list, then do some merge across within that
range--and even merge across each row after data|validation was applied--but in
xl2003, the _change event still fired.

But there have been lots of problems with merged cells in different
versions--each new version seems to be better working with merged cells.

If excel doesn't fire, then common work-arounds are using a button near that
cell and training the user to click the button to invoke the code.

Or just using a dropdown or combobox and using code associated with that object.

===

And if that OP is using xl97, take a look at Debra Dalgleish's notes. There's a
problem with data|validation and event firing when the list is on a worksheet.

http://contextures.com/xlDataVal08.html#Change

(Maybe it's not the merged cells at all????)

JLatham wrote:

Dave,
There were a couple of basic problems with things, causing it not to work -
he and I are working them out via email now. Just some VB Editor/module
confusion.

I do like the suggestion for
If Target.Cells.Count 1 then Exit Sub

Along those lines, I'll ask a question here. He's making a choice from a
list but that's in a merged cell group: C1:I1 on a sheet named MASTER.
That's not triggering a _Change() event for the worksheet. Do you know if
there's a workaround for that or not? I haven't researched yet, but suspect
not. I've never done that in any of my projects, always using a list in a
single cell.

For the moment I'm triggering off of the sheet's _Change() event and at that
point just examining the contents of C1 to see if a .Find works with whatever
is in there if it's not empty and if it is not same as seen before (based on
a Static variable).

Any helpful hint(s) in that area would be gratefully accepted.

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I'm beginning to get a handle on this thing.

The _Change() event is firing, but there's a cell on the worksheet that is
set up to a
UDF that looks like this:
Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

the cell (M1) itself is setup as =DocProps("last save time")
it ends up placing a date into the cell. But it seems to be interrupting
the flow inside of the _Change() event processing, so it never completes, and
that was fooling me into thinking it wasn't firing.

Now that I've got another clue as to what's happening here, I can probably
fix it and actually get it to work as it should instead of with the
work-around (which Steve says is just fine for him) . First I've got to bone
up on Application.Volatile to see what effect that's having - not something I
believe I've ever used. It's always interesting to see what other people
have used to find a solution. Definite learning experience. Makes me
realize that even after working with Excel since at least the days of Office
4.5, that there are many corners in it that I've not turned yet.

I want to thank you again for taking the time and effort to try the
_Change() event setup with merged validated cells and letting me know it did
work for you. If you hadn't done that, I probably would not have worked this
far in trying to find out just what the heck is going on here.

Since this particular routine is actually going to set the values of some
other cells on the same worksheet, I'll probably end up moving the function
inside of the DocProps UDF into this routine and that in itself will probably
cure the whole problem.

What is happening is that during the _Change() processing suddenly that
"Dave Peterson" wrote:

Hmmm.

I use xl2003 and I could set up a merged cell with data|validation that used a
named range from another sheet as my list.

The _Change event fired in my simple tests.

Maybe you could post the exact steps you used where it didn't fire.

JLatham wrote:

Thank you very much for the suggestions. Confirms what I suspected - in XL
2003, the _Change event isn't firing. I think I'm going to have to stick
with the work around that I came up for him using the _SelectionChange event.
The worksheet is already laid out and one they've been using for several
years and the columns that are merged in the one row are used for data
further down the sheet, so we'd have to do a complete rebuild of the sheet to
get the data validation into a single cell and have room to display it across
the sheet where it is now.

I think I'm going to leave things as they are and see if the way things are
working now is acceptable.

Again, thank you for your time and your thoughts.

"Dave Peterson" wrote:

So the cell with data|validation is merged (C1:I1)?

My first recommendation would be to drop the merged cells--they can cause lots
of trouble.

===
If my list was C1:Ixx and it was merged across each row, then excel 2003 stopped
me from using that list in the data|validation dialog. I could only use one
column.

I could create a one column list, then do some merge across within that
range--and even merge across each row after data|validation was applied--but in
xl2003, the _change event still fired.

But there have been lots of problems with merged cells in different
versions--each new version seems to be better working with merged cells.

If excel doesn't fire, then common work-arounds are using a button near that
cell and training the user to click the button to invoke the code.

Or just using a dropdown or combobox and using code associated with that object.

===

And if that OP is using xl97, take a look at Debra Dalgleish's notes. There's a
problem with data|validation and event firing when the list is on a worksheet.

http://contextures.com/xlDataVal08.html#Change

(Maybe it's not the merged cells at all????)

JLatham wrote:

Dave,
There were a couple of basic problems with things, causing it not to work -
he and I are working them out via email now. Just some VB Editor/module
confusion.

I do like the suggestion for
If Target.Cells.Count 1 then Exit Sub

Along those lines, I'll ask a question here. He's making a choice from a
list but that's in a merged cell group: C1:I1 on a sheet named MASTER.
That's not triggering a _Change() event for the worksheet. Do you know if
there's a workaround for that or not? I haven't researched yet, but suspect
not. I've never done that in any of my projects, always using a list in a
single cell.

For the moment I'm triggering off of the sheet's _Change() event and at that
point just examining the contents of C1 to see if a .Find works with whatever
is in there if it's not empty and if it is not same as seen before (based on
a Static variable).

Any helpful hint(s) in that area would be gratefully accepted.

"Dave Peterson" wrote:

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



Steve wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson