Ask Your Question
1

Unable to hide a cell from printing using macro

asked 2020-07-30 15:58:20 +0200

squire ash gravatar image

updated 2020-07-30 15:59:37 +0200

I want to hide 2 cells (Which contain say discount and discount %) from printing ( not make them invisible) when discount percentage is 0 using a macro is 0. Here is the code I have used:

        rem before this is some other code

         If DiscPercent.getValue = 0 Then
        Disc.CellProtection.isPrintHidden = True
        DiscPercent.CellProtection.isPrintHidden = True
        Print DiscPercent.CellProtection.isPrintHidden
    Else
        Disc.CellProtection.isPrintHidden = False
        DiscPercent.CellProtection.isPrintHidden = False
End If

The trouble is that the isPrintHidden property is not getting set and I don't know why. I am only able to change this property from the calc UI and I wrote a dummy macro to verify this. Is this property a read only property?? Am I missing something??

Thanks

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2020-07-30 20:45:12 +0200

Lupp gravatar image

updated 2020-07-31 12:20:00 +0200

In short:

cp = cell.CellProtection  
cp.IsPrintHidden = True  
cell.CellProtection = cp

There are properties of structured types which only can be set by assigning a new "value" of the structure as a whole. This is similar to the way you need to handle some array-properties of cell ranges (DataArray e.g).
From memory I would say that this is not a concept consistently applied to all structured properties.

BTW: You can even hide cells from printig by ConditionalFormatting. This may in some cases help to avoid macro programming.
===Edit 2020-07-31 about 09:30 UTC===
Concerning the usage of CF see this example: (deleted) ===Edit shortly after===
The uploaded example had a little flaw concerning a comment on merging. The attachment below should fix this.
C:\fakepath\ask257890conditionallyHideWhenPrinting.ods

edit flag offensive delete link more

Comments

Thank you very much @Lupp. It worked. I did not know that I needed to overwrite the value of the structure itself. I will also investigate using the conditional formatting option.

squire ash gravatar imagesquire ash ( 2020-07-31 04:56:10 +0200 )edit

Thanks for the example on conditional formatting. I will look into i for further clarity

squire ash gravatar imagesquire ash ( 2020-07-31 11:51:08 +0200 )edit

I tried using conditional formatting and it was a way better solution than the macro. Thanks for the advice @Lupp .

squire ash gravatar imagesquire ash ( 2020-08-05 09:50:54 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-30 15:58:20 +0200

Seen: 24 times

Last updated: Jul 31