Blank cell causes VB crash

C

Colin Hayes

HI

I'm using this code to transfer entries in a2:a26 individually to the
tabs along the bottom of the page :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("a2:a26"), Target) Is Nothing Then
Sheets(Target.Row + 1).Name = Target.Value
End If
End Sub


Unfortunately , this crashes out immediately if the cell is made blank
for any reason. I tried to use validation , but it hasn't worked. It
seems to confuse Excel , which doesn't know whether to crash or validate
when the error occurs.

Can someone suggest some extra code to add to the above so that it won't
crash? Perhaps a message box too to explain that blank entrees are not
allowed.

Grateful for any assistance.
 
B

Bernie Deitrick

Colin,

If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then
MsgBox "Hey! Blanks are not allowed"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
Exit Sub
End If
.....

End Sub



HTH,
Bernie
MS Excel MVP
 
M

Mike H

Colin,

Far simpler to select a2:a26 and then Data|validation|number|greater than|0

Mike
 
C

Colin Hayes

Hi All

OK thanks everyone for the solutions. I've got it working now - no more
crashes!

Thanks again.
 
Top