Blank cell causes VB crash
 

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.

Blank cell causes VB crash
 

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


"Colin Hayes" wrote in message
...

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.

Blank cell causes VB crash
 

Colin,

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

Mike

"Colin Hayes" wrote:


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.

Blank cell causes VB crash
 

If Target.Cells.Count 1 or target="" or target=" " Then Exit Sub


--
Don Guillett
SalesAid Software

"Colin Hayes" wrote in message
...

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.

Blank cell causes VB crash
 

Try putting in

On Error Resume Next

"Colin Hayes" wrote:


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.

Blank cell causes VB crash
 

Hi All

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

Thanks again.


In article ,
AKphidelt writes
Try putting in

On Error Resume Next

"Colin Hayes" wrote:


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.