Stop

  • Thread starter Thread starter StargateFan
  • Start date Start date
S

StargateFan

I always seem to come across interesting things as I make up new
workbooks. I'm using the following code to allow user to step to the
left:

ActiveCell.Offset(0, -1).Select

Butt when user gets to A1, if the button is pushed again by accident,
an error box comes up because the first cell is reached:

Run-time error '1004': Application-defined or object-defined error

Is there any way to stop this error popping up? Any advice re this?
Even a message saying "you can't go any further to the left", heck,
whatever works <g>.

Thanks. :oD
 
maybe this

Sub test()
If ActiveCell.Column <> 1 Then
ActiveCell.Offset(0, -1).Select
End If
end sub
 
You have 2 options:
- Check where the active cell is first.
If ActiveCell.Column > 1 Then ActiveCell.Offset(0, -1).Select

- Do it anyway and ignore the error, as (in this case) no damage is done or
propagated by the error.
On Error Resume Next
ActiveCell.Offset(0, -1).Select
On Error GoTo 0

NickHK
 
maybe this

Sub test()
If ActiveCell.Column <> 1 Then
ActiveCell.Offset(0, -1).Select
End If
end sub
Click to expand...

Super. It seems to work perfectly. I keep going left till it stops
yet no error comes up which is great.

Thanks. :oD
 
You have 2 options:
- Check where the active cell is first.
If ActiveCell.Column > 1 Then ActiveCell.Offset(0, -1).Select

- Do it anyway and ignore the error, as (in this case) no damage is done or
propagated by the error.
On Error Resume Next
ActiveCell.Offset(0, -1).Select
On Error GoTo 0
Click to expand...

Thanks!

What is the difference, pls, between the 2nd code above and Gary's
solution?

Sub test()
If ActiveCell.Column <> 1 Then
ActiveCell.Offset(0, -1).Select
End If
end sub

Thanks. :oD
 
The 2nd option executes the code anyway, but any error raised is simply
ignored and code continues.

In Gary's code, the potentially error causing is not executed if it would
raise an error.

NickHK
 
The 2nd option executes the code anyway, but any error raised is simply
ignored and code continues.

In Gary's code, the potentially error causing is not executed if it would
raise an error.
Click to expand...

Ah, since I didn't see anything after hitting colum A, didn't realize
that. I switched over to the one that would execute anyway, and
without error popup.

Here's what I'm now using for this:
****************************************************
Sub GoToPreviousCell()
On Error Resume Next
ActiveCell.Offset(0, -1).Select
On Error GoTo 0

With ActiveSheet
.EnableAutoFilter = True
.Protect UserInterfaceOnly:=True
End With

End Sub
****************************************************

It probably seems like overkill to add the protection code after, but
I've had users do lots of horrendous things incl unprotecting sheets
and then messing them up royally, all by accident <g>. So this will
make sure after every button they use, the sheet gets re-protected
(yet doesn't interfere with manual filtering). Just a blurb about why
the extra code at the end.

Thanks. Much appreciated. :oD
 
The 2nd option executes the code anyway, but any error raised is simply
ignored and code continues.

In Gary's code, the potentially error causing is not executed if it would
raise an error.
Click to expand...

Thanks. That completes this issue, it looks like.

Re both codes, I didn't see any difference in the result between the 2
so didn't know that this could happen. I switched over to the one you
provided that would execute anyway, and without error popup. Here's
what I'm now using:
****************************************************
Sub GoToPreviousCell()
On Error Resume Next
ActiveCell.Offset(0, -1).Select
On Error GoTo 0

With ActiveSheet
.EnableAutoFilter = True
.Protect UserInterfaceOnly:=True
End With

End Sub
****************************************************

It probably seems like overkill to add the protection code after, but
I've had users do lots of horrendous things incl unprotecting sheets
and then messing them up royally, all by accident <g>. So this will
make sure after every button they use, the sheet gets re-protected
(yet doesn't interfere with manual filtering). Just a blurb about why
the extra code at the end.

Thanks. Much appreciated. :oD
 
Back
Top