No blank cell

[gladsonjos]

Hello pals!

Well i am looking for a solution of a problem i am facing currently.

I got a excel table say A1, B1, C1.....................F1 - (cells)

Now after i enter data in cell A1, i use tab to move to next cell that
is B1, but i want data to be compulsorily input in B1 - if i move or
press tab accidentally to move further, that is to C1, i want a pop up
message to appear, "you can't proceed, unless u enter data in B1"

Do anybody can help me out in this, would be greatful if you do.

Still scratching my brains ***@@@###$$$*******

Thanks,
Cheers
Gladson.

 

[Guest]

Try this: right-click on tab , "View Code" and copy/paste code below.


Private Sub worksheet_Change(ByVal target As Range)
On Error GoTo ws_Exit
Dim col As Integer
Application.EnableEvents = False
Set ws_range = Range("$A1:$F1")
Set isect = Application.Intersect(target, ws_range)
If Not isect Is Nothing Then
If target.Column > 1 Then
For col = 2 To target.Column - 1
If Cells(1, col) = "" Then
MsgBox "You must enter " & Chr(64 + col) & "1 before continuing"
End If
Next col
End If
End If
ws_Exit:
Application.EnableEvents = True

End Sub

HTH

 

[gladsonjos]

Sorry buddy, it didnt solve my problem:

It did give me a pop up alert when i moved forward after C1, but if i
click on ok and continue further, i could go further.

What i wanted is, to restrict the user to move forward at all, unless
he completed entry into cell B1, that is even if its clicked ok after
the pop up alert, it shouldnt allow the user to move forward to the
next cell untill data is filled in B1.........any solutions buddy.

as well, i am not looking for two cells, but one cell only, if
possible one entire coloumn and not two coloumns.

Thanks for the great effort you have been putting in, appreciate that
very much.

Gud Day ahead!
Gladson

 

[Dave Peterson]

If you want to make sure that the user enters data in A1:F1 before they select
any other cell, you could use something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("a1:F1")

On Error GoTo ErrHandler:
For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) Then
Application.EnableEvents = False
myCell.Select
MsgBox "Please enter a value in: " & myCell.Address(0, 0)
Exit For
End If
Next myCell

ErrHandler:
Application.EnableEvents = True

End Sub


Personally, this would drive me nuts.

I'd put a formula in a cell (Say G1) that looked like:
=if(counta(a1:f1)=6,"","Please enter all the values")
In big red letters.