Columns really will not hide/unhide with VBA code

[joe-engr]

I have searched the forum related to hiding & unhiding ranges, but none of the code examples/solutions have done the trick for me!

I want to be able to unhide a column in multiple sheets with the click of a button. I then have a second button which will hide these columns. Here is my code from the HIDE button:

Private Sub CommandButton3_Click()
Sheets("Do").Activate
Columns("F:F").EntireColumn.Hidden = True
Sheets("Re").Select
Columns("F:F").EntireColumn.Hidden = True
Sheets("Mi").Select
Columns("F:F").EntireColumn.Hidden = True
Sheets("Fa").Select
Columns("F:F").EntireColumn.Hidden = True
Sheets("So").Select
Columns("F:F").EntireColumn.Hidden = True
End Sub

When the button is clicked, I can see that the macro runs through the sheets (screen flashes quickly), but then Column F in each of the sheets is still visible. I am receiving no error.

May I please get some ideas?

Joe

 

[JamesW]

Welcome to the board.

Hmm, that's weird.. Works fine for me.

What happens when you try to do it manually?

 

[VoG]

Try

Private Sub CommandButton3_Click()
Dim ws As Worksheet
For Each ws In Sheets(Array("Do", "Re", "Mi", "Fa", "So"))
    ws.Columns("F").Hidden = True
Next ws
End Sub

 

[joe-engr]

Thanks for the welcome! I usually troubleshoot myself, and my research on the internet is sufficient for code help. I am admittedly still a novice in VBA.

When I do it manually, via right-clicking the column etc., it works fine. I am working with MS Office in the German language, but if the VBA code wanted German, I would think it would give me an error.

OK, now, I tried Peter's code, and it WORKED! I have no explanation for why the other didn't, but the result is now good.

Thank you both for the help.

I see how this for-next process is working, and I will add that and the other elements to my code knowledge base!

Joe

 

[VoG]

Perhaps you have other code that unhides the column on sheet activate or deactivate?

 

[Peter_SSs]

I have no explanation for why the other didn't, but the result is now good.

The other code didn't work because the code is in a particular sheet's module. Even though you Activate sheet 'Do', when you use

Columns("F:F").EntireColumn.Hidden = True

that refers to the sheet belonging to the module the code is in, not the Active sheet.

If you had done this, I believe it would have worked

Sheets("Do").Activate
ActiveSheet.Columns("F:F").EntireColumn.Hidden = True
Sheets("Re").Activate
ActiveSheet.Columns("F:F").EntireColumn.Hidden = True

However, I would still go with Peter's looping code that doesn't require each sheet to be activated to do the hiding/unhiding.