Adding values to a combo box

[jbear]

Good morning, everyone! I'm still learning the in's and out's of Excel
while I work on various projects. Right now, I'm trying to implement a
combo box onto a spreadsheet which will populate based on values
entered on another worksheet within the workbook. There a few factors
complicating my attempts though:

First, the combo box is on Sheet1 and the array is on Sheet2. I've
only had success in my efforts when I put the coding to populate the
combo box with the array information on the "ThisWorkbook" object in
VBA. Here is the code that works under this condition:

Sub Main()

Dim i As Integer
Dim NumCUSIPs As Integer
Dim CUSIPList() As String

' Calls the CUSIPManager function to populate
' the combo box
Call CUSIPManager(NumCUSIPs, CUSIPList())
For i = 1 To NumCUSIPs
Sheets("Sheet1").ComboBox1.AddItem CUSIPList(i)
Next i
Sheets("Sheet1").Select

End Sub

Sub CUSIPManager(CUSIPCount As Integer, CUSIPs() As String)

' Counts the number of CUSIPs related
' to the account and stores them to an array

Sheets("Sheet2").Select
Range("A2").Select
Do While ActiveCell.Value <> ""
CUSIPCount = CUSIPCount + 1
ReDim Preserve CUSIPs(CUSIPCount)
CUSIPs(CUSIPCount) = ActiveCell.Value
ActiveCell.Offset(1).Select
Loop

End Sub

My second problem is that the array of information that will be used to
populate the combo box is of variable size. People could add or remove
information from the array at anytime. Right now, with the coding on
the "ThisWorkbook" object, it works fine. If my code is fine where it
is, then that eliminates problems 1 and 2 and brings me to problem 3...

Problem 3 is that when I first open the workbook, only the first value
in the array is populated. Is there anyway to have the values in the
combo box updated upon opening the worksheet?

I appreciate any insight that anyone can provide. I've been reading
these forums for a few months now for pointers as I've been learning
VBA, but this is the first time that I can officially say "I'm
stumped!". Thanks!

 

[dd]

Try this example from Debra Dalgleish at Contextures.

http://www.contextures.com/ComboBoxAddress.zip

Good morning, everyone! I'm still learning the in's and out's of Excel
while I work on various projects. Right now, I'm trying to implement a
combo box onto a spreadsheet which will populate based on values
entered on another worksheet within the workbook. There a few factors
complicating my attempts though:

First, the combo box is on Sheet1 and the array is on Sheet2. I've
only had success in my efforts when I put the coding to populate the
combo box with the array information on the "ThisWorkbook" object in
VBA. Here is the code that works under this condition:

Sub Main()

Dim i As Integer
Dim NumCUSIPs As Integer
Dim CUSIPList() As String

' Calls the CUSIPManager function to populate
' the combo box
Call CUSIPManager(NumCUSIPs, CUSIPList())
For i = 1 To NumCUSIPs
Sheets("Sheet1").ComboBox1.AddItem CUSIPList(i)
Next i
Sheets("Sheet1").Select

End Sub

Sub CUSIPManager(CUSIPCount As Integer, CUSIPs() As String)

' Counts the number of CUSIPs related
' to the account and stores them to an array

Sheets("Sheet2").Select
Range("A2").Select
Do While ActiveCell.Value <> ""
CUSIPCount = CUSIPCount + 1
ReDim Preserve CUSIPs(CUSIPCount)
CUSIPs(CUSIPCount) = ActiveCell.Value
ActiveCell.Offset(1).Select
Loop

End Sub

My second problem is that the array of information that will be used to
populate the combo box is of variable size. People could add or remove
information from the array at anytime. Right now, with the coding on
the "ThisWorkbook" object, it works fine. If my code is fine where it
is, then that eliminates problems 1 and 2 and brings me to problem 3...

Problem 3 is that when I first open the workbook, only the first value
in the array is populated. Is there anyway to have the values in the
combo box updated upon opening the worksheet?

I appreciate any insight that anyone can provide. I've been reading
these forums for a few months now for pointers as I've been learning
VBA, but this is the first time that I can officially say "I'm
stumped!". Thanks!

 

[JB]

Thank you all for the responses! They helped me with what I was doing.