Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
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. [code] 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.Hi dden = 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.Hi dden = 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
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 "Jonas" wrote in message ... 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. [code] 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.Hi dden = 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.Hi dden = 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
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" wrote in message ... 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. [code] 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.Hi dden = 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.Hi dden = 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
"JLGWhiz" wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
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" wrote: "JLGWhiz" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
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" wrote: 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" wrote: "JLGWhiz" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
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" wrote in message ... 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" wrote: 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" wrote: "JLGWhiz" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkboxes disappear after macro is run
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 "JLGWhiz" wrote: 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" wrote in message ... 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" wrote: 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" wrote: "JLGWhiz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear checkboxes | Excel Worksheet Functions | |||
Multiple checkboxes, one macro? | Excel Programming | |||
Worksheet Buttons disappear after Macro runs! | Excel Programming | |||
Enable & Disable Macro popup disappear | Excel Programming | |||
Macro buttons disappear when I print | Excel Programming |