Preventing blank cells
 

I have a cell that has "P" in it by default. The only two values that should
go in this cell are "P" or "F". But at least one of those has to be in it.
How do I prevent the cell being left blank? And is there a way to ensure
that ONLY a "P" or an "F" is put in the cell?

Preventing blank cells
 

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As String
'Change callout here as desired
MyCell = "A2"

If Range(MyCell).Value = "P" Or Range(MyCell).Value = "F" Then
Exit Sub
Else
MsgBox "Please use a value of ""P"" or ""F"" in " & MyCell, , "Invalid Entry"
Application.EnableEvents = False
Range(MyCell).Select
End If
Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bishop" wrote:

I have a cell that has "P" in it by default. The only two values that should
go in this cell are "P" or "F". But at least one of those has to be in it.
How do I prevent the cell being left blank? And is there a way to ensure
that ONLY a "P" or an "F" is put in the cell?

Preventing blank cells
 

This is perfect. But what I really have is a 5X5 grid of cells that needs
this code. AND this same grid is repeated 64 times. So the first grid of
cells is:
H6:L6
H7:L7
H8:L8
H9:L9
H10:L10

the next grid is:

H14:L14
H15:L15
H16:L16
H17:L17
H18:L18

the next grid is:

H22:L22
H23:L23
H24:L24
H25:L25
H26:L26

this continues skipping 3 rows between each grid until the last grid which is:

H510:L510
H511:L511
H512:L512
H513:L513
H514:L514

How do I make this code apply to all the cells in all the grids?

"Luke M" wrote:

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As String
'Change callout here as desired
MyCell = "A2"

If Range(MyCell).Value = "P" Or Range(MyCell).Value = "F" Then
Exit Sub
Else
MsgBox "Please use a value of ""P"" or ""F"" in " & MyCell, , "Invalid Entry"
Application.EnableEvents = False
Range(MyCell).Select
End If
Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bishop" wrote:

I have a cell that has "P" in it by default. The only two values that should
go in this cell are "P" or "F". But at least one of those has to be in it.
How do I prevent the cell being left blank? And is there a way to ensure
that ONLY a "P" or an "F" is put in the cell?

Preventing blank cells
 

Hmm. For that many cells, I'd use a different approach. For the P/F, use
Data-Validation, List, input
P,F
Note that you can other various options to help your user know what's going
on. However, we still have the issue of blank cells.

For this, I'd have a cell somewhere (say Z1) with a formula
=COUNTA(H6:L10,H14:L18,etc...)=1600
*1600 = 5*5*64

Then, modify the code I gave earlier. Downside is that macro no londer
automatically finds cell that is in error, but as it should be last cell user
modified, should be easy to find.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As String
'Change callout here as desired
MyCell = "Z1"

If Range(MyCell).ValueThen
Exit Sub
Else
MsgBox "Please use a value of ""P"" or ""F"" in appropriate cells!", ,
"Invalid Entry"

End If
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bishop" wrote:

This is perfect. But what I really have is a 5X5 grid of cells that needs
this code. AND this same grid is repeated 64 times. So the first grid of
cells is:
H6:L6
H7:L7
H8:L8
H9:L9
H10:L10

the next grid is:

H14:L14
H15:L15
H16:L16
H17:L17
H18:L18

the next grid is:

H22:L22
H23:L23
H24:L24
H25:L25
H26:L26

this continues skipping 3 rows between each grid until the last grid which is:

H510:L510
H511:L511
H512:L512
H513:L513
H514:L514

How do I make this code apply to all the cells in all the grids?

"Luke M" wrote:

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyCell As String
'Change callout here as desired
MyCell = "A2"

If Range(MyCell).Value = "P" Or Range(MyCell).Value = "F" Then
Exit Sub
Else
MsgBox "Please use a value of ""P"" or ""F"" in " & MyCell, , "Invalid Entry"
Application.EnableEvents = False
Range(MyCell).Select
End If
Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bishop" wrote:

I have a cell that has "P" in it by default. The only two values that should
go in this cell are "P" or "F". But at least one of those has to be in it.
How do I prevent the cell being left blank? And is there a way to ensure
that ONLY a "P" or an "F" is put in the cell?