Worksheet_Change Errors

Hi Experts,

I need your help (please!). I'm fairly new to this, so apologies for the dumb questions.

I'm using the same Worksheet_Change macro on several different sheets, to call a different macro, which is saved on a general module sheet.

It should be fairly simple, when a particular cell changes, then it calls the macro.

The exact code is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target = Range("C1") Then
Call FindSheet
End If
End Sub

The macro runs fine but if I drag and drop a completely unrelated formula anywhere on the same sheet, I get the following error message:

Run-time error 13. Type Mismatch.

Any ideas?

Obviously, any help would be gratefully recieved!

Thanks

Dean

Hi Dean,

At a glance I would say you need to tell the code what to do with
Range("C1").

IE: If Target = Range("C1").Value Then ...

If this still isn't working then post back.

Regards,

James

Hi Deana,

> If Target = Range("C1") Then

This line is not doing what you expect it to; it compares the target to the
value of C1.

Therefore, if you copy/drag to a multicell range, the sub will error because
a multicell range does not have a value property

Try instead:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "C1" Then
Call FindSheet
End If
End Sub

---
Regards,
Norman



"deanayoung" <[email protected]> wrote
in message news:[email protected]...
>
> Hi Experts,
>
> I need your help (please!). I'm fairly new to this, so apologies for
> the dumb questions.
>
> I'm using the same Worksheet_Change macro on several different sheets,
> to call a different macro, which is saved on a general module sheet.
>
> It should be fairly simple, when a particular cell changes, then it
> calls the macro.
>
> The exact code is:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Target = Range("C1") Then
> Call FindSheet
> End If
> End Sub
>
> The macro runs fine but if I drag and drop a completely unrelated
> formula anywhere on the same sheet, I get the following error message:
>
> Run-time error 13. Type Mismatch.
>
> Any ideas?
>
> Obviously, any help would be gratefully recieved!
>
> Thanks
>
> Dean
>
>
> --
> deanayoung
> ------------------------------------------------------------------------
> deanayoung's Profile:
> http://www.excelforum.com/member.php...o&userid=24939
> View this thread: http://www.excelforum.com/showthread...hreadid=384758
>

I think you meant:

If Target.Address = "$C$1" Then
or
If Target.Address(0,0) = "C1" Then

I like this style:

if intersect(target,me.range("c1")) is nothing then exit sub

I find it easier to extend the range to multiple cells.



Norman Jones wrote:
>
> Hi Deana,
>
> > If Target = Range("C1") Then
>
> This line is not doing what you expect it to; it compares the target to the
> value of C1.
>
> Therefore, if you copy/drag to a multicell range, the sub will error because
> a multicell range does not have a value property
>
> Try instead:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>
> If Target.Address = "C1" Then
> Call FindSheet
> End If
> End Sub
>
> ---
> Regards,
> Norman
>
> "deanayoung" <[email protected]> wrote
> in message news:[email protected]...
> >
> > Hi Experts,
> >
> > I need your help (please!). I'm fairly new to this, so apologies for
> > the dumb questions.
> >
> > I'm using the same Worksheet_Change macro on several different sheets,
> > to call a different macro, which is saved on a general module sheet.
> >
> > It should be fairly simple, when a particular cell changes, then it
> > calls the macro.
> >
> > The exact code is:
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > If Target = Range("C1") Then
> > Call FindSheet
> > End If
> > End Sub
> >
> > The macro runs fine but if I drag and drop a completely unrelated
> > formula anywhere on the same sheet, I get the following error message:
> >
> > Run-time error 13. Type Mismatch.
> >
> > Any ideas?
> >
> > Obviously, any help would be gratefully recieved!
> >
> > Thanks
> >
> > Dean
> >
> >
> > --
> > deanayoung
> > ------------------------------------------------------------------------
> > deanayoung's Profile:
> > http://www.excelforum.com/member.php...o&userid=24939
> > View this thread: http://www.excelforum.com/showthread...hreadid=384758
> >

--

Dave Peterson

Hi Dave,

*Invariably*, I use the construct:

> if intersect(target,me.range("c1")) is nothing then exit sub
>
> I find it easier to extend the range to multiple cells.

I agree, but for once I did vary!

---
Regards,
Norman



"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I think you meant:
>
> If Target.Address = "$C$1" Then
> or
> If Target.Address(0,0) = "C1" Then
>
> I like this style:
>
> if intersect(target,me.range("c1")) is nothing then exit sub
>
> I find it easier to extend the range to multiple cells.
>
>
>
> Norman Jones wrote:
>>
>> Hi Deana,
>>
>> > If Target = Range("C1") Then
>>
>> This line is not doing what you expect it to; it compares the target to
>> the
>> value of C1.
>>
>> Therefore, if you copy/drag to a multicell range, the sub will error
>> because
>> a multicell range does not have a value property
>>
>> Try instead:
>>
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>>
>> If Target.Address = "C1" Then
>> Call FindSheet
>> End If
>> End Sub
>>
>> ---
>> Regards,
>> Norman
>>
>> "deanayoung" <[email protected]>
>> wrote
>> in message
>> news:[email protected]...
>> >
>> > Hi Experts,
>> >
>> > I need your help (please!). I'm fairly new to this, so apologies for
>> > the dumb questions.
>> >
>> > I'm using the same Worksheet_Change macro on several different sheets,
>> > to call a different macro, which is saved on a general module sheet.
>> >
>> > It should be fairly simple, when a particular cell changes, then it
>> > calls the macro.
>> >
>> > The exact code is:
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> > If Target = Range("C1") Then
>> > Call FindSheet
>> > End If
>> > End Sub
>> >
>> > The macro runs fine but if I drag and drop a completely unrelated
>> > formula anywhere on the same sheet, I get the following error message:
>> >
>> > Run-time error 13. Type Mismatch.
>> >
>> > Any ideas?
>> >
>> > Obviously, any help would be gratefully recieved!
>> >
>> > Thanks
>> >
>> > Dean
>> >
>> >
>> > --
>> > deanayoung
>> > ------------------------------------------------------------------------
>> > deanayoung's Profile:
>> > http://www.excelforum.com/member.php...o&userid=24939
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=384758
>> >
>
> --
>
> Dave Peterson