asked on

Format Control on two Buttons

EE Pros,

With what little hair I still have, I'm pulling the rest of it out. I have two buttons and I'm trying to control the size in order to have them be the same. Each time I right click on either of them and go to FORMAT CONTROL and SIZE, no matter what combination I choose, Size, Ratio, Ratio, Size, Lock Aspect Ratio, etc. etc., I still cannot get them to size correctly. Additionally, I have them in a WS that contracts and expands based on an Outline setup. One problem I have had is that when I expand and contract the outline, the buttons sometimes change sizing and get so small, I have to relocate and resize them. Other times, when I contract the outline, the buttons still show even though they are not part of the highest level of outline.

Can someone give me an idea on how to get the following accomplished?

1.) Sizing so that when I enter the size or % it doesn't auto readjust? In which order do I need to enter the parameters?

2.) Prevent buttons from showing when they are not in the rows that contract when using "Outline" level 1

Example enclosed.

Thank you in advance.

B.
Same-Size-Buttons.xlsm

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

Shums,

You not only saved some of my hair that's left, more immediately, my hair that was gone began to rejuvenate itself! Thank you for the quick response. I tried out your recommendation and it worked perfectly. And, I learned something new.

Thanks again!

B.

Shums Faruk

LOL.

You're welcome B! Glad I was able to help :)

Jacques Geday

Well we always have this issue with the buttons and controls in Excel they go Waco several times despite all possible settings that could be there.

This piece of code will insure that the leader is Option Button 1 (in this case) can be modified to suit an other situation. and will align the next one based on its dimention. You have in H2 a possibility to set spacing between the controls

Try changing completely the shape of Option Button 1 then put a value in spacing and see the results. The macro will auto execute each time the sheet is updated. We can also modify this behavior.

check out the attached workbook and here is the code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim shp As Shape
Dim dLeft As Variant, dWidth As Variant, dHeight As Variant, dTop As Variant

If Sh.Name = "Sheet1" Then
    For Each shp In Sh.Shapes
        If shp.Type = msoFormControl And shp.Name = "Option Button 1" Then
            dLeft = shp.Left
            dHeight = shp.Height
            dWidth = shp.Width
            dTop = shp.Top
        End If
    Next shp
    
    For Each shp In Sh.Shapes
        If shp.Type = msoFormControl And Left(shp.Name, 6) = "Option" And shp.Name <> "Option Button 1" Then
            shp.Left = dLeft
            shp.Width = dWidth
            shp.Height = dHeight
            shp.Top = dTop + dHeight + Sh.Range("H2")
        End If
    Next shp
End If
End Sub

Open in new window

gowflow
Same-Size-Buttons-V01.xlsm

Jacques Geday

Ooops !!! I missed it. But no sweat you have an extra bonus.
gowlfow

Bright01

ASKER

Gowflow...... sorry.... Thanks for the additional insight!

B.