Setting Cursor Active in Cell via VB Script

[BCarter]

I have a VB script that runs to validate the string length of a cell and if
the lenght exceeds the standard I open a message box to indicate the error.
When the user presses OK I want the cursor to be active in the cell with the
error. I am using the following:

Target.Cells.Activate

But this only makes the cell active it does not open the cell for edit. If
the user moves to the next cell I don't capture the problem because they
didn't make a change in the cell. Is there a Target.Cells.Edit or something
that will open the cell with the cursor in it?

 

[Joel]

Why don't you put the data in a TEXTBOX and let the user edit the textbox.
then check the length of data before continueing

TextData = Range("A1")
do while len(TextData) > 256
msgbox("Correct Data in textbox. Length must be less than 256 character")
textbox1.visible = True
loop
textbox1.visible = False

 

[Dick Kusleika]

I have a VB script that runs to validate the string length of a cell and if
the lenght exceeds the standard I open a message box to indicate the error.
When the user presses OK I want the cursor to be active in the cell with the
error. I am using the following:

Target.Cells.Activate

But this only makes the cell active it does not open the cell for edit. If
the user moves to the next cell I don't capture the problem because they
didn't make a change in the cell. Is there a Target.Cells.Edit or something
that will open the cell with the cursor in it?

VBA and cells in Edit Mode don't work well together. Why don't use Excel's
buit in Data Validation? It has a Text Length option and you can customize
the error message. It also won't let the user leave the cell with invalid
data.