hide a column macro - with a twist

[John Caines]

Hello All.
I found a bit of code here to hide /toggle some rows which was;

Sub ToggleRows()
Rows("23:37").Select
 If Selection.EntireRow.Hidden = True Then
 Selection.EntireRow.Hidden = False
 Else: Selection.EntireRow.Hidden = True
 End If
End Sub

Great!! Works fine,,,,so, on the same sheet I want to also toggle some columns at the end (Right hand side) of my worksheet.
So, I think's to myself,,, let's just replace the words 'Row' With 'Column' from the code above.

So I've tried;

Sub ToggleColumns()
Columns("AN:AT").Select
 If Selection.EntireColumn.Hidden = True Then
 Selection.EntireColumn.Hidden = False
 Else: Selection.EntireColumn.Hidden = True
 End If
End Sub

Problem,,I get a Run-time error '1004'....???
Unable to set Hidden property of the Range clas
?? Not a clue what this means.
\The line that is highlighted yellow on the debug is

: Selection.EntireColumn.Hidden = True

As a note,, the bottom of my sheet from Row 38 I have hidden (Don't want to show all cells below this row as none are being used). Also I have hidden the columns from Column AV onwards, as I don't want to see any columns after AU.
So not sure if this has something to do with it/ sub not working??

Can anybody help me out here? I'm sure it's something quite easy, but it's only easy when you know how!

I've tried googling,,, what a mess,,, sent all over the net, not really sure what I'm looking at as everyone has slightly different problems.
Hope someone can help

Many thanks
JohnC

 

[Rick Rothstein]

I am not sure why the code you posted is not working for you as it works fine for me. In any event, here is a simpler alternative macro that you can try... see if it works for you.

[table="width: 500"]
[tr]
	[td]Sub ToggleColumns()
  Columns("AN:AT").EntireColumn.Hidden = Not Columns("AN:AT").EntireColumn.Hidden
End Sub[/td]
[/tr]
[/table]

 

[John Caines]

Hi there Rick,,
Many thanks for your reply.

Sorry,, but it's just not working for me
I really don't know what to do.
I'm not great in excel,, maybe it's something simple I'm doing wrong,, but I have another macro button working fine.
I'm not sure if I can PM you my sheet?
Not sure if this is allowed, I'll have to read the forum rules,,, but it's just a std excel 2016 workbook.

Been on it all day, formatting it,,
It's so close to being finished,, I've just a couple of small issues with buttons!
Let me know if it's ok Rick and I'll buzz this over (I have dropbox).

Many thanks for your time.
As a note;
I still get this line highlighted yellow;

Columns("AN:AT").EntireColumn.Hidden = Not Columns("AN:AT").EntireColumn.Hidden

A very frustrated
John C

 

[John Caines]

Actually Rick,
Here is my workbook
https://www.dropbox.com/s/gfst3r021s5mv9v/Mr-Excel.zip?dl=0

If some of the fonts look wrong,, it's because I used a custom font call "throw your hands up in the air" by Kimberly Geswein
https://www.dafont.com/throw-my-hands-up-in-the-air.font

Anyway, the sheet (JAN) that will open,, it has 3 buttons.
1. the one that is working is the 'Toggle Below' Button.
2. The 'Toggle Tips' button just ain't having it! I just want to toggle columns AN:AT on and off . Really not sure what's wroong here.
3. There is a smilely face button that did work when sheet was formatted completely differently. But now this won't work,, and I can't see why.
So, it's just 2 issues,, but I really don't know what's happening here, why 2 buttons don't work.

Any help here would be greatly appreciated.

As a note, the Font is a really good hand written looking font,, might be useful for someone.

Many thanks in advance.
But God knows why this ain't working

Yours sincerely
John C

 

[John Caines]

Sorry,,
The above sheet,,
I've tried to edit the post /text as I've made a false statement,,, but I'm too late for editing it,, so I'll have to contact an admin,,,
In the meantime,, please ..
The only button NOT working in the workbook above is the 'Toggle Tips' Button that is trying to toggle coloumns AN:AT to hide and unhide.

##The Smilely face button works fine now,, it was my fault, I had duplicated some cell references in the code when I reformatted the sheet, and thus thought it wasn't working.
Sorry for all this confusion.
I'm getting tired I think!

So it's just the 1 button (VBA) that isn't working,
God knows why.

Many thanks again
Yours sincerely
John C

 

[JumboCactuar]

Unable to set Hidden property of the Range class

Usually get this if the sheet is protected, if needed unprotect with VBA then protect again after toggle

 

[John Caines]

Thanks for your reply JumboCactuar

No,, I haven't passwork protected the workbook or sheets?

( I have seen I have made some error with grouping some ranges,, I didn't even realise I'd done this,,,
But I've just removed all these,,

Still the same error with this 1 button to toggle the coulumns on and off

I'll replace the zip file with the updated workbook that's got the groupings removed,, (But this still hasn't altered my problem)

Many thanks for your reply

John C

 

[JLGWhiz]

Sorry,,
The above sheet,,
I've tried to edit the post /text as I've made a false statement,,, but I'm too late for editing it,, so I'll have to contact an admin,,,
In the meantime,, please ..
The only button NOT working in the workbook above is the 'Toggle Tips' Button that is trying to toggle coloumns AN:AT to hide and unhide.

##The Smilely face button works fine now,, it was my fault, I had duplicated some cell references in the code when I reformatted the sheet, and thus thought it wasn't working.
Sorry for all this confusion.
I'm getting tired I think!

So it's just the 1 button (VBA) that isn't working,
God knows why.

Many thanks again
Yours sincerely
John C

Check under the 'Review' tab on the ribbon to see if you have the sheet protected. It will throw a 1004 error if you do. Otherwise, I couldn't get the error with the sheet unprotected.

 

[John Caines]

Thanks for jumping in JLGWhiz,,
Nope... it's not protected.

Here's an image
https://www.dropbox.com/s/z0lgpo6swrg5r2e/not-protected.jpg?dl=0

???
man,, this is so annoying,,,

Stuck here,, really not a scoobies!

Many thanks
John C

I did upload workbook in excel 2016 in the above dropbox link,
But I think you need to have a dropbox account to download it?
I'm not sure,,
But it's there if it helps

Thanks again
John C

 

[Fluff]

Try hiding the columns manually, what happens?