VISUAL BASIC EVENT WHEN EXIT A CELL

Question

I would like to display a message if the user exits a specific cell without entering data. I can use VB to display a message but don't know how to display the message when the user exits the cell without entering any data.

I have the following code, but only want it to run when the user exits the cell.

Sub Initial_CTMS_Date()

    If Cells(10, 3) = "" Then

    MsgBox "Please enter Intial CTMS Date", vbOKOnly

    End If

End Sub

I have excel 2007.

Answer 1

You need an Selection_Change event and a variable to record the previous selected cell.

Insert this in the code sheet for the desired sheet. Notice that the variable declaration has to be at the very top of the module and outside the event code, just as below:

Dim LastCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not LastCell Is Nothing Then

    If LastCell.Address = "$J$3" And LastCell.Value = "" Then

         msg=MsgBox ("Please enter Intial CTMS Date", vbOKOnly+vbExclamation, "Missing data")

    End If

End If

Set LastCell = Target

End Sub

Regards,

Per

Answer 2

Hello Harald

Then you need a Workbook_SheetChange event, which has to be placed in the codesheet for ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'look at Cell A1

If Not LastCell Is Nothing Then

    If LastCell.Address = "$A$1" And LastCell.Value = "" Then

         msg = MsgBox("Please enter a value", vbOKOnly + vbExclamation, "Missing data")

    End If

End If

Set LastCell = Target

End Sub

The code above will only fire on cell A1 in all sheets in the workbook.

The code below has to be insterted into the codesheet for ThisWorkbook as well, and will fire when a cell in column 2 is selected.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

TargetCol = 2 'Look at column 2

If Not LastCell Is Nothing Then

    If LastCell.Column = TargetCol And LastCell.Value = "" Then

         msg = MsgBox("Please enter a value", vbOKOnly + vbExclamation, "Missing data")

    End If

End If

Set LastCell = Target

End Sub

Best regards,

Per

Answer 3

You need an Selection_Change event and a variable to record the previous selected cell.

Insert this in the code sheet for the desired sheet. Notice that the variable declaration has to be at the very top of the module and outside the event code, just as below:

Dim LastCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not LastCell Is Nothing Then

    If LastCell.Address = "$J$3" And LastCell.Value = "" Then

         msg=MsgBox ("Please enter Intial CTMS Date", vbOKOnly+vbExclamation, "Missing data")

    End If

End If

Set LastCell = Target

End Sub

Regards,

Per

just found this; thanks

is there a way to 

-1- make this work for all the sheets in a workbook without putting a copy in each sheet's code?

-2- limit the effect to only one column or cell so it doesn't fire all the time

-3- is there an event for selecting a cell?

Answer 4

I don't think there is an event for exiting a cell, but there is one for entering (at least changing the selection). So if you can do this... put the word "date" in the cell then when someone goes into the cell. the event code could remove the word,and when they move to another cell the code could check if the word "date" was there or not. the only problem being that the code would fire every time the selection changes.

Regards

Steve