ComboBox within Frame

[barbados]

Hi all,

Let me start by telling what I did. On an empty worksheet, I inserted a frame control (Developer -> Insert - More controls -> Microsoft Forms 2.0 Frame). Within this frame, I added a combo box and a label (right-click the frame -> Frame Object -> Edit).

Now what I want to achieve is, that based on the value in the combo box, change the name of the label. I want this done by VBA. How to do this?

Some remarks:
The ComboBox1_Change() functionality doesn't work, since the combo box is sitting within the frame.
The frame is there mainly for layout purposes. I like the box and the header around my controls. I also like the fact I can format the frame.


Help is appreciated, as well as working code on how to achieve this.

Thanks.

[HansV]

Welcome to Eileen's Lounge!

Let's say you have a frame Frame1 with controls ComboBox1, Label1.

Place the following code in the worksheet module:

Code: Select all

Public WithEvents cbx As MSForms.ComboBox

Private Sub cbx_Change()
    ' As an example, set the caption of the label to the value of the combo box
    Sheet1.Frame1.Controls("Label1").Caption = cbx.Value
End Sub

And place the following code in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_Open()
    Set Sheet1.cbx = Sheet1.Frame1.Controls("ComboBox1")
End Sub

The On Change event procedure of the combo box won't run until cbx has been set. This will happen when the workbook is opened, or when you run Workbook_Open manually.

[HansV]

Oh, and don't forget to place the workbook in a trusted location. If you're using Excel 2010, you can make the workbook a trusted document instead.
Otherwise, ActiveX controls will be disabled.

And keep in mind that using ActiveX controls this way is rather fragile. Excel crashed several times when I was trying out the above code.

[barbados]

Thanks for your help. Your solution is working fine. I googled on WithEvents and noticed many people put this in a class rather than in a worksheet module. Do you know whether one method is preferred over the other or doesn't it matter at all?

Thanks.

[HansV]

A worksheet module is a special kind of class module - that's why you're able to declare cbx as WithEvents. You wouldn't be able to do that in a standard module.
You could use a separate class module, but since the code is tightly bound to the worksheet, it is convenient to place it in the worksheet module.