Adding Items to ListBox-eliminating Blanks
 

Will post again as I do not see my post.

The Situation: I need to add items (mulitcolumn) to a listbox based o
the value of another combobox. Basically, the user will select th
state, and then based on the state--the listbox will populate.

The problem is 2 fold- first-I can get the items to add, but it als
adds blank lines. I do not want the blank lines-It basically adds
blank line if the value does not match or adds the data if it does.

The second part is that I have to use a constant ("66") rather than
variable ("PackCount") due to the way I structured the code. I'd lik
to use the variable of PACKCOUNT rather than 66 to determine the numbe
of loops for flexability.

Any help would be greatly appreciated.

My code is as follows:

Sub PopulateBox()


Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))
Dim i As Integer

ListBox1.Clear

Dim Data(1 To 66, 1 To 2)

On Error Resume Next
For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
End If
Next i

For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value

End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Su

--
Message posted from http://www.ExcelForum.com

Adding Items to ListBox-eliminating Blanks
 

Sub PopulateBox()

Dim data()

Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount =
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))
Dim i As Integer

ListBox1.Clear

reDim Data(1 To Packcount, 1 To 2)

On Error Resume Next
For i = 1 To Packcount
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value
End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Sub

--
Regards,
Tom Ogilvy


"jpendegraft " wrote in message
...
Will post again as I do not see my post.

The Situation: I need to add items (mulitcolumn) to a listbox based on
the value of another combobox. Basically, the user will select the
state, and then based on the state--the listbox will populate.

The problem is 2 fold- first-I can get the items to add, but it also
adds blank lines. I do not want the blank lines-It basically adds a
blank line if the value does not match or adds the data if it does.

The second part is that I have to use a constant ("66") rather than a
variable ("PackCount") due to the way I structured the code. I'd like
to use the variable of PACKCOUNT rather than 66 to determine the number
of loops for flexability.

Any help would be greatly appreciated.

My code is as follows:

Sub PopulateBox()


Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount =
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))
Dim i As Integer

ListBox1.Clear

Dim Data(1 To 66, 1 To 2)

On Error Resume Next
For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
End If
Next i

For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value

End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Sub


---
Message posted from http://www.ExcelForum.com/

Adding Items to ListBox-eliminating Blanks
 

I have a sample workbook that handles any number of dependent comboboxes.
Mail me if you want a copy.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jpendegraft " wrote in message
...
Will post again as I do not see my post.

The Situation: I need to add items (mulitcolumn) to a listbox based on
the value of another combobox. Basically, the user will select the
state, and then based on the state--the listbox will populate.

The problem is 2 fold- first-I can get the items to add, but it also
adds blank lines. I do not want the blank lines-It basically adds a
blank line if the value does not match or adds the data if it does.

The second part is that I have to use a constant ("66") rather than a
variable ("PackCount") due to the way I structured the code. I'd like
to use the variable of PACKCOUNT rather than 66 to determine the number
of loops for flexability.

Any help would be greatly appreciated.

My code is as follows:

Sub PopulateBox()


Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")
Packcount =
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))
Dim i As Integer

ListBox1.Clear

Dim Data(1 To 66, 1 To 2)

On Error Resume Next
For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 1) = PackagesAvailable.Cells(i, 2).Value
End If
Next i

For i = 1 To 66
If Sheet10.Cells(i, 1) = cbState.Value Then
Data(i, 2) = PackagesAvailable.Cells(i, 3).Value

End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Sub


---
Message posted from http://www.ExcelForum.com/

Adding Items to ListBox-eliminating Blanks
 

Tom's answer is close but the
"Packcount =" will generate a Syntax error and not the answer jp i
looking for

--
Message posted from http://www.ExcelForum.com

Adding Items to ListBox-eliminating Blanks
 

Tom's solution (as usual) worked to get the PACKCOUNT set as a variabl
(by REDIMMING IT), but did not address the adding the blanks... An
help on the blanks?

Bob certainly would love a copy of th


--
Message posted from http://www.ExcelForum.com

Adding Items to ListBox-eliminating Blanks
 

You mean because
Packcount =
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))

should all be on one line.

or

Packcount = _
Application.WorksheetFunction.CountA(PackagesAvail able.Range("A:A"))


Since I didn't change that line of code, and it appeared in two lines
(because of word wrap) in the OP's posting, I didn't go through and reformat
the posting - I assumed (maybe too much) that the OP could figure it out.

--
Regards,
Tom Ogilvy

"eklarsen " wrote in message
...
Tom's answer is close but the
"Packcount =" will generate a Syntax error and not the answer jp is
looking for.


---
Message posted from http://www.ExcelForum.com/

Adding Items to ListBox-eliminating Blanks
 

It's in the post.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jpendegraft " wrote in message
...
Tom's solution (as usual) worked to get the PACKCOUNT set as a variable
(by REDIMMING IT), but did not address the adding the blanks... Any
help on the blanks?

Bob certainly would love a copy of the



---
Message posted from http://www.ExcelForum.com/

Adding Items to ListBox-eliminating Blanks
 

I assumed that not using a fixed size array would eliminate the blanks. If
you still have blanks embedded in the array the obvious answer is not to add
them to the array, but that can be complex.

Sub PopulateBox()

Dim data()
Dim i As Long
Dim j as Long
Dim rng as Range

Set PackagesAvailable = ThisWorkbook.Sheets("BrandCount")

With PackagesAvailable
Set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With

' figure out how many values you will have
packcount = 0
for i = 1 to rng.rows.count
if sheet10.Cells(i,1).Value = cbState.Value and _
rng(i) < "" then
packcount = packcount + 1
end if
Next
ListBox1.Clear

reDim Data(1 To Packcount, 1 To 2)


j = 0
For i = 1 To rng.rows.count
If Sheet10.Cells(i, 1) = cbState.Value Then
if PackagesAvailable.Cells(i,2) < "" then
j = j + 1
Data(j, 1) = PackagesAvailable.Cells(i, 2).Value
Data(j, 2) = PackagesAvailable.Cells(i, 3).Value
End if
End If
Next i

ListBox1.ColumnCount = 2

ListBox1.list = Data

End Sub

code is untested, so it might have problems, but suggests a general
approach.

--
Regards,
Tom Ogilvy



"jpendegraft " wrote in message
...
Tom's solution (as usual) worked to get the PACKCOUNT set as a variable
(by REDIMMING IT), but did not address the adding the blanks... Any
help on the blanks?

Bob certainly would love a copy of the



---
Message posted from http://www.ExcelForum.com/

Adding Items to ListBox-eliminating Blanks
 

Thanks Tom. Big help once again

--
Message posted from http://www.ExcelForum.com

Adding Items to ListBox-eliminating Blanks
 

apparently not.

--
Regards,
Tom Ogilvy

"jpendegraft " wrote in message
...
Thanks Tom. Big help once again.


---
Message posted from http://www.ExcelForum.com/

Adding Items to ListBox-eliminating Blanks
 

Adding Items to ListBox-eliminating Blanks
I have a sample workbook that handles any number of dependen
comboboxes.
Mail me if you want a copy.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi,
I am new at this or this forum for that matter...I would like to get
copy of this workbook
Regards
Raou

--
Message posted from http://www.ExcelForum.com