Remove VBA loop on all worksheets.

[RPM7]

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

 

[mole999]

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]

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]

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:-

Dim ws As Worksheet

Remove:-

For Each ws In Worksheets

wherever you see this line in the code.

Replace:-

With ws

with:-

With ActiveSheet

Remove:-

Next ws

wherever you see it in the code.

This should do it for you.

Cheerio,
vcoolio.