Cell to be mandatory fill in

[CBrausa]

I am trying to make a form and I want some cells to be a mandatory fill
in.
If there is something in column C then I would like certain cells in
that row to highlight and/or they can't go to the next field until it
is filled in.

I am self taught in Excel that in itself is scary, and my reference is
'Excel 2002 Formulas'. Not sure where or what to look under. I
remember reading something on this somewhere but now that I need it I
can't find it.

 

[Bernie Deitrick]

CBrausa,

Select all the cells that you want to have required to be filled in, and name that range "MustFill".

Then copy the code below, right-click the sheet tab, select
"View Code", and paste the code into the window that appears.

The code will select the cells that aren't filled in, one at a time. You could also use a message
bax to say "Fill this in..."

HTH,
Bernie Deitrick
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCell As Range
Dim myRange As Range

On Error GoTo NoRange
Set myRange = Range("MustFill")
For Each myCell In Range("MustFill")
If myCell.Value = "" Then
Application.EnableEvents = False
myCell.Select
Application.EnableEvents = True
Exit Sub
End If
Next myCell
NoRange:
Application.EnableEvents = True
End Sub

 

[CBrausa]

Can an IF statement be added, ie: if b10 is >0 then the cells have to be
filled in? IF b10 is <0 the cells don't have to be filled in?

 

[Bernie Deitrick]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCell As Range
Dim myRange As Range

On Error GoTo NoRange

If Range("B10").Value <= 0 Then Exit Sub

Set myRange = Range("MustFill")
For Each myCell In Range("MustFill")
If myCell.Value = "" Then
Application.EnableEvents = False
myCell.Select
Application.EnableEvents = True
Exit Sub
End If
Next myCell
NoRange:
Application.EnableEvents = True
End Sub

 

[CBrausa]

Bernie-
Thank you so much for your help. It works up to a certain point and
then it stops. Does it make a difference if two cells are merged?
Once all of the mandatory cells are filled in, can they fill in info in
the other cells in that row that pertain to them that are not
mandatory?
B10 is merged with B11. This is the cell that if something is in the
cell then the mandatory cells have to be filled in.
It jumps to the header cells that have to be filled in it then jumps
over to the first mandatory cell in the row, I enter a number and it
won't continue to the next cell. The other cells that are mandatory are
in F10/11 merged as are cells H10/11, S10/11. These have to be filled
in but the other cells in the row need info only if the buyer deems it
necessary. Where do I go from here?

 

[Bernie Deitrick]

Try this. Delete your named range, merge the cells that you want to have
merged, then select your cells for the named range and rename it. If you
merge the cells AFTER the name was created, then the second cell of each
merged cell will cause a problem.

Bernie

 

[CBrausa]

they have always been merged. It's not moving to the next mandatory
cell.
can the other cells then be filled in with what ever ifo the buyer
wants listed?

 

[Bernie Deitrick]

CBrausa,

Once the mandatory cells have been filled, then the user can move to any other cell and enter any
information that they want.

In all of my tests, the code has worked. Send me a copy of your workbook, with the mandatory cells
highlighted with color, and I will figure out what's going on in your workbook. Reply to me, then
take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP