I hope not a dumb question.....
 

I have a worksheet that has several macros and event macros in it. All
the macros work as I have used them several times before.

However, one of my endusers ran into an issue with one of my Event
Macro's... a worksheet change event. She had entered into a cell, a
large amount of text (without counting, I would say 200+ characters).
When she tries to enter more text, she gets a "Type Mismatch Error"
with the event macro.

I hope this is not a dumb question.....is there some sort of data limit
for a cell that is being violated to create this error?

Thanks for any help!!!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=550134

I hope not a dumb question.....
 

As always, post your code for comments.
http://tinyurl.com/hgzsf
--
Don Guillett
SalesAid Software

"Celt" wrote in message
...

I have a worksheet that has several macros and event macros in it. All
the macros work as I have used them several times before.

However, one of my endusers ran into an issue with one of my Event
Macro's... a worksheet change event. She had entered into a cell, a
large amount of text (without counting, I would say 200+ characters).
When she tries to enter more text, she gets a "Type Mismatch Error"
with the event macro.

I hope this is not a dumb question.....is there some sort of data limit
for a cell that is being violated to create this error?

Thanks for any help!!!


--
Celt
------------------------------------------------------------------------
Celt's Profile:
http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=550134

I hope not a dumb question.....
 

Here is the code.....

I know it works. It works on ever cell on the page except for one wit
a lot of text in it.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngA As Range, rngB As Range, cell As Range


If IsNumeric(Target.Value) = True Then
If Target.Value < 0 Then
Target.Value = Abs(Target.Value)
End If
If Int(Target.Value) - (Target.Value) = 0 Then
ActiveSheet.Unprotect Password:="finance"
Target.Interior.ColorIndex = xlNone
ActiveSheet.Protect Password:="finance", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True
AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
End If

If Not IsNumeric(Target.Value) = True Then

'-I used the On Error Resume Next / On Error Goto 0 statements to forc
the code to ingnore my "too much text" issue......if that is what th
problem is-

On Error Resume Next
With Worksheets("Budget Summary")
Set rngA = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With
For Each cell In Target
Set rngB = rngA.Find( _
What:=Target.Value, _
After:=rngA(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngB Is Nothing Then
ActiveSheet.Unprotect Password:="finance"
Target.Interior.ColorIndex = xlNone
ActiveSheet.Protect Password:="finance", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True
AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
On Error GoTo 0
If IsEmpty(cell) Then
ActiveSheet.Unprotect Password:="finance"
cell.Interior.ColorIndex = xlNone
ActiveSheet.Protect Password:="finance", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True
AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
Next
End If


End Su

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=55013