Best way to assign data validation?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

Column C either contains Item ID numbers or on some rows is merged across to
Col. F to contain a text label. I need to ensure a Group ID is in Col. B if
an Item ID is in Col. C. I can't use "blank", because it picks up the text
label. What is the best way to assign a data validation to this? Or should
I try a VBA solution?

Ed
 
I have no control over when the user will insert the text label. I was
thinking about assigning the validation to the whole column; that way, the
user could put a label anywhere and the validation wouldn't mind. If I
assigned only a certain number of rows and the user did something
unexpected, it would throw off the whole thing.

A thought: can I use ISNUMERIC or a similar function in validation? "If Col
C is not blank and ISNUMERIC, then Col B can not be blank" ???

Ed
 
Hi Ed

here's a vba solution that might meet your needs - every time an entry is
made into column C column B is checked for data unless column C is merged.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Target.MergeCells Then
Exit Sub
ElseIf Target.Value <> "" Then
If Target.Offset(0, -1).Value = "" Then
MsgBox "Cell " & Target.Offset(0, -1).Address & " must not
be blank."
End If
End If
End If
End Sub

---
to use this code, right mouse click on the sheet tab where you want it to
apply to and choose view code
then copy & paste it into the code window
if any lines turn red, click at the end of them and press the delete key,
this should fix up any line wrap problems

hope this helps
Cheers
JulieD
 
Thank you, Julie. One question, though - this sheet already has a
Workbook_SheetSelectionChange macro. Would these two conflict on the same
sheet?

Ed
 
Hi Ed

According to the information on events on Chip Pearson's website
(http://www.cpearson.com/excel/events.htm) the workbook_sheetselectionchange
event is like the worksheet_selectionchange event which fires when the
active selection moves to a new range, whereas the worksheet_change event
fires when a cell value is changed.

i quickly tested a workbook with both bits of code in it and it seems to
work okay together. When a range is selected the
workbook_sheetselectionchange event fires
if the cell value in the range changes then the worksheet_change event
fires, followed by the workbook_sheetselectionchange code again.

if Chip is around (or one of the other people more knowledgable in
programming than me) hopefully they'll pop in and let you know if there's
more to it than what i've outlined above.

Cheers
JulieD
 
Back
Top