What Is Wrong With This?

  • Thread starter Thread starter SkippyPB
  • Start date Start date
S

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
 
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
 
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
 
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.
Click to expand...

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.
Click to expand...

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
Click to expand...
////
(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
 
Steve,

Replace this line

ActiveCell.offset(rnum, cnum).Activate

with this

Target.Activate
Click to expand...


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