Multiple Macro Buttons Hide/Show Specific Amount of Lines

[DoctorK]

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]

Another approach...

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

 

[DoctorK]

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]

You're welcome, DoctorK. Glad it worked out for you.

 

[DoctorK]

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

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]

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]

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]

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]

Not all sales reps mess things up, but for the mavericks on your team...

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.