VBA Controls

I have several Combo boxes on a page in a spreadsheet and I want to place a button on the page to set all the Combos to a certain selection.

As I am fairly new to VBA, I cannot find how to address the controls from a macro run from a button.

Can someone give me a few hints on this?
LVL 5
gbentleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tureCommented:
gbentley,

Something like this, perhaps?

Private Sub CommandButton1_Click()
  Dim x As OLEObject
  For Each x In Me.OLEObjects
    If x.ProgId = "Forms.ComboBox.1" Then
      x.Object.Text = "Test"
    End If
  Next x
End Sub

Ture Magnusson
Karlstad, Sweden
0
calacucciaCommented:
Hi gbentley,

here's a small macro that should show you the prinipal important points and methods to run the controls of Combos  by a buton click. In the example, there are 3 combo boxes and two of them are filled from an array, described in the same macro, the third one is filled with values from a certain range on the spreadsheet.

The macro (named Button5_Click is assigned to the click event from a button on the sheet (as you asked)

To assign a macro to a button, right-click on the button, select assign macro, type the name you want to give in the name box (or accept the name proposed), and click the 'New' button, now in the appearing Sub YourName....End Sub, paste following code and test it.
#############################################################################

Sub Button5_Click()
'The first combo box (name Drop Down 1) will be filled with all values/text
'from the array Combo1
Combo1 = Array("Monday", "Tuesday", "Wednesday")
For i = 0 To UBound(Combo1)
Sheets("Sheet1").Shapes("Drop Down 1").Select
     Selection.AddItem Combo1(i)
Next i
'The second combo box (name Drop Down 2) will be filled with all values/text
'from the array Combo2
Combo2 = Array("Jan", "Jimmy", "Margaret", "Bill", "Valentyn", "Fidel")
For i = 0 To UBound(Combo2)
Sheets("Sheet1").Shapes("Drop Down 2").Select
     Selection.AddItem Combo2(i)
Next i
'The thirdcombo box (name Drop Down 3) will be filled with all values put in the range
'$F$8:$F$16 on "Sheet1", will not be linked to a cell, will contain 8 drop down lines
' and the 3D-shading will not be displayed
Sheets("Sheet1").Shapes("Drop Down 3").Select
     With Selection
        .ListFillRange = "$F$8:$F$16"
        .LinkedCell = ""
        .DropDownLines = 8
        .Display3DShading = False
    End With
End Sub


Good Luck,

Calacuccia
0
calacucciaCommented:
Forgot to mention the remove items part, important to reset the control box before filling it:

(only done for first of three in example macro from above):

'The first combo box (name Drop Down 1) is cleared and will be filled with all values/text
'from the array Combo1
Combo1 = Array("Monday", "Tuesday", "Wednesday")
Sheets("Sheet1").Shapes("Drop Down 1").Select
     Selection.RemoveAllItems
For i = 0 To UBound(Combo1)
     Selection.AddItem Combo1(i)
Next i
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
antratCommented:
Hi gbentley

 I'll assume you are using the "control toolbox" for your controls if you aren't and your using "Forms" I would suggest using the "control toolbox" (more flexible).
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
To place a control on the worksheet:
click the control you want(this will put the Toolbox in Edit Mode) and then simply click on your worksheet where you want the control. Do the same for all controls you want.
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
To see the properties of the controls:
Click any control you have on the worksheet and then click "Properties" on the "Control Toolbox" (top right, small hand pointing to a sheet.)this will show the Property window relating to the control that is selected.
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
To change the properties of the controls:
The first tab is all properties in alphabetical order, the second tab is all properties catergorised, select tab 2. Most of these will suit your needs, if they don't the best way is to change them and see what happens, remembering to put it back as it was if not sure.
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
To add code to the controls:
 For this example I will use a Command button. Double click the Button, this will open the Visual Basic Editor with this already typed in:

Private Sub CommandButton1_Click()
<It is in here your code will go>
End Sub
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
To fill a combobox with a particular list place this example code in, just be sure the numbers after the Control correspond. ie: CommandButton1 is the same as yours, if not change to CommandButton2 or whatever.

Private Sub CommandButton1_Click()
ComboBox1.ListFillRange = "A1:A3"
End Sub

or


Private Sub CommandButton1_Click()
ComboBox1.ListFillRange = "MyNamedRange"
End Sub

if your range is named
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
to learn more while in the VBE type

Private Sub CommandButton1_Click()
CommandButton1.
End Sub

The DOT (.) will bring up all the "Members" for this control, select anyone of them and then highlight it and hit F1 this will go straight to the help for that member.

The drop arrow, top right, will contain all the "Events" for the particular control, such as Click,DoubleClick,Error and so on
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


Once you are ready to test it close the VBE and then deselect "Edit Mode" on the "Control Toolbox" (top left, ruler and triangle)and click your button.


Hope this will at least help you on your way.

Good luck

antrat

0
gbentleyAuthor Commented:
Thanks for that. I now have a better idea of what is going on. I didn't give you an Excellent as the one thing you didn't tell me was how to set the combo to a certain item in the list programmatically. I have now found the property I need. (ListIndex)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.