Mandatory Cells

[Mel1221]

Hey all!

I'm sure this question has been asked a few times before ;o).
Basically I'm looking for a validation/mandatory rule for the below.
I have a row of data (well the whole sheet actually), say A1,B1,C1,D1 where a1 can have either value of used
or Inuse. When the user selects 'Unused' from the dropdown, they will
be forced to enter in details in to the other cells. I.e Name, Department, Cost Center
etc...

I've tried an IF statement and a lil coding but I'm a bit of a novice and this has me stumped.

Any help provided would be a godsend.
cheers
Mel

 

[Zoddy]

Code

Hi Mel1221

Can be done quite easily but the code has to go in the 'Private module of the Worksheet Oblect'. Select the sheet rather than the module and change from the General view to the Worksheet view (see screen shot attached).

Putting the code here runs itself when the cells in Col A are selected (as long as they are blank). Adjust the cells you want filling by adding additional code after Cost Centre entry. Have made the code offer the options of Used & Unused rather than using a validation list as the first time you click on the cell the code runs and does not pick up the selection from the list. Try it - any problems reply back.

The code is in the screen shot but i'll repeat it here:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = "1" And ActiveCell.Offset(0, 1) = "" And Target.Value = "" Then
cellcont = MsgBox("Please click Yes for Unused or No for Used", 3)
If cellcont = vbCancel Then End
If cellcont - vbYes Then cellcont = "Used" Else cellcont = "Unused"
ActiveCell.Formula = cellcont
If cellcont = "Unused" Then
nme = InputBox("Enter Name")
If nme = "" Then ActiveCell.Formula = "": End
ActiveCell.Offset(0, 1) = nme

dept = InputBox("Enter Department")
ActiveCell.Offset(0, 2) = dept

CCent = InputBox("Enter Cost Centre")
ActiveCell.Offset(0, 3) = CCent
End If
ActiveCell.Offset(1, 1).Select

End If
End Sub

N.B The 'Then' on line 3 should be on the end of line 2 (text wrapping)

Regards

Zoddy