Multiple Macro Buttons Hide/Show Specific Amount of Lines

DoctorK

New Member
Joined
Dec 1, 2016
Messages
12
Hi,

I'm fairly new to vb all on my own and have a small understanding of it. I'm in voice engineering so I'm trying to map a sales workbook/form to a hidden tab for building the telephone lines in the switch via .csv, so learning excel was something I didn't expect.

I have read many posts before resorting to a new thread and can't seem to find the proper statements to make this work properly.

The issue is pretty simple. I made buttons to show/hide as many lines as sales reps need to add phone numbers in the fields. The buttons are to simply shorten the fields to condense the large sections I have below it. This is what is created:

Row 15 is where I created the 6 buttons. Row 17 is where I started 'line 1'. So the buttons are
10 lines, 20 lines, 50 lines, 100 lines, 150 lines and finally 200 lines

Click on the 10 Lines button and the function will show only the first 10 lines and hide the other 190. Click again or it will show the rest of them (200).

The button for 10 lines works excellent. I duplicated the similar instructions for the 20 lines but there's a small problem when clicking on one to the other. It will either skip some in between and show the rest or it won't work at all. the buttons have to be clicked in a way to reset them back to show the 200, but they work independently if the 200 are showing from the start.

example:
>all 200 lines showing
>click on the 10 lines button and it's reduced down to 10 rows I need.
>click on the 20 lines button and lines 1-10 show, 11-20 remain hidden, 21-200 show.
>click on the 20 lines button again and only 1-10 show.



I read somewhere that there is no way to overcome this issue without having a 'reset' button to show all the 200. I'd like to think there can be some instruction to have it check to see if any of the lines are hidden, show them all and then carry out what needs to be hidden.

If there's a better way of doing this, I'm all for it. I just want to keep the options there for the sales reps to choose what they need. My code:

----------------------------------------------------------------------------------

Sub TenLinesMacro()
'
' TenLinesMacro Macro
'


'
ActiveWindow.SmallScroll Down:=12
Rows("27:216").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Range("B26").Select
ActiveWindow.SmallScroll Down:=-12
Else
Selection.EntireRow.Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End If
End Sub
Sub TwentyLines()
'
' TwentyLines Macro
'


'

ActiveWindow.SmallScroll Down:=12
Rows("37:216").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Range("B26").Select
ActiveWindow.SmallScroll Down:=-12
Else
Selection.EntireRow.Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End If
End Sub


----------------------------------------------------------------------------------



Thanks!



- DoctorK
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Another approach...

Code:
Sub TenLinesMacro()
' TenLinesMacro Macro
Cells.EntireRow.Hidden = False
Rows("27:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-12
End Sub

Sub TwentyLines()
' TwentyLines Macro
Cells.EntireRow.Hidden = False
Rows("37:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End Sub

Sub AllLines()
' Show all lines
Cells.EntireRow.Hidden = False
End Sub
Rather than have each button act as a toggle to hide/show rows (which is the root of the problem when clicking from button to button), this approach simplifies each button - first unhiding all rows, then hiding the specified rows. A 7th button is added to show all rows.

Cheers,

tonyyy
 
Last edited:

DoctorK

New Member
Joined
Dec 1, 2016
Messages
12
Thanks tonyy!

I had 200 lines which was really the operation to "show" all lines. The key point you made was to keep to the objective and keep it simple and I strayed from that by allowing the toggle button to get the best of me.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
You're welcome, DoctorK. Glad it worked out for you.
 

DoctorK

New Member
Joined
Dec 1, 2016
Messages
12
One problem it seems. It's intermittent. Every once in a while, running the macro gives a Run-time error '1004' "Unable to set the Hidden property of the Range class."

This is all I've got

Code:
Sub TenLines()
' TenLines Macro
Cells.EntireRow.Hidden = False
Rows("27:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-12
End Sub


Sub TwentyLines()
' TwentyLines Macro
Cells.EntireRow.Hidden = False
Rows("37:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End Sub


Sub FiftyLines()
' FiftyLines Macro
Cells.EntireRow.Hidden = False
Rows("67:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End Sub


Sub OneHundredLines()
' OneHundredLines Macro
Cells.EntireRow.Hidden = False
Rows("117:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End Sub


Sub OneFiftyLines()
' OneFiftyLines Macro
Cells.EntireRow.Hidden = False
Rows("167:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End Sub


Sub TwoHundredLines()
' TwoHundredLines Macro
Cells.EntireRow.Hidden = False
Range("B26").Select
ActiveWindow.SmallScroll Down:=-36
End Sub


Sub AllLines()
' Show all lines
Cells.EntireRow.Hidden = False
End Sub
Did I add something in there I wasn't? I only copied and pasted and made more. This is all I need.

Thanks
 

DoctorK

New Member
Joined
Dec 1, 2016
Messages
12
I might also add that the "TwoHundredLines" basically unhides all that had. The error doesn't seem specific to just that macro command though.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Run-time error '1004' "Unable to set the Hidden property of the Range class."
One condition that could produce this error is a Protected sheet.
 

DoctorK

New Member
Joined
Dec 1, 2016
Messages
12
I've experimented with it and that seems to be it. I'm okay with not locking down those specific cells like before. If the sales reps mess it up, I just won't accept it. Ty.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
Not all sales reps mess things up, but for the mavericks on your team...

Code:
Sub TenLines()
' TenLines Macro
[COLOR=#ff0000]ActiveSheet.Unprotect 'Password:="12345"[/COLOR]
Cells.EntireRow.Hidden = False
Rows("27:216").Hidden = True
Range("B26").Select
ActiveWindow.SmallScroll Down:=-12
[COLOR=#ff0000]ActiveSheet.Protect 'Password:="12345"[/COLOR]
End Sub
Passwords are optional, and commented out in the above.
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top