Running macro disrupts links on option buttons

Topic

New Reply

In an XLSM Excel 2007 workbook, there are macros that hide or unhide various rows via a user form. Rows include option buttons in groups of 3. When the macro is used to hide rows, then unhide them later, the option buttons link to incorrect cells, some of the cells in different rows all of a sudden link to the same source, or the link is missing. There is a protect macro that runs first, then the user form. Then I have to unprotect the worksheet to choose options, which is when I realize they don’t work correctly.

Has anyone else experienced this? Any thoughts of whether it’s option button related or macro related? I helped with the macros and they were working. The end user added the option buttons later, which is when the problems started.

I’m attaching a sample file. The password is “password” and the “change” button launches the form. I’ve been experimenting with including the “Individual” and then excluding the “Individual” option.

Reply | Quote

Replies

After opening your file [small.xlsm] using Excel2007:
I clicked the [Close] button on the Checklist Options Form that is displayed.
I then Unprotected the worksheet using the password supplied.
Observations:
In row1, cols [A], and [C] there are’ghost’ images of option button groups.
I counted five ‘picture’ groups here. These ‘pictures’ shouldn’t be here (in my opinion).
i.e. if you right-click to select the radio button ‘grouping’ frame, you can ‘drag’ the group away to say, column [K]
After dragging the group away, it then seems that nothing has happened as you still have the ‘ghost’ radio button images as before in cols [A], and [C].
You can repeat this ‘select and drag away’ another four times until the radio buttons have completely disappeared from cols [A], and [C]. This is usually due to an ‘unintentional’ copy process.

Look at row 16. The group box frame around the radio option buttons looks a little thicker here.
That’s because there are two of them, one on top of another.
Specifically, if you right-click the frame and look in the ‘name box’ (i.e. to the left of the formula bar), you will see we have ‘Group Box 1094’ on top of ‘Group Box 1090’.
This is probably not wanted.
The group boxes all seem to extend into column [D].
Personally, I find this a little unsightly as it makes reading the entries in column [D] messy.
To ‘properly’ size and align each Group Box to fit to the right-edge of column [C] cells, keep the [Alt] key pressed down as you use the mouse to grab and move the group box ‘size handles’.
Pressing the [Alt] key while moving the size handle of an object causes it to ‘snap-to-grid’.
Which means cells on the spreadsheet.
I suspect that the reported problems could have occurred when hiding rows that contain these multiple ‘group’ boxes.
For example, if the group box doesn’t get entirely hidden (e.g. because it’s top or bottom edge overlaps a row which isn’t included in the hide command etc.). I have assumed that the properties of all objects have been set to ‘move and size with cells’ etc.
This may cause the radio buttons that have been hidden to ‘disconnect’ from the group box.
So, there is quite a bit of cleaning up to do in your example file.
I notice that the linked cell for the radio buttons are in column [L] of the corresponding row.
It would seem appropriate that the value in column [L] should be 1, 2 or 3 according to whether the radio button in corresponding col [A], or [C] is selected.
I notice that this is not always the case in the example file.
A quick way to check whether radio buttons have been correctly set is to enter value 1 in all rows in column [L].
This should result in all the radio buttons in column [A] being selected, and showing as ‘red’ as per your conditional formatting.
Then change all the values in col [L] to 2, to check column radio buttons.
Then change all the values in col [L] to 3, to check column [C] radio buttons.
This will show where you need to fix the radio buttons.
Also, rows 44 to 57 are pre-hidden in your sample file.
These rows don’t contain any radio buttons.
In terms of the Form design, it seems you can select from both the ‘Include’ and the ‘Exclude’ groups at the same time.
This doesn’t seem to make sense. For example, you shouldn’t be able to select ‘Include Aircraft’ as well as ‘Exclude Aircraft’ on the Collateral page.
The radio buttons on the Form do the hiding and showing of the relevant rows, but you could be left with the Form showing both selected.
It seems to me that there should be one group box for each ‘pair’ of buttons on each page of the Form.
Perhaps the Form should also use ‘check boxes’ to allow for multiple selections (e.g. for Collateral)?
I will have another look at your file soon, but I am travelling this week.
Hope this helps a little.

regards
zeddy

Reply | Quote

In addition to the above comments, when I opened the file most of the Group Boxes could not fit in the rows without overlapping the row above and below.

When I tried to correct the problem by resizing the Group Box it would not fit since the Option Buttons were already at their minimum size.

A simple solution is to resize all the row height so there is not overlap. Then adjust the Zoom on the sheet, and the Print scaling.

In addition if you want a list of all the shapes on the Worksheet the below Macro will show each Shape name beginning at C5000
Before running the below make sure that the sheet with the shapes is “Active”

Sub MyShapes()
Dim i As Integer
Dim zzz As Object
i = 1
Set zzz = ActiveSheet.Shapes
For Each Shape In zzz
Cells(4999 + i, 3).Value = zzz.Item(i).Name
i = i + 1
Next

End Sub

Good luck Tom D

Reply | Quote