What Is Wrong With This?

[SkippyPB]

I have the following VBA code in my spreadsheet:

Private Sub Worksheet_Change (ByVal Target As Excel.Range)

If Target.Column = 2 Then
ColName = "EMP ID"
lchar = Left(Target.Value, 1)
If lchar = " " Then
cnum = Target.Column
rnum = Target.Row
GoTo FieldErr
End If
End If
..
..
..
..
FieldErr:

MsgBox ColName & " Cannot Begin With a Space"
ActiveCell.offset(rnum, cnum).Activate
..
..
End Sub

The issue I have is the ActiveCell instruction is not putting the
cursor or pointer back into the cell in error. It puts it several
rows and columns away.

Any help would be most appreciated.

Note that this is just one example. I need to perform the same data
validation on several columns, rows of data within the spreadsheet.

Thanks.


////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[Vergel Adriano]

Steve,

Replace this line

ActiveCell.offset(rnum, cnum).Activate

with this

Target.Activate

 

[John Bundy]

You are telling it to offset from the current spot
ActiveCell.offset(rnum, cnum).Activate
if the activecell is C3 you are moving down 3 and over 3 to F6

not sure but i think you want this
ActiveCell(rnum, cnum).Activate

 

[PCLIVE]

Seems like you may want this for your last line.

Cells(rnum, cnum).Activate

HTH,
Paul

 

[John.C.Hildreth]

As it is written, you will execute the code associated with FieldErr
every time column 2 is changed. You want to only execute that when
there is an error.

Also, you don't use the offset function to activate the cell.

Tested, but not proven...



Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 2 Then
bErr = False
ColName = "EMP ID"
lchar = Left(Target.Value, 1)
If lchar = " " Then
bErr = True
cnum = Target.Column
rnum = Target.Row
End If
End If
'
If bErr = True Then
MsgBox ColName & " Cannot Begin With a Space"
Target.Activate
End If


End Sub



HTH,
John

 

[SkippyPB]

As it is written, you will execute the code associated with FieldErr
every time column 2 is changed. You want to only execute that when
there is an error.

That was only a snippet of the code. There is a goto after all the
edits that would have prevented what you are describing.

Also, you don't use the offset function to activate the cell.

That may be, but I saw a similar example in the Execl 2003 Power
Programming with VBA by John Wallenbach.

Tested, but not proven...



Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 2 Then
bErr = False
ColName = "EMP ID"
lchar = Left(Target.Value, 1)
If lchar = " " Then
bErr = True
cnum = Target.Column
rnum = Target.Row
End If
End If
'
If bErr = True Then
MsgBox ColName & " Cannot Begin With a Space"
Target.Activate
End If


End Sub



HTH,
John

////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve

 

[SkippyPB]

Steve,

Replace this line

ActiveCell.offset(rnum, cnum).Activate

with this

Target.Activate

Perfect. Thank you very much.

Regards,
////
(o o)
-oOO--(_)--OOo-

"My luck is so bad that if I bought a cemetery,
people would stop dying."
-- Rodney Dangerfield
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Remove nospam to email me.

Steve