RE: Adding values to a combo box

First the reason you're having difficulty with the code being in the Sheet1
or Sheet2 module is that the the Parent object for the Range property
defaults to the *** module that you are in. It can be explicitly stated
like this:
Sub Test
'Assumes this is in Sheet1s code
Sheets("Sheet2").Select
Range("A1") = "Test"
'You might think you should be setting Sheet2.Range("A1")
'but you are actually setting Sheet1.Range("A1")
With Sheets("Sheet2")
.Range("A1") = "Test"
'Now we've given the Range("A1") the Parent object of "Sheet2"
'so it should be updating correctly
End With
End Sub

As for problem 3, in the ThisWorkbook code, check out the built in Events in
Excel, these are found in the 2 comboboxes at the top of the code window.
Choose "Workbook" in the left on and "Open" in the Right, a sub like this
should appear for you:
Private Sub Workbook_Open()
'Add your code to populate the combobox here
'You can probably just call your other code
Call Main
End Sub
Also check out the Events in the individual Sheets code. You have options
for SelectionChange, Change, and many others. Post back or email me @
chick65stang@xxxxxxxxxxxxxxx (remove nospam) if you need more help
--
Charles Chickering

"A good example is twice the value of good advice."


"jbear@xxxxxxxxxxxxxxxxxxx" wrote:

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 spread*** which will populate based on values
entered on another work*** 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 work***?

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!


.

Quantcast