Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help...code needed to ignore null cells
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 ________________________________________ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help...code needed to ignore null cells
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help...code needed to ignore null cells
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help...code needed to ignore null cells
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help...code needed to ignore null cells
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help...code needed to ignore null cells
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help...code needed to ignore null cells
Nick - Thanks so much again for your help. Everything works great now!
Have a great week, puakeni |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
Generating blank or null cells that the Histogram Data Analysis tool will ignore | Excel Worksheet Functions | |||
Count cells with numbers and ignore cells with errors | Excel Discussion (Misc queries) | |||
NULL Cells | Excel Worksheet Functions | |||
Pivot chart - Ignore zero values - help needed | Excel Discussion (Misc queries) |