View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Colin Hayes Colin Hayes is offline
external usenet poster
 
Posts: 465
Default 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.