Help...code needed to ignore null cells

[puakeni1]

Hi all - I'm new to VBA but recently found a very useful code on the
web. I am using it to display values from specific cells in a data
validation box...neat little feature. If you click on a cell in column
B, a data validation box displays with info from other cells (that I
specified in the code), so you can see info from those cells upfront
without scrolling to the left. The only thing is that when a cell is
null (for instance cell 12 and 13), an empty line will display in it's
place in the data validation box (see code below). What code I can add
so that it ignores the null cells and doesn't display a blank lines?
I'm hoping there's something I can use. If anyone can help me, I'd
greatly appreciate it!! Thanks a bunch!

Here's the code:
____________________________________
Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Cells(2, 18) = False Then Exit Sub
With Target
If .Column = 2 And _
.Row > 2 And _
.Row < ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count Then
.EntireColumn.Validation.Delete
With .Validation
.Add Type:=xlValidateInputOnly
.InputTitle = "" ' Optional
.InputMessage = Cells(Target.Row, 10) & Chr(10) & _
Cells(Target.Row, 11) & Chr(10) & _
Cells(Target.Row, 12) & Chr(10) & _
Cells(Target.Row, 13) & Chr(10) & _
Cells(Target.Row, 14) & Chr(10) & _
Cells(Target.Row, 15) & Chr(10) & _
Cells(Target.Row, 16)

End With
End If
End With
End Sub
________________________________________

 

[hewett_nick]

Hi puakeni,

This should do it for you...

Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
Dim myList As String

On Error Resume Next
If Cells(2, 18) = False Then Exit Sub
With Target
If .Column = 2 And _
.Row > 2 And _
.Row < ActiveSheet.UsedRange.Row + _
ActiveSheet.UsedRange.Rows.Count Then
.EntireColumn.Validation.Delete

For i = 10 To 16
If Not Cells(Target.Row, i) = "" Then
myList = myList & Cells(Target.Row, i) & Chr(10)
End If
Next i

With .Validation
.Add Type:=xlValidateInputOnly
.InputTitle = "" ' Optional
.InputMessage = myList
End With
End If
End With
End Sub

Kind regards,
Nick.

 

[puakeni1]

Hi Nick,

Thank you so much for your help! This is exactly what I needed...works
like a charm! You're the best!

Many Thanks!
puakeni

 

[NickH]

There is a hardcore of highly skilled MVPs, that make this the
excellent resource it is, who may take issue with that last comment.
But thanks all the same. ?;^)

NickH

 

[puakeni1]

Hi Nick,
LOL...people like me are just so grateful to get help from folks like
yourself!

I do have another question though. I just found that when I protect my
worksheet, the code doesn't work? Why is this? Is there a way around
this? I've heard that some codes don't work when the worksheet is
protected if the code includes formatting stuff, but mine doesn't (I
don't think). Hmmm, I'm confused.

Hope you or anyone else has some ideas...

Thanks again!
puakeni

 

[NickH]

Sorry puakeni, I've not looked at the group for a few days.

Your code is making a change to the sheet, hence, it will fail if the
sheet is protected. The way round it is to put something like...

Me.Unprotect "Your_Password"

....at the top of your routine, and...

Me.Protect "Your_Password"

....at the bottom of your routine.

HTH
NickH

 

[puakeni1]

Nick - Thanks so much again for your help. Everything works great now!

Have a great week,
puakeni