Checkboxes disappear after macro is run

[Jonas]

Hi

I have a macro that loops through a bunch of checkboxes to see if they are
checked or not. If they are not checked the row it sits on plus the row below
is hidden. Also the unchecked checkbox is also hidden. After this I call on
print preview.

Sub print_checked()

Dim obj As OLEObject

Application.ScreenUpdating = False

For Each obj In ActiveSheet.OLEObjects
'first check to see if the checkbox really is a checkbox
If TypeOf obj.Object Is MSForms.CheckBox Then
'then if the checkbox is not checked i hide rows and the
checkbox itself
If obj.Object.Value = False Then
ActiveSheet.Rows(obj.TopLeftCell.row).EntireRow.Hidden = True
ActiveSheet.Rows(obj.TopLeftCell.row + 1).EntireRow.Hidden =
True
obj.Visible = False
End If
End If

Next obj

Application.ScreenUpdating = True

ActiveWindow.SelectedSheets.PrintPreview

End Sub

When printing is done I wish to restore the sheet to its orginal look with
all rows and checkboxes visible again. Therefore I have this code:
Code:

Sub restore_from_print()

Dim obj As OLEObject

Application.ScreenUpdating = False

For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
If obj.Object.Value = False Then
ActiveSheet.Rows(obj.TopLeftCell.row).EntireRow.Hidden = False
ActiveSheet.Rows(obj.TopLeftCell.row + 1).EntireRow.Hidden =
False
obj.Visible = True
End If
End If

Next obj

Application.ScreenUpdating = True

End Sub

BUT neither the rows or the checkboxes get visible, at least not all of
them. Sometimes it seems a couple of rows are shown. If I run the code
without calling on print preview the restore-code seems to work fine. ?!

What am I doing wrong here?

Regards Jonas

 

[JLGWhiz]

If you are manually invoking the print command from the preview screen, it
should close that screen and return to normal view upon completion of the
printing. However, on the chance that it does not, try inserting this at
the beginning of the second macro.

ActiveWindow.View = xlNormalView

 

[JLGWhiz]

I looked at your post again, and see that I misread the problem. I think
maybe this modified version of your second macro might cure the problem.

Sub restore_from_print()

Dim obj As OLEObject

Application.ScreenUpdating = False

For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
If obj.Object.Value = False Then
ActiveSheet.Rows(obj.TopLeftCell.Row - 2).EntireRow.Hidden =
False
ActiveSheet.Rows(obj.TopLeftCell.Row - 1).EntireRow.Hidden =
False
obj.Visible = True
End If
End If

Next obj

Application.ScreenUpdating = True

End Sub

'When the row is hidden, the top left relative cell reference changes.

 

[Jonas]

I looked at your post again, and see that I misread the problem. I think
maybe this modified version of your second macro might cure the problem.

Sub restore_from_print()

Dim obj As OLEObject

Application.ScreenUpdating = False

For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
If obj.Object.Value = False Then
ActiveSheet.Rows(obj.TopLeftCell.Row - 2).EntireRow.Hidden =
False
ActiveSheet.Rows(obj.TopLeftCell.Row - 1).EntireRow.Hidden =
False
obj.Visible = True
End If
End If

Next obj

Application.ScreenUpdating = True

End Sub

'When the row is hidden, the top left relative cell reference changes.

Hmm, I get it but your suggestion won't do the trick.
The row references seem to get messed up somewhere.
Any other ideas?

Regards Jonas

 

[JLGWhiz]

Maybe I still don't understand the problem. I used a single checkbox on a
sheet and ran your macros. The first macro hid two rows, the the second
macro, as modified, unhid them. I have now tried it with two checkboxes and
it worked OK. Maybe it has to do with where the checkboxes are located. Are
any on the same row? Have you tried using a different reference from the
TopLeftCell? Like a public variable?

 

[Jonas]

Hi, been traveling but now back to solve this...

My check boxes are located on separate rows. About 20 of them on each sheet.
Your code hides and shows rows but not all rows. Very strange.
Referencing with a public variable - how do you mean?

/J

 

[JLGWhiz]

I was thinking along the lines of :

Public myRow = obj.TopLeftCell.Row

But that doen't really do much more than what you have now. It just assures
that there is an integervalue assigned to the variable. Probably not worth
the effort.

When you say "not all rows", do you mean not all rows per check box or all
row for some check boxes and none for others, or is it a mixture of some and
none? I can't get a clear picture of what is happening? It looks like you
are hiding two rows per check box and then, If the checkbox value is false,
you unhide the rows. Have you looked to see if the value is False for those
checkboxes where the rows do not become visible again?

 

[Jonas]

Could I possibly mail you my workbook and you have a look at it directly.
There I could more easily illustrate the problem.
If this is possible and you don't wish to post your address here mail me at
nacknoemo(at)hotmail.com

Regards Jonas