• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to hide/unhide rows/columns in other sheets (with sht + wb protection), then print

Josh C

New Member
Hi there, thanks in advance for your help.

I have little experience with VBA. I have a workbook with worksheet and workbook protection and multiple sheets. I want to have a button on one of the sheets, that when clicked, will proceed to: 1. hide/unhide rows/columns in other sheets based on true/false cell values in the other sheets, and then print the some specific sheets (say sheet 5 to 10) if a cell value in each of the sheets = true i.e if false, do not print the sheet. Please help!

I've attached a sample. Cheers!
 

Attachments

  • upload sample.xlsx
    15.6 KB · Views: 6
Josh C
Are those sheets layouts in this sample as in Your real file?
... especially ranges which show data of hide/unhide rows or/and columns and print?
How to say those specific sheets if there are now four sheets ... (say sheet 5 to 10 ... hmm?) ?
and
Your: I want to have a button on one of the sheets, that when clicked, will proceed to:...
means that 'the button' sheet won't hide/unhide nor print!
Many things should be clear ... before more steps to do...
 
Hi vletm,

Thanks for the reply! It is similar to my real file. I was hoping that from the vba code, i could infer how to change the ranges/addresses of the cells to fit their actual location. Similarly i can infer how to rename the sheets to fit my actual workbook.

Let me reword my question:

I have a workbook with worksheet and workbook protection and multiple sheets (sht A - Sht D). I want to have a button on one of the sheets (say sht A), that when clicked, will proceed to: 1. hide/unhide rows/columns in other specific sheets based on true/false cell values in those sheets, and then print some specific sheets in a specified sheet range (say sht B to D by default) if a cell value in each of the sheets = true, i.e print sht B,C,D by default, but with a cell value in each sht B,C,D that if = False will have that sheet omitted in printing. Please refer to the attachment above. Thank you!
 
Josh C
> Layout - It is similar to my real file.
Means ... some fixed text should find in the whole sheet (now, there are no fixed text to find!)
That's possible ... but ... only with 'fixed text'.

> Your: I want to have a button on one of the sheets, that when clicked, will proceed to:...
means that 'the button' sheet ( eg "A") won't hide/unhide nor print!

> Your: I have little experience with VBA.
It would be much better and clear for You to offer same layout, than modify others made code from 'similarity...'.
 
hi vletm,

there are some fixed texts that i think matters - the password for WB and WS protection are fixed texts and in the sample file, same for the names of each sheet. The true/false values however are determined by a dynamic formula that will indicate to the macro whether the columns/rows are to be hidden or unhidden (note explicit action of unhiding as opposed to status quo).

means that 'the button' sheet ( eg "A") won't hide/unhide nor print! - yes, that is the intended result, i know my sample file indicated otherwise, sorry about that

It would be much better and clear for You to offer same layout, than modify others made code from 'similarity...'. I have done so taking googled vba code in the past. So i believe i can do it again this time. I have intentionally uploaded a sample file but with enough similarities that i think exist with my actual wb (which is a full blown rating model), so much that i can interpret the code and change the parameters to fit my wb. Thanks!
 
Josh C
I tried to find out something positive to comment ...
think > All matters, Excel won't think.
fixed > eg text 'Print sheet?' should be in fixed address - of course, it's value can change. If not in fixed address then that text have to find.
googled vba & I have little experience with VBA > Yes ... me too.
... as I underlined
... seems that this thread is for someone else
 
Hi,

Excel will think based on predefined logic. I have provided a sample file of which i know macros will work as planned based on predefined parameters - of which can be changed to reflect my actual file.

Yes, print sheet has fixed address - which is also fixed in each of the cells in the each of the specified sheets. Of course my actual WB has it in a different location but i can easily adjust the location.

I can see you have little experience with VBA.

Appreciate if you acknowledged your shortcomings internally from the beginning instead of ostensibly appearing like you could help once the uncertainties were clarified (which should have been implicitly apparent based on the initial attachment).

There's no need to hypothesise based on my actual file - providing a solution based on the attached sample file is good enough and i can tweak the code. If you can't even do it for the attached file then obviously there's no need to theorise whether you can do it for the actual one.
 
Hi !​
A demonstration as a beginner starter to paste to the ThisWorkbook module :​
Code:
Sub Demo1()
         Dim Ws As Worksheet, Rg As Range
    For Each Ws In Worksheets
          If Ws.Name <> "SHT A" Then
        With Ws.UsedRange
               Set Rg = .Find("Hide rows?", , xlValues, xlWhole)
            If Not Rg Is Nothing Then
                For Each Rg In Range(Rg(2), Ws.Cells(.Rows(.Rows.Count).Row, Rg.Column))
                      If Rg.Value2 = True Then Rg.EntireRow.Hidden = True
                Next
            End If
               Set Rg = .Find("Hide columns?")
            If Not Rg Is Nothing Then
                For Each Rg In Range(Rg(2), Ws.Cells(Rg(2).Row, .Columns(.Columns.Count).Column))
                      If Rg.Value2 = True Then Rg.EntireColumn.Hidden = True
                Next
            End If
               Set Rg = .Find("Print sheet?")
            If Not Rg Is Nothing Then If Rg(1, 2).Value2 = True Then Ws.PrintOut
        End With
          End If
    Next
               Set Rg = Nothing
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Back
Top