Remove VBA loop on all worksheets.

RPM7

Board Regular
Joined
Nov 28, 2007
Messages
191
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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
Back
Top