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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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

ADVERTISEMENT

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

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,261
Messages
5,657,695
Members
418,410
Latest member
CrishFergus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top