Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range into ListBox excluding blanks
I successfully populate a listbox with a list of values based on the value
selected in a combox. The value in the combobox is a named range. However, that list may have blanks in it that I don't want. How can I display the listing in the listbox excluding any blanks so it is one solid list? THe total items is less than 200, and often less than 20. Private Sub ComboBox1_Change() Dim rng As Variant 'rng = UserForm1.ComboBox1.Selected(x) With ActiveSheet rng = UserFormListToCell.ComboBox1.Value UserFormListToCell.ListBox1.RowSource = rng End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range into ListBox excluding blanks
Loop through the cells in that range and check to see if they're empty. Use
..additem to add them to the listbox. Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .Clear .AddItem "rngname1" .AddItem "rngname2" End With End Sub KIM W wrote: I successfully populate a listbox with a list of values based on the value selected in a combox. The value in the combobox is a named range. However, that list may have blanks in it that I don't want. How can I display the listing in the listbox excluding any blanks so it is one solid list? THe total items is less than 200, and often less than 20. Private Sub ComboBox1_Change() Dim rng As Variant 'rng = UserForm1.ComboBox1.Selected(x) With ActiveSheet rng = UserFormListToCell.ComboBox1.Value UserFormListToCell.ListBox1.RowSource = rng End With End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range into ListBox excluding blanks
ps. The me keyword is the object that owns the code. In the sample code I
posted, it was the userform itself. By using that keyword, I don't have to worry about the name of the userform. (I did assume that it was a single userform--not that you were controlling two???) KIM W wrote: I successfully populate a listbox with a list of values based on the value selected in a combox. The value in the combobox is a named range. However, that list may have blanks in it that I don't want. How can I display the listing in the listbox excluding any blanks so it is one solid list? THe total items is less than 200, and often less than 20. Private Sub ComboBox1_Change() Dim rng As Variant 'rng = UserForm1.ComboBox1.Selected(x) With ActiveSheet rng = UserFormListToCell.ComboBox1.Value UserFormListToCell.ListBox1.RowSource = rng End With End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range into ListBox excluding blanks
Dear Dave,
Thanks so much for the code solution for creating range excluding blanks. It works when the range is on the same worksheet that I launch the Userform from. Could you assist further by showing me how the rage can be from another worksheet? A related question, please: How can I make this new blank-less range also the range I use in a Validation List? Here's what I ended up with: Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() Dim nName As Name Dim myNames() As String Dim iCtr As Long iCtr = 0 For Each nName In ThisWorkbook.Names If LCase(nName.Name) Like LCase("List*") Then iCtr = iCtr + 1 ReDim Preserve myNames(1 To iCtr) myNames(iCtr) = nName.Name End If Next nName If iCtr = 0 Then UserFormListToCell.ComboBox1.Enabled = False Else With UserFormListToCell.ComboBox1 ..Clear ..List = myNames ..Enabled = True "Dave Peterson" wrote: Loop through the cells in that range and check to see if they're empty. Use ..additem to add them to the listbox. Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .Clear .AddItem "rngname1" .AddItem "rngname2" End With End Sub KIM W wrote: I successfully populate a listbox with a list of values based on the value selected in a combox. The value in the combobox is a named range. However, that list may have blanks in it that I don't want. How can I display the listing in the listbox excluding any blanks so it is one solid list? THe total items is less than 200, and often less than 20. Private Sub ComboBox1_Change() Dim rng As Variant 'rng = UserForm1.ComboBox1.Selected(x) With ActiveSheet rng = UserFormListToCell.ComboBox1.Value UserFormListToCell.ListBox1.RowSource = rng End With End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range into ListBox excluding blanks
If you know the name of the sheet, you can change this line:
With ActiveSheet to With Worksheets("sheetnamehere") ====== If I wanted to use a range that contained blanks as my list in data|validation, I'd copy it to a new range. Sort it and use the non-empty range. Or maybe just sort the original range (all the columns!) and use a dynamic range name that grew or contracted with the amount of data. Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic (Maybe that's what you could use for populating the listbox, too???) KIM W wrote: Dear Dave, Thanks so much for the code solution for creating range excluding blanks. It works when the range is on the same worksheet that I launch the Userform from. Could you assist further by showing me how the rage can be from another worksheet? A related question, please: How can I make this new blank-less range also the range I use in a Validation List? Here's what I ended up with: Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() Dim nName As Name Dim myNames() As String Dim iCtr As Long iCtr = 0 For Each nName In ThisWorkbook.Names If LCase(nName.Name) Like LCase("List*") Then iCtr = iCtr + 1 ReDim Preserve myNames(1 To iCtr) myNames(iCtr) = nName.Name End If Next nName If iCtr = 0 Then UserFormListToCell.ComboBox1.Enabled = False Else With UserFormListToCell.ComboBox1 .Clear .List = myNames .Enabled = True "Dave Peterson" wrote: Loop through the cells in that range and check to see if they're empty. Use ..additem to add them to the listbox. Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .Clear .AddItem "rngname1" .AddItem "rngname2" End With End Sub KIM W wrote: I successfully populate a listbox with a list of values based on the value selected in a combox. The value in the combobox is a named range. However, that list may have blanks in it that I don't want. How can I display the listing in the listbox excluding any blanks so it is one solid list? THe total items is less than 200, and often less than 20. Private Sub ComboBox1_Change() Dim rng As Variant 'rng = UserForm1.ComboBox1.Selected(x) With ActiveSheet rng = UserFormListToCell.ComboBox1.Value UserFormListToCell.ListBox1.RowSource = rng End With End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range into ListBox excluding blanks
Well, I don't know the name of the worksheet that contains the range. I have
about 100 worksheets, each one has in it a range in which the user enters a list. Later we want those values in listboxes accessible on various other worksheets. All was working well, but for the blanks. (I was using ..rowsource.) If this is getting too specific, jut let me know. You've been very helpful already. And, yes, this workbook solution already has Debra Dalgleish dynamic list box in it-- very valuable! I find her web sit offers numerous good solutions that are compatible with my workbook development style. "Dave Peterson" wrote: If you know the name of the sheet, you can change this line: With ActiveSheet to With Worksheets("sheetnamehere") ====== If I wanted to use a range that contained blanks as my list in data|validation, I'd copy it to a new range. Sort it and use the non-empty range. Or maybe just sort the original range (all the columns!) and use a dynamic range name that grew or contracted with the amount of data. Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic (Maybe that's what you could use for populating the listbox, too???) KIM W wrote: Dear Dave, Thanks so much for the code solution for creating range excluding blanks. It works when the range is on the same worksheet that I launch the Userform from. Could you assist further by showing me how the rage can be from another worksheet? A related question, please: How can I make this new blank-less range also the range I use in a Validation List? Here's what I ended up with: Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() Dim nName As Name Dim myNames() As String Dim iCtr As Long iCtr = 0 For Each nName In ThisWorkbook.Names If LCase(nName.Name) Like LCase("List*") Then iCtr = iCtr + 1 ReDim Preserve myNames(1 To iCtr) myNames(iCtr) = nName.Name End If Next nName If iCtr = 0 Then UserFormListToCell.ComboBox1.Enabled = False Else With UserFormListToCell.ComboBox1 .Clear .List = myNames .Enabled = True "Dave Peterson" wrote: Loop through the cells in that range and check to see if they're empty. Use ..additem to add them to the listbox. Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .Clear .AddItem "rngname1" .AddItem "rngname2" End With End Sub KIM W wrote: I successfully populate a listbox with a list of values based on the value selected in a combox. The value in the combobox is a named range. However, that list may have blanks in it that I don't want. How can I display the listing in the listbox excluding any blanks so it is one solid list? THe total items is less than 200, and often less than 20. Private Sub ComboBox1_Change() Dim rng As Variant 'rng = UserForm1.ComboBox1.Selected(x) With ActiveSheet rng = UserFormListToCell.ComboBox1.Value UserFormListToCell.ListBox1.RowSource = rng End With End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range into ListBox excluding blanks
Then you could go through the Names collection:
With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With Becomes Set myRng = activeworkbook.names(Me.ComboBox1.Value).referstor ange KIM W wrote: Well, I don't know the name of the worksheet that contains the range. I have about 100 worksheets, each one has in it a range in which the user enters a list. Later we want those values in listboxes accessible on various other worksheets. All was working well, but for the blanks. (I was using .rowsource.) If this is getting too specific, jut let me know. You've been very helpful already. And, yes, this workbook solution already has Debra Dalgleish dynamic list box in it-- very valuable! I find her web sit offers numerous good solutions that are compatible with my workbook development style. "Dave Peterson" wrote: If you know the name of the sheet, you can change this line: With ActiveSheet to With Worksheets("sheetnamehere") ====== If I wanted to use a range that contained blanks as my list in data|validation, I'd copy it to a new range. Sort it and use the non-empty range. Or maybe just sort the original range (all the columns!) and use a dynamic range name that grew or contracted with the amount of data. Debra Dalgleish explains dynamic range names he http://contextures.com/xlNames01.html#Dynamic (Maybe that's what you could use for populating the listbox, too???) KIM W wrote: Dear Dave, Thanks so much for the code solution for creating range excluding blanks. It works when the range is on the same worksheet that I launch the Userform from. Could you assist further by showing me how the rage can be from another worksheet? A related question, please: How can I make this new blank-less range also the range I use in a Validation List? Here's what I ended up with: Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() Dim nName As Name Dim myNames() As String Dim iCtr As Long iCtr = 0 For Each nName In ThisWorkbook.Names If LCase(nName.Name) Like LCase("List*") Then iCtr = iCtr + 1 ReDim Preserve myNames(1 To iCtr) myNames(iCtr) = nName.Name End If Next nName If iCtr = 0 Then UserFormListToCell.ComboBox1.Enabled = False Else With UserFormListToCell.ComboBox1 .Clear .List = myNames .Enabled = True "Dave Peterson" wrote: Loop through the cells in that range and check to see if they're empty. Use ..additem to add them to the listbox. Option Explicit Private Sub ComboBox1_Change() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next With ActiveSheet Set myRng = .Range(Me.ComboBox1.Value) End With On Error GoTo 0 Me.Label1.Caption = "" If myRng Is Nothing Then Beep Me.Label1.Caption = "Select a name from the combobox" Else For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then 'skip it Else Me.ListBox1.AddItem myCell.Value End If Next myCell End If End Sub Private Sub UserForm_Initialize() With Me.ComboBox1 .Clear .AddItem "rngname1" .AddItem "rngname2" End With End Sub KIM W wrote: I successfully populate a listbox with a list of values based on the value selected in a combox. The value in the combobox is a named range. However, that list may have blanks in it that I don't want. How can I display the listing in the listbox excluding any blanks so it is one solid list? THe total items is less than 200, and often less than 20. Private Sub ComboBox1_Change() Dim rng As Variant 'rng = UserForm1.ComboBox1.Selected(x) With ActiveSheet rng = UserFormListToCell.ComboBox1.Value UserFormListToCell.ListBox1.RowSource = rng End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenating cells but excluding blanks | Excel Discussion (Misc queries) | |||
Dsum excluding blanks | Excel Worksheet Functions | |||
average in non-continuous set, excluding blanks | Excel Worksheet Functions | |||
Count IF excluding blanks or zeroes | Excel Worksheet Functions | |||
How to get lowest value excluding blanks | Excel Worksheet Functions |