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