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