Referencing a form object in VBA code 1

[robinsdale]

Hi, I have a Excel97 VBA form with a number of comboboxes that I want to populate with values in a sheet. My goal is to have one procedure to handle populating all of the comboboxes, which I would call with parameters such as:
1. name of the combobox to be populated
2. name of the sheet holding the values

Currently, the following code populates a specific combobox:
=============
CboItem.Clear
nSuppNum = .Range("A3&quot.Value
With Sheets("Supplies&quot
.Select
If .Range("B5&quot.Value = "" Then
CboItem.AddItem ("No Supplies Entered&quot
CboItem.Text = "No Supplies Entered"
Exit Function
End If

CboItem.Clear
nSuppNum = .Range("A3&quot.Value
For i = 1 To nSuppNum
cRecRange = "B" & LTrim(Str(4 + i))
.Range(cRecRange).Select
If UCase(.Range(cRecRange).Value) <> &quot;&quot; Then
cSuppName = .Range(cRecRange).Value
CboItem.AddItem (cSuppName)
End If
Next
End With
==========
To create a common function for populating comboboxes, I would need to replace the word &quot;CboItem&quot; with a combobox name passed as a parameter. My Question: is this possible to do in VBA? The FAQ doesn't seem to have any solutions. In Visual FoxPro, I can do this using macro-expansion (the &quot;&&quot and a double period after the object name variable:
WITH THISFORM
.&cmCbo..DisplayValue = &quot;Foobar&quot;
ENDWITH

I apologize if I've asked a question previously dealt with...
Regards,
Robyn

 

[SkipVought]

Hi,
You might be able to accomplish this objective another way.
1. Assign a range name to each list ie ddList1, ddList2 etc.
2. In your UserForm, for each ComboBox, assign the RowSource property the appropriate range name.
3. If you drop down lists are dynamic, you can format each list with the name in the first row, and automatically rename each range using similar code:

    Application.DisplayAlerts = False
    Range("dd1List").CurrentRegion.CreateNames _
        Top:=True, _
        Left:=False, _
        Bottom:=False, _
        Right:=False

Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

...and if you want to dymanically add items, here's a way to do that too:

Sub LoadCombos()
    Dim sList As String, rngName As Range
    For Each Control In UserForm1.Controls
        With Control
            Select Case .Name
                Case "ComboBox1"
                    sList = "dd1List"
                Case "ComboBox2"
                    sList = "dd2List"
            End Select
            For Each rngName In Range(sList)
                .AddItem (rngName.Value)
            Next
        End With
    Next
    UserForm1.Show
End Sub

Skip,
metzgsk@voughtaircraft.com

 

[robinsdale]

Thanks, Skip. Your solution will work.

Do I take it, then, that as far as you know, there is no way to reference an object using a variable - just for my edification, since I'm so accustomed to writing 'black-box' code (classes??) that can handle tasks such as these using parameters...

Regards,
Robyn

 

[robinsdale]

Hi Skip,
Thanks again for taking the time to ponder my problem. As it turns out, I didn't ask the question properly - you found a solution to what I asked, but my question should have been worded:
What is the right way to pass the name of an object to a subprocedure?

The answer was the following:
Call the procedure as a procedure, not a function (which I did, and failed to mention in the first missive) as follows.
In the calling procedure:

Call PopulateCombos CboItem
'where CboItem is the combo to be populated

Then, in the sub, I could reference that combo:

Public Sub PopulateCombos(cCombo as Control)

cCombo.Clear
' more muchly modified code here

End Sub

The key was the &quot;name as Control&quot; as argument in the called procedure. I was getting all these error messages about &quot;object required&quot;... anyway, all this points to a need for a remedial course in parameter passing in VBA. Is there a KB article or other white paper I could study that would explain this in detail? The Excel Programming for Dummies book by J Walkenbach gets tantalisingly close, but is geared more for neophytes (more so than even I) so it wasn't much help.

Thanks again for your help on this, Skip!!
Kindest regards,
Robin

 

[robinsdale]

Hi Skip,
You are probably already a bit tired of these missives, but I just wanted to let you know that once I'd clawed my way out of the tiger-trap of my own kludgy (ancient-style) solution question, I took the time to thoroughly analyse - and appreciate!!! - your perfectly elegant solution:


Sub LoadCombos()
Dim sList As String, rngName As Range
For Each Control In UserForm1.Controls
With Control
Select Case .Name
Case &quot;ComboBox1&quot;
sList = &quot;dd1List&quot;
Case &quot;ComboBox2&quot;
sList = &quot;dd2List&quot;
End Select
For Each rngName In Range(sList)
.AddItem (rngName.Value)
Next
End With
Next
UserForm1.Show
End Sub


which I am employing. Give me enough time, and I'm quick...

Thanks for your patience, and for that brilliant code!

Kindest regards,
Robin (much abashed and embarassed)