Remove VBA loop on all worksheets.

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
176
Can anyone help me with a simple problem.
I don't know much about VBA but can tweak code I find online to do some applications.

I want to use a toggle button to hide a range, but only do this on the worksheet that the toggle button is active on.
The code I found on-line is for all worksheets, but I don't know how to fix it.

Does anyone know how to fix this code?

Private Sub ToggleButton4_Change()
Application.ScreenUpdating = False
Dim ws As Worksheet
With ToggleButton4
If ToggleButton4.Value = True Then
ToggleButton4.BackColor = RGB(255, 199, 206)
ToggleButton4.ForeColor = RGB(192, 0, 0)
For Each ws In Worksheets
With ws
.Unprotect ("")
.Range(.Columns(38), .Columns(43)).Hidden = True
.Protect ("")
End With
Next ws
.Caption = "Show"
Else
ToggleButton4.BackColor = &H8000000F
ToggleButton4.ForeColor = RGB(0, 0, 0)
For Each ws In Worksheets
With ws
.Unprotect ("")
.Range(.Columns(38), .Columns(43)).Hidden = False
.Protect ("")
End With
Next ws
.Caption = "Hide"
End If
End With
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,337
Office Version
2019, 2016, 2013
Platform
Windows
what range are you looking to cover off
as referenced
.Range(.Columns(38), .Columns(43)).Hidden = True
would be AL to AQ

If you added to each worksheet "worksheet_activate" code, you could automatically hide the desired range, the rest of the time as your only normally viewing on sheet, it makes no difference if the sheet is hidden or visible
 

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
176
Hi mole999,

Thanks for the reply.

I'm not looking to cover off anything.
I just want to hide and unhide a range of columns using a toggle button.
What I have works per-se, but its also hiding the same columns in every work sheet which I don't want.

I don't know where to use the "worksheet_activate" code.
My VBA experience is very limited to say the least.

Can the existing code be modified in any way to work on the active sheet only?
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
953
Hello RPM7,

If the code you have is working OK and you just want to run it from toggle buttons on each separate sheet rather than all sheets at once, then try the following:-

Remove:-
Code:
Dim ws As Worksheet
Remove:-

Code:
For Each ws In Worksheets
wherever you see this line in the code.

Replace:-

Code:
With ws
with:-

Code:
With ActiveSheet
Remove:-
Code:
Next ws
wherever you see it in the code.

This should do it for you.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,121
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top