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

 

[John Caines]

Hi Fluff!!!

Jesus,, I didn't think to do this,,,
Just tried,,, it won't let me do it!!

Says Can't push objects off the sheet!

I'll google what this means,,,,
but I have the columns all hidden from AU,, I think I used CTRL + SHIFT + Left arrow,,, and selected Hide


Man,, what's going on here,,
I'll google.

many thanks again,, I just didn't think to try this

Best regards
John C

 

[JumboCactuar]

i looked at your sheet, random comment in one of the cells is the problem

delete comment in cell AP17 then use

Private Sub ToggleColumns_Click()
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
End Sub

if the comment is needed, simply add it with the toggle and remove when toggling again

like:

Private Sub ToggleColumns_Click()
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Range("AP17").AddComment
Range("AP17").Comment.Text Text:="test comment" & Chr(10) & "fgfg"


Else:
Range("AP17").ClearComments
Columns("AO:AU").EntireColumn.Hidden = True
End If
End Sub

 

[John Caines]

Many thanks for taking a look as well JumboCactuar,,

I have done what you said,,
just 1,, sorry 2 questions though.

Under modules I have (see screenshot please,

https://www.dropbox.com/s/4yshfogkvg8e747/module.jpg?dl=0

It says;

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

But does it need to say;

Private Sub ToggleColumns_Click()
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
End Sub

Not sure if the word Private makes a difference.

And 2nd question,,, can I not hide columns with comments in?
I really had the sheet formatted so 1 column was going to hold comments,, but I wanted to toggle this on and off (Hide/unhide)

I thought surely excel would allow you to hide columns with comments???
Maybe not...


Many thanks everyone for your help,, it's now working again!

What a mare!...

Yours sincerely
JohnC

https://www.dropbox.com/s/jupkycmis4wsvcl/module-png.png?dl=0
(Dropbox doesn't allow previews of jpeg,, so link is png

 

[JumboCactuar]

You cannot hide cells with comments, that's why it errors with "can't push objects off sheet"

Try the 2nd code I posted, should work fine just edit the comments text.

Private keyword just hides it from other modules (and the macro menu)

 

[John Caines]

Many thanks again JumboCactuar,

Yes,, I'm just reading about it here;

https://support.office.com/en-us/ar...in-excel-559f37da-2b7f-4548-a58d-96669f5310d6

I might have to reformat my sheet again!! ,,, oh what fun!! hahaha,,

Been on it all day.

Need to get off PC now,, eyes are burning up!

Many thanks to everyone here for all your help.

Very greatly appreciated,, really,, couldn't have gotr this sheet to where it is now without peoples help here.

Have a great evening everyone.

Best regards
A very grateful
John C

 

[JLGWhiz]

disregard, inadvertant post.

 

[JLGWhiz]

Many thanks for taking a look as well JumboCactuar,,

I have done what you said,,
just 1,, sorry 2 questions though.

Under modules I have (see screenshot please,

https://www.dropbox.com/s/4yshfogkvg8e747/module.jpg?dl=0

It says;

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

But does it need to say;

Private Sub ToggleColumns_Click()
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
End Sub

Not sure if the word Private makes a difference.

And 2nd question,,, can I not hide columns with comments in?
I really had the sheet formatted so 1 column was going to hold comments,, but I wanted to toggle this on and off (Hide/unhide)

I thought surely excel would allow you to hide columns with comments???
Maybe not...


Many thanks everyone for your help,, it's now working again!

What a mare!...

Yours sincerely
JohnC

https://www.dropbox.com/s/jupkycmis4wsvcl/module-png.png?dl=0
(Dropbox doesn't allow previews of jpeg,, so link is png

If you put a Form Controls button on a sheet, then the '_Click' is omitted from the title and the code for the button would go into a standard code module (one of the numbered modules, not sheet, thisworkbook nor userform.
If you put an Active-X button on a sheet then the '_Click' is required and the code goes into the worksheet code module of the sheet where the button is used.
It all has to do with how Excel handles different controls and where it looks for code based on the type of control.

 

[John Caines]

Many thanks for this info JLGWhiz,
Interesting actually,, I was wondering why some went to wrokbook and others are modules,,,,


===========
I do have 1 query though.
I've just tried to protect my sheet and when I do the modules, VBA won't work!

I think I need the codes to kind of say;
"Hey, if this sheet (Or certain cells) are protected I'll unprotect it, do what I have to do, then re-protect it."

The codes I have work great now, but not when protected and are the following;

Inserts formatted 'ticks' and 'crosses' This is the (workbook VBA)

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
   If Sh.Name = "READ ME" Then Exit Sub
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("H4:AL15,H17:AL20,H24:Q31")) Is Nothing Then
      Cancel = True
      With Target
          .Font.Name = "Wingdings"
          .Font.Size = 12
          If .Value = "ü" Then
              .Value = " û "
              Target.Interior.Color = vbRed
              Target.Font.Color = vbWhite
          Else
              .Value = "ü"
              Target.Interior.Color = vbGreen
              Target.Font.Color = vbBlack
          End If
      End With
   End If
End Sub

A toggle rows on and off....(This is module 1)

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

This is Module 2

Sub ResetCells()
With Intersect(Selection, Range("H4:AL15,AP4:AS15,H17:AL20,AP17:AS20,H24:Q31,S24:AK31,AG32:AK31"))
      .Font.Name = "Ariel"
      .Interior.Color = xlNone
      .Font.Color = vbBlack
      .ClearContents
   End With
End Sub

A Toggle Columns on and off....(Module3)

Private Sub ToggleColumns_Click()
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
End Sub

That's all the codes I have for my sheet.
If anybody can tell me please what needs altering in these codes as I'm not sure.
I take it will be some lines to amend the above codes,,,, but maybe it's something else?
I'm really not sure.

Hope someone can help me out here;

Yours sincerely
John C

 

[John Caines]

I seem to have found some code that might do it,, but just having a few errors with my sheet at the moment,, trying toi sort this out 1st

I found 2 lines of code which are;

ActiveSheet.Unprotect ""

and

ActiveSheet.Protect ""

So from the above,, maybe the code.....

Private Sub ToggleColumns_Click()
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
End Sub

should now be....

Private Sub ToggleColumns_Click()
ActiveSheet.Unprotect ""
If Columns("AO:AU").EntireColumn.Hidden = True Then
Columns("AO:AU").EntireColumn.Hidden = False
Else: Columns("AO:AU").EntireColumn.Hidden = True
End If
ActiveSheet.Protect ""
End Sub

As soon as I get my sheet working again I'll be able to tell if it works!

Might do the trick in unprotecting it to run the code, then protecting it again after?

Many thanks
John C

 

[John Caines]

Just as a follow up,, got my sheet working again,,,
the 2 lines I used seem to work great!

Excellent!
Now I know how to unprotect a protected sheet to use VBA!!

Many thanks
John C