VBA Macro Help - Hide/Unhide one column across multiple sheets

Question

I am having trouble with a macro in excel. I can't seem to write a macro that will hide/unhide one column on all 10 sheets. The thought is if the value in cell X26 on sheet 10 is 1, column G on sheets 1-10 is visible. If the value in X26 on sheet 10 is anything else, it's hits. I've spent alot of hours on this and I can't seem to figure out what I am doing wrong. My goal is not to use a button. I just want to change the value to make the macro work. I am appreciate any help you can provide. Thanks!

Answer 1

Hi Chis

The following macro is NOT for a regular module, it belongs to the Worksheet_Change event panel as shown in the picture below.

Here is the code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim X As Integer

If Not Application.Intersect(Target, Range("X26")) Is Nothing Then 

    Application.ScreenUpdating = False      

        If Target.Value = 1 Then 

            For X = 1 To 10 

            Sheets(X).Range("G1").EntireColumn.Hidden = False 

            Next X 

        Else 

            For X = 1 To 10 

            Sheets(X).Range("G1").EntireColumn.Hidden = True 

            Next X 

        End If 

End If 

Target.Select 

Application.ScreenUpdating = True

End Sub

Do let me know if you need more help

Regards

Jeovany

Answer 2

Thanks for the help, Jeovany. When I put the code in the worksheet and hit run, the Macro name box shows up. I won't let me run the private sub without putting something in there. Please see below.

It's like it wants me to put something the module box. When I do and try to find a call sub, it doesn't seem to work.

Thanks!

Answer 3

Nevermind. You're a genius. I was trying to click run when I didn't have to. Once I changed the code to look at the correct cell, everything worked! Thank you so much.

Answer 4

Hi Jeovany,

Sorry about the additional message. I went to save my workbook and now the macro won't run after I reopened it. I can't figure out why. Here is the current code I have:

I was working before I saved it and then when I went to close and reopen, I won't work anymore. The only change in I made in your code was the cell I was using in sheet 10. It's X 23. What I have in the module is nothing. Am I supposed to use a call function in the module to have this macro work or is there a setting that I need to make sure I have enabled? It was working just fine before I closed it.

Thanks,

Chris

Answer 5

Nevermind again. Apparently, I just need to take a break and come back to find my error. Thanks again!