Blank cell causes VB crash

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
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.
 
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
 
Hi All

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

Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Restricting Cell entry to certain letters 7
Trying to run excel vba code when cell value changes from empty to entered value. 1
Excel Worksheet_change with 2 functions to insert date and hyperlink window 0
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0
Code in certain sheets 1
VB CODE 7
Run-time error 1004: Unable to set the left property of the OLEObject class 0
Cell change event causing EXCEL to crash 3

Back
Top