No Frames in Control Toolbox?

[Don Wiss]

On my sheets I generally prefer to use objects from the Forms toolbar. But
a bunch of them, for some reason, do not allow resizing. This means they
can't be hidden when the space behind them is hidden. So I am then forced
to use controls from the Control Toolbox.

When I have Option Buttons (radio buttons) I often like to put a Group Box
(Frame) around them. With multiple sets of Option Buttons from the Forms
toolbar one needs this to group them (the Control Option Buttons have a
group name property for this). But it still doesn't make sense that I can't
find a frame in the Control Toolbox.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Leith Ross]

Hello Don,

The Frame control isn't shown but is available. Go to the bottom of the
Toolbar where the wrench and hammer form an X. Click on the icon and it
will display a list of additional control. Scroll down to *Microsoft
Forms 2.0 Frame*. The mouse pointer will change to a cross. Move the
mouse to where you want the Frame and left click and hold. This will
draw the control on the worksheet. You can move it and resize it later
if want.

Sincerely,
Leith Ross

 

[Dennis I]

Question for Leith Ross. I too was looking for help on finding a frames
control and was pleased to read your reply. Now I have put in a frame as you
have suggested and added some controls (option buttons). But I cannot find a
way to use the events from those buttons. How do I capture an event related,
say, to OptionButton1 in Frame1?

 

[Don Wiss]

Question for Leith Ross. I too was looking for help on finding a frames
control and was pleased to read your reply. Now I have put in a frame as you
have suggested and added some controls (option buttons). But I cannot find a
way to use the events from those buttons. How do I capture an event related,
say, to OptionButton1 in Frame1?

All Option Buttons have a linked cell. All buttons within the same group
share the cell. It will have a value of 1,2,etc. The default is all buttons
on a sheet are in the same group. Frames will allow more than one group on
the same sheet. The linked cell can be found on the properties sheet. You
can also have the clicking fire a macro. For a Forms button you give it the
name of the macro to run. So more than one button can call the same macro.
For a Controls button the macro will be unique for each button and resides
behind the sheet. To start the macro click view code.

Not sure if this answers your question, as I'm not sure of the question.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Dennis I]

Thank you for the information. I need to use the buttons' value property in
code I am writing for the a worksheet. When I put option buttons (say
OptionButton1 and OptionButton2) directly on a worksheet (not in a frame), I
use "OptionButton1.value" and "OptionButton2.value" to see which is selected
and to direct further actions. However, when I put the buttons into a frame
(named Frame1), than the code does not seem to recognize OptionButton1.value
and OptionButton2.value. I've tried accessing the value properties by
"frame1.OptionButton1.value", but that does not work. In fact, I have
similar problem for all the controls in put in the frame. I put a command
button in the frame ("CommandButton1"), but when I write code for the
worksheet it does not recognize that this object exists.

 

[Leith Ross]

Hello Dennis,

Here is a macro that illustrates how to use the Frame from the Contro
Toolbox. Place a frame on a worksheet. For this example it should b
Frame1 ( the default name). Run the macro and it will automaticall
size its self and place 2 option buttons on the Frame. Whe
OptionButton1 is clicked, the result will appear in cell J3 and whe
OptionButton2 is clicked the result will appear incell J4. You ca
change the code to place result in cell you wish.


Code
-------------------
Sub SetupFrameAndControls()

With ActiveSheet.Frame1
.Caption = "User Options"
.Height = 65
.Width = 110
End With

With ActiveSheet.Frame1.Controls
.Add "Forms.OptionButton.1", "OptionButton1", True
.Add "Forms.OptionButton.1", "OptionButton2", True
End With

With ActiveSheet.Frame1.Controls("OptionButton1")
.Left = 5
.Top = 10
.Caption = "Option 1"
.BackColor = vbButtonFace
.ControlSource = "'Sheet1'!$J$3"
End With

With ActiveSheet.Frame1.Controls("OptionButton2")
.Left = 5
.Top = 30
.Caption = "Option 2"
.BackColor = vbButtonFace
.ControlSource = "'Sheet1'!$J$4"
End With

End Sub

 

[Tom Ogilvy]

That's why frame controls are not included in the control toolbox when the
worksheet is where you are working. While you can do as Leith has suggested
( you can do it manually as well although it isn't straightforward), to the
best of my knowledge, there is no way to capture events produced by controls
actually on the frame. No only do they look klunky (in my opinion), I
always recommend that they were not designed to be used on worksheets and
are best avoided.

--
Regards,
Tom Ogilvy

Thank you for the information. I need to use the buttons' value property in
code I am writing for the a worksheet. When I put option buttons (say
OptionButton1 and OptionButton2) directly on a worksheet (not in a frame), I
use "OptionButton1.value" and "OptionButton2.value" to see which is selected
and to direct further actions. However, when I put the buttons into a frame
(named Frame1), than the code does not seem to recognize OptionButton1.value
and OptionButton2.value. I've tried accessing the value properties by
"frame1.OptionButton1.value", but that does not work. In fact, I have
similar problem for all the controls in put in the frame. I put a command
button in the frame ("CommandButton1"), but when I write code for the
worksheet it does not recognize that this object exists.

 

[Don Wiss]

That's why frame controls are not included in the control toolbox when the
worksheet is where you are working. While you can do as Leith has suggested
( you can do it manually as well although it isn't straightforward), to the
best of my knowledge, there is no way to capture events produced by controls
actually on the frame.

I like using a linked cell. I haven't actually implemented this yet, as I
was pulled to another project and I'm now on holiday this week. But playing
with them now I find that they are difficult to work with. If you simply
place OptionButtons on the frame when you exit Design Mode the
OptionButtons disappear. Seems they are being hidden behind the frame.

Then when you use Leith's macro to put the OBs on the frame you can't
simply get into the Design Mode for the OBs by clicking on them. So how?

All of this would be moot if all the objects on the Forms menu could be
resized and easily hidden.

No only do they look klunky (in my opinion), I
always recommend that they were not designed to be used on worksheets and
are best avoided.

You can get around the klunky look by changing the background colors to all
be white. Or all the same color that is used to signify an input cell. Or
maybe making the OBs transparent.

Don <www.donwiss.com> (e-mail link at home page bottom).