macro works on one sheet but not the other.

[simusphere]

I have attached my office football pool spreadsheet I have been working on and there are multiple problems I am trying to figure out. The first one is driving me crazy because the macro works on the old sheet and not on my new one. They look identical but the only difference is that I am using activex radio buttons on the new sheet and the forms radio buttons in the old sheet.

The macro is called "Make Printable" and is attached to the Make Printable command button in the upper left hand corner of each sheet. They are using the exact same macro but the results are perfect sizing and page brakes in the old sheet and over sizing incorrect page brakes in the new one. I also noticed that in the new sheet the first row is getting printed which should not happen because that is a hidden row. I thought pastespecial xlFormats is supposed to only past the visible rows. At least it works on my old sheet. You can step through the macro and see when the first row stays visible right after the pastespecial xlFormats line of code. I'm stumped why this is happening.

Bonus problem #1: The radio buttons in both sheets are somehow interacting with each other which is not the way I want them to work they should be in their own group or frame on each sheet (which they are I think) and not affect one another. Why does clicking on a button in one sheet erase the selection in the other sheet?

Bonus problem #2: How can I use a check box control to turn off the conditional formatting temporarily.

[Norie]

What do you want to do to make the sheets printable?

Remove the option/command buttons, hide the first row, set the page breaks and replace 1s with Xs?

By the way, the option buttons on both sheets are ActiveX.

PS The rows on both sheets aren't the same height, that could be what's affecting the page breaks.

Yep, when I manually change the row height on the new sheet the page breaks work.

[simusphere]

The make printable button adds a footer with page numbers, pastes the values of the formula's to another sheet and changes the 1's to X's. The old sheet is working great, I just wanted to change the radio buttons from the old form radio buttons to the new activex radio buttons which allows more manipulation of the properties. I have a macro in there to unhide the radio button grouping on the old sheet called Unhide_Group_Boxes. If you run it you will see the difference in the radio buttons from the old sheet to the new sheet. The new sheet does not use frames around the radio buttons, just same group name property per game.

If you are talking about the resulting printable sheet having different row height that IS the problem. How do I get the macro makeprintable to work so the formatting of the original sheet remains the same 21 pixels, like it does on my old sheet without unhiding row 1? The damn thing works fine on the old sheet which doesn't make any sense at all.

Please Login or Register  to view this content.

[Norie]

As far as I can see there are no Forms option buttons on either sheet.

If you goto Design mode and right click any of the option buttons you'll have a Properties option, that's not available to Forms controls.

Each of the option buttons has a GroupName property and that's what you use to make exclusive groups.

For example if you look at the properties of the option buttons located in column C and D on either sheet they both have the GroupName - 'Game 1' on the old sheet and GM1 on the new sheet.

As for the row heights, they aren't copied over when you use PasteSpecial, just as the hidden row isn't hidden.

So you end up with the default row height of a new sheet.

Have you considered copying the sheet then removing all the buttons from the copy and doing all the other stuff on it too?

[simusphere]

I guess my question is why does the macro work perfectly on the old sheet and not on the new sheet? Answer that one and you will ease my frustration.

I though the radio buttons were different because when I made them on the old sheet I used the insert button on the ribbon and used the upper form controls. On the other sheet I used the active x controls. Are you telling me there is no difference between the two?

Also why do the radio buttons on one sheet affect the other as if they are in the same group name when clearly they are not?

[Norie]

There must be some difference in the sheets, but for the life of me I can't see it.

Perhaps something in the hidden columns.

As for the option buttons, Forms and ActiveX controls are different but there are no Forms option buttons on either sheet.