? Possible for 'Button' to Reveal Hidden Rows (previously set up with relevant forumulae)

[PurrSent]

Using Excel 2010 and Windows 10 s1803

Hi

I’ve got a workbook with tabs: Summary / Jan / Feb / … / Dec

The Summary picks up the various row totals in each of the Month’s sheets

I will (potentially) need extra rows in each of the Monthly sheets so I have already set up, with formulae, these extra rows in each of the Monthly sheets, which I would like hidden until required.

I would like to have a cell, in each Monthly sheet, that I can just click on (similar to a link) that would reveal 1, or more, hidden row

Is this possible, please?

Assuming this would need VBA, would you please bear in mind that my knowledge of VBA is approx nil / very very limited. [I did do a semester on programming (Delphi) when doing Business IT degree, over 10 yrs ago, but had to work hard to grasp it ]

Many thanks

 

[tonyyy]

PurrSent,

You might consider the following...

Sub unHideRows()
Dim lastRow As Long, rws As Long
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
rws = Application.InputBox(prompt:="Please enter the number of lines to unhide.", Type:=1)
ActiveSheet.Rows(lastRow + 1 & ":" & lastRow + rws).Hidden = False
End Sub

Sub addButtons()
''''Add a button to each sheet (except Summary sheet)
Dim r As Range, btn As Button, ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "Summary" Then
        Set r = ws.Range("A1:B1") 'Button will fill this range; change range as desired
        Set btn = ws.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
        With btn
            .OnAction = "unHideRows"
            .Caption = "unHideRows"
        End With
    End If
Next ws
End Sub

Sub deleteButtons()
''''Delete all buttons from all sheets
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    ws.Buttons.Delete
Next ws
End Sub

Paste the three macros into a standard module.
Run the addButtons macro. This will create a button on each sheet in Range("A1:B1"). Change the range if you prefer the button in a different location.
Clicking a button will prompt you to enter the number of rows to unhide, then proceed to unhide the additional rows.
The deleteButtons macro is provided in case you want to start over.

Cheers,

tonyyy

 

[PurrSent]

Hi Tonyyy

Many thanks for posting this

Sorry to be so dense, but do I paste all three into the one module or should I paste each in separate modules?

Many thanks
%

 

[PurrSent]

Well, I've copied & Pasted into three modules, as I decided this was most likely the way forward.
When coming to save this, I keep getting the message :

The following features cannot be saved in macro-free workbooks:
"- VB project
To save a file with these feratures, click No, and then choose a macro-enabled File Type list.
To continue saving as a macro-free workbook, click Yes"

So, in Excel Workbook, I've clicked on File, Options, Trust Centre, Trust Centre Settings ...,
then I've clicked on "Enable all macros (not recommended; potentially dangerous code can run)
and also put a tick in the box for "Trust access to the VBA propject object model"

Having done all this, Excel won't let me save this.

Please enlighten me as to what I need to do

Many thanks
%

 

[tonyyy]

Save a macro-enabled workbook

Adding Code to an Excel Workbook

 

[PurrSent]

Hi guys

Thanks Tonyyy for the links. They do help a lot but ... I'm really struggling I clearly need to learn a lot more about all this, not that I'm particularly surprised.

I have now enabled macros and saved the file as .xlsm. I've copied and pasted the code, each into it's own Module, ie Module 1, Mod 2, Mod 3

It's not surprising that, when I click on 'Run' for any of these macros, they don't work. I realise I need to edit the cell references but I'm unclear as to what to replace the references with. For example, regarding the Hidden Rows, they are rows 51 to 117. In the code, where it says
"lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row", I thought, maybe, I need to replace 'A' with a row number so I changed 'A' to '51' but that didn't work. I tried '117' but that didn't work either. Debug highlighted the code where I had changed the 'A' to a row number. So, stumped!!!

I have no idea, as yet, how to add an 'object' to the Worksheet, let alone how to have the code active on it

The code suggests not adding a Button to 'Summary' sheet, which is good. I also have 'Master' and 'Notes' sheets too, neither of which will want such a button. I need to find out how to edit the code to include these.

Another thing is that I'm wanting to Lock various cells (where there is formulae) and so the sheet will be Protected. Will this affect the running of the Macro[s}?

So, as you can gather, I am certainly struggling with this and would really appreciate any help offered.

Many thanks
%

 

[tonyyy]

If you don't want to add buttons to the Master and Notes sheets (as well as the Summary sheet), replace the addButtons code with the following...

