Determining text in a cell
 

I have built a form used by various people. I woull like to make sure they
input some valid form of data into the cell. Upon execution of the macro, I
would like to check the cell for length of characters or somthing like that.
I have tried to use the somthing like:

Dim EHS as String
Dim MyLen as Integer
range("A1").select
EHS = Selection
MyLen = Len(EHS)

If Mylen <2 then
Some type of message or input box....

When I try this I get a Runtime error 13.

Any suggestions?

ZABU

Determining text in a cell
 

"ZABU" ha scritto nel messaggio
...
I have built a form used by various people. I woull like to make sure
they
input some valid form of data into the cell. Upon execution of the macro,
I
would like to check the cell for length of characters or somthing like
that.
I have tried to use the somthing like:

Dim EHS as String
Dim MyLen as Integer
range("A1").select
EHS = Selection
MyLen = Len(EHS)

If Mylen <2 then
Some type of message or input box....

When I try this I get a Runtime error 13.


Hi, ZABU.
Try this:
-----------------------------
.....
Dim EHS As String
Dim no As Boolean
If IsError(Range("K1")) Then
no = True
Else
If Len(Range("K1").Value) < 2 Then
no = True
Else
EHS = Range("K1").Value
End If
End If

If no Then MsgBox ("Not valid")
......
-----------------------------

Determining text in a cell
 

Hi ZABU,

I think you may also try to handle Change Event of the worksheet.
The event will be fired if we change the value of one cell in the worksheet
and we then can valid the value in the cell and take the proper action.
e.g.
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Target.Address = "$A$1" Then
If Len(Target.Text) 5 Then
MsgBox "Not Valid"
Target = vbNullString
End If
End If
End Sub

<quoted
Change Event
See AlsoApplies ToExampleSpecificsOccurs when cells on the worksheet are
changed by the user or by an external link.

Private Sub Worksheet_Change(ByVal Target As Range)
Target The changed range. Can be more than one cell.

Remarks
This event doesn't occur when cells change during a recalculation. Use the
Calculate event to trap a sheet recalculation.

Example
This example changes the color of changed cells to blue.

Private Sub Worksheet_Change(ByVal Target as Range)
Target.Font.ColorIndex = 5
End Sub
</quoted

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Determining text in a cell
 

Hi ZABU,

Did my suggestion help you?
If you still have any concern on this issue, please feel free to let me
know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Determining text in a cell
 

Yes! I put the doed in and everything worked great! Thanks for your
assistance. I still have not had a chance to check teh code for interactive
program monitoring in prior posts.

Thanks Peter.

Regards,

ZABU
""Peter Huang"" wrote in message
...
Hi ZABU,

Did my suggestion help you?
If you still have any concern on this issue, please feel free to let me
know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.