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