We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Unable to hide a cell from printing using macro [closed]

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

squire ash gravatar image

updated 2021-06-27 01:30:26 +0200

Alex Kemp gravatar image

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
        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??


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-06-27 01:30:38.840093

1 Answer

Sort by » oldest newest most voted

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.

edit flag offensive delete link more


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

Question Tools

1 follower


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

Seen: 97 times

Last updated: Jul 31 '20