Hide Columns on multiple sheets based on option button

[iSoleil]

Hi guys,

My code to show/hide columns on multiple tabs does seem to work. I know it's very basic, but I just learned VBA codes from googling it and I can't seem to crack this one.

What I want to do is hide columns F:J on sheets 1-31 (same sheet names too) if OptionButton1 is clicked and show columns F:J if OptionButton2 is clicked.

This was my very mediocre attempt at testing it on Sheet1. Surprise, surprise. Not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If OptionButton1.Value = True Then
    Sheet1.Columns("F:G").Hidden = True
Else
    Sheet1.Columns("F:G").Hidden = False
End If
End Sub

Any help is greatly appreciated. Thanks!

 

[alansidman]

Maybe, this untested code

Option Explicit


Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
For Each sh In Worksheets
If Sheets("Sheet1").OptionButton1.Value = True Then
    sh.Shapes.Columns("F:G").Hidden = True
Else
    sh.Shapes.Columns("F:G").Hidden = False
End If
Next sh
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.OptionButton1.Value = True Then
For i = 1 To 31
    Sheets(i).Range("F1:J1").EntireColumn.Hidden = True
Next
Else
For i = 1 To 31
    Sheets(i).Range("F1:J1").EntireColumn.Hidden = False
Next
End If
ActiveSheet.OptionButton1.Value = False
End Sub

 

[My Aswer Is This]

I made a mistake try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.OptionButton1.Value = True Then
For i = 1 To 31
    Sheets(i).Range("F1:J1").EntireColumn.Hidden = True
Next
End If
If ActiveSheet.OptionButton2.Value = True Then
For i = 1 To 31
    Sheets(i).Range("F1:J1").EntireColumn.Hidden = False
Next
End If
ActiveSheet.OptionButton2.Value = False
ActiveSheet.OptionButton1.Value = False
End Sub

 

[iSoleil]

I made a mistake try this:

Private Sub Worksheet_Change(ByVal Target As Range)If ActiveSheet.OptionButton1.Value = True ThenFor i = 1 To 31    Sheets(i).Range("F1:J1").EntireColumn.Hidden = TrueNextEnd IfIf ActiveSheet.OptionButton2.Value = True ThenFor i = 1 To 31    Sheets(i).Range("F1:J1").EntireColumn.Hidden = FalseNextEnd IfActiveSheet.OptionButton2.Value = FalseActiveSheet.OptionButton1.Value = FalseEnd Sub

Hi myans,

It's not working.

 

[iSoleil]

Maybe, this untested code

Option ExplicitSub Worksheet_Change(ByVal Target As Range)Dim sh As WorksheetFor Each sh In WorksheetsIf Sheets("Sheet1").OptionButton1.Value = True Then    sh.Shapes.Columns("F:G").Hidden = TrueElse    sh.Shapes.Columns("F:G").Hidden = FalseEnd IfNext shEnd Sub

Hi alansidman,

Sorry, I forgot to mention that my option buttons are on a different sheet named "Main".

I need it to hide columns F:G on 31 other sheets named "1", "2", "3",..."31".

The actual sheet names correspond to the sheet number.

Thanks for the quick response tho.

 

[My Aswer Is This]

Do you know how to install sheet change event script?
And a sheet change event requires you to make some manual change in the sheet.
Clicking a option button is not a sheet change event.

Hi myans,

It's not working.

 

[iSoleil]

I made a mistake try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.OptionButton1.Value = True Then
For i = 1 To 31
    Sheets(i).Range("F1:J1").EntireColumn.Hidden = True
Next
End If
If ActiveSheet.OptionButton2.Value = True Then
For i = 1 To 31
    Sheets(i).Range("F1:J1").EntireColumn.Hidden = False
Next
End If
ActiveSheet.OptionButton2.Value = False
ActiveSheet.OptionButton1.Value = False
End Sub

You lost me at "install".
I'm sorry, I don't know anything. T^T

Do you know how to install sheet change event script?
And a sheet change event requires you to make some manual change in the sheet.
Clicking a option button is not a sheet change event.

 

[alansidman]

Option Explicit


Sub Worksheet_Change(ByVal Target As Range)


Dim sh As Worksheet
For Each sh In Worksheets
If ws.Name <> "Main" Then
If Sheets("Main").OptionButton1.Value = True Then
    sh.Shapes.Columns("F:G").Hidden = True
Else
    sh.Shapes.Columns("F:G").Hidden = False
End If
End If
Next sh
End Sub

 

[alansidman]

Here is a corrected code. I had a typo in the above code.

Sub Worksheet_Change(ByVal Target As Range)


Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name <> "Main" Then
If Sheets("Main").OptionButton1.Value = True Then
    sh.Shapes.Columns("F:G").Hidden = True
Else
    sh.Shapes.Columns("F:G").Hidden = False
End If
End If
Next sh
End Sub