Sub addButtons()
''''Add a button to each sheet (except Summary sheet)
Dim r As Range, btn As Button, ws As Worksheet
For Each ws In ThisWorkbook.Sheets
   [COLOR=#0000ff] If ws.Name <> "Summary" And ws.Name <> "Master" And ws.Name <> "Notes" Then[/COLOR]
        Set r = ws.Range("A1:B1") 'Button will fill this range; change range as desired
        Set btn = ws.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
        With btn
            .OnAction = "unHideRows"
            .Caption = "unHideRows"
        End With
    End If
Next ws
End Sub

(The line of code in blue is the line that changed.)

I suggest running the macros on a copy of your workbook (ie, do some testing) before running them on the real workbook.

Regarding Protected sheets - the macros will not run correctly (if at all) on protected sheets. For testing purposes, unprotect the sheets then run the addButtons macro. If that is successful, then click one of the buttons (it will initiate the unHideRows macro) and see if that works.

If all that works satisfactorily, then we can address the Protected sheets issue with additional code to unprotect the sheet, run the code, then re-protect the sheet. I'll need to know if the sheet is password protected (please, do not provide the password.)

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

This line of code finds the last unhidden row in the ActiveSheet. It does so by starting in Column A and counting the number or rows (over a million in Excel 2010); then it moves up (similar to holding the ctrl key and pressing the Up Arrow. It's assumed Column A is filled with data or formulas; if it contains empty cells, choose a Column without empty cells by changing the letter "A" to the appropriate column letter.

 

[PurrSent]

As always, many thanks

Yes, you may be relieved to know that I’ve been ‘practicing’ on a copy of the worksheet and not the actual worksheet. In fact, I’ve saved the worksheet as a template so it’s easy enough to start over, if necessary

I feel I’m following, so far, and can understand the logic of the code you’ve supplied. I can see how ‘Master’ and ‘Notes’ sheets have been included. Thanks

I have also worked out how to add a button to a sheet, using Form Control rather than ActiveX. From what I’ve read, it seems Form Control should be sufficient for this purpose. Please correct me if I’ve got that wrong.

I don’t have a column that has data in each cell so, as ‘A’ column is just a blank border, I have typed in ‘1’ in all the cells from A1:A151 (A151 being the last + one row that has anything entered)

Rows 51:117 are the hidden rows

So, on running this code, I get the box asking how many rows to unhide, enter a number, ie 1, or 5), clicking OK, but nothing happens, none of the hidden rows are revealed

Sub unHideRows()
Dim lastRow As Long, rws As Long
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
rws = Application.InputBox(prompt:="Please enter the number of lines to unhide.", Type:=1)
ActiveSheet.Rows(lastRow + 1 & ":" & lastRow + rws).Hidden = False
End Sub

The worksheet is unlocked / unprotected. I can’t think what else I need to do. Developer tab is available; the workbook is saved as .xlsm; macros are enabled.

Would you help me to get this to run properly, please?

Many thanks
%

 

[tonyyy]

So, I had assumed incorrectly that the hidden rows were at the bottom of the range, when in fact they are "sandwiched" between visible rows.

The following code loops through the rows to determine the first hidden row and uses that row as the basis for unhiding rows...

Sub unHideRows()
Dim lastRow As Long, rws As Long, hiddenRow As Long
Dim r As Range
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For Each r In Range("A1:A" & lastRow).Rows
    If r.EntireRow.Hidden Then
        hiddenRow = r.Row
        Exit For
    End If
Next r
If hiddenRow > 0 Then
    rws = Application.InputBox(prompt:="Please enter the number of lines to unhide.", Type:=1)
    ActiveSheet.Rows(hiddenRow & ":" & hiddenRow + rws - 1).Hidden = False
Else
    MsgBox "There are no hidden rows."
End If
End Sub

 

[PurrSent]

Tonyyy

This is now working brilliantly I'm so pleased. Thank you so so much

Would you mind now helping me with getting this working on Protected Sheets, please? There is no Password to the Sheet protection. I've just left that blank. The worksheets are protected only so that formulae are'nt accidentally over-written etc.

Regarding Protected sheets - the macros will not run correctly (if at all) on protected sheets. For testing purposes, unprotect the sheets then run the addButtons macro. If that is successful, then click one of the buttons (it will initiate the unHideRows macro) and see if that works.

If all that works satisfactorily, then we can address the Protected sheets issue with additional code to unprotect the sheet, run the code, then re-protect the sheet. I'll need to know if the sheet is password protected (please, do not provide the password.)

I'm reading up about macros working on Protected sheets, so as to understand a bit more but it's certainly complicated

As always, many thanks for all your help. I really appreciate it

%