Frame control hides its inner controls in a worksheet of an Excel file

[polocar]

Hello,
I’m preparing an Excel 2003 file with some controls placed in the first worksheet.
To create the controls I use the “Control Tools” toolbar, that you can obtain clicking on “View” -> “Toolbars” -> “Control Tools” (it’s not the “Forms” toolbar, because I want to use the controls and their methods and properties in the Visual Basic Editor. With the “Forms” toolbar controls, I have tested that it isn’t possible).

To be a little tidy, I wanted to group the controls into frames, so I clicked on “Other controls” button of the “Control Tools” toolbar and selected “Microsoft Forms 2.0 Frame”. Then I put the frame in the middle of the worksheet and added some CommandButton, TextBox and Label controls in the inner of the frame.
My problem is that, when I click on “Exit from project mode” of the “Control Tools” toolbar, the controls into the frame disappear, as the frame rose to the first layer of the z-order.
This is strange, because I created the controls AFTER the frame! I also tried to set manually the z-order of the frame and the controls (the controls on the first layer, the frame on the second one), but it has no effect: during the project mode the controls are correctly over the frame, but, as soon as I exit from the project mode to test them, the frame returns on the first layer, and the controls disappear.

Have you any idea about this strange behaviour and how to solve it?

Thank you very much

 

[combo]

Hi,
you can work with frame as MSForms object in edit mode (when you select it, it's an excel shape, you can't add controls). Right-click it and go Object Frame > Edit. Now it is possible to add controls.

combo

 

[polocar]

Hi combo,
I did what you suggested and I was able to create a frame with a CommandButton control that remains visible over the frame in "Execution mode" too, very good.

When I opened the Visual Basic Editor I was hoping to find CommandButton1 in the Worksheet1 code, so to write CommandButton1_Click event handling procedure,
but unfortunately I didn't find it in the combobox of the Worksheet1 controls...
I also tried to write CommandButton1. (plus some property) and Frame1.CommandButton1. (plus some property), but it isn't recognized.

So, how to write the code for handling the events of the controls added to the frame in the way you suggested?

Thank you

 

[combo]

Hi,
when you edit frame, you can right-click any control and set its propertes.
To fire events, probably the only way is to use WithEvents keyword, in separated class module or sheet module. I can't see the control in object browser / sheet module drop-down / property window.
I used the second way, left CommandButton1 name unchanged. You can choose the best way to assign control to cmd1, below you have to click frame first.

Private WithEvents cmd1 As CommandButton

Private Sub cmd1_Click()
MsgBox "withevents event fired"
End Sub

Private Sub Frame1_Click()
Set cmd1 = Me.Frame1.Object.commandbutton1
End Sub

combo

 

[polocar]

Ok combo, it functions. Thank you very much

 

[combo]

I'm glad it works, however frame is not the best solution for worksheet. You can get the same visual effect using combination of labels, with backcolor set to 'Button face' and, for some labels, SpecialEffect set to fmSpecialEffectEtched.
You will not be limited to events available by WithEvents. Worksheet module can display and directly handle controls' events. It is also possible to group optionbuttons without frame, using GroupName property.

combo

 

[polocar]

In effect WithEvents is not so intuitive (for me)...
Your second solution seems to me easier and more clear (and the visual effect is the same, using a rectangle plus a text box taken from the Excel drawing bar)