Worksheet_Change Errors
 

Hi Experts,

I need your help (please!). I'm fairly new to this, so apologies fo
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 i
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 unrelate
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 :confused

--
deanayoun
-----------------------------------------------------------------------
deanayoung's Profile: http://www.excelforum.com/member.php...fo&userid=2493
View this thread: http://www.excelforum.com/showthread.php?threadid=38475

Worksheet_Change Errors
 

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

Worksheet_Change Errors
 

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" wrote
in message ...

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 :confused:


--
deanayoung
------------------------------------------------------------------------
deanayoung's Profile:
http://www.excelforum.com/member.php...o&userid=24939
View this thread: http://www.excelforum.com/showthread...hreadid=384758

Worksheet_Change Errors
 

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" wrote
in message ...

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 :confused:


--
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

Worksheet_Change Errors
 

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" wrote in message
...
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"
wrote
in message
...

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 :confused:


--
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