0

I had this sub in another spreadsheet where I could click a button to collapse and expand certain columns. I copied it into a new spreadsheet to use to collapse some rows but now I get the error "Sub or function not defined". It highlights Buttons

Sub HideDetails()
    Range("3:8").Select
    If Selection.EntireColumn.Hidden Then
        Selection.EntireColumn.Hidden = False
        Buttons("btnToggleDetails").Caption = "-"
    Else
        Selection.EntireColumn.Hidden = True
        Buttons("btnToggleDetails").Caption = "+"
        Range("A1").Select
        Application.CutCopyMode = False
    End If
    Range("A1").Select
    Application.CutCopyMode = False
End Sub

There are no other scripts in this workbook. This one was originally in Module1 but I tried moving it to a new module.

enter image description here

Edit: I changed the button name in the code but not the screenshot. Both references are to btnToggleDetails now but it still throws the same error.

Improve this question

2 Answers 2

Reset to default
1

It's telling you that the identifier Buttons() can't be found in the current scope. If Buttons() is something that you've declared somewhere else, you either need to make it public or you need to fully qualify the object that contains the Buttons() object, for example:

  Sheet1.Buttons("btnToggleDetails").Caption = "+"
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Well that fixed the error but since I still had "EntireColumn" references my entire workbook is hidden and I can't get it back! lol
superuser.com/questions/1060182/…
On the Home tab of the ribbon, go to the Cells group and select the Format button. From there, choose Hide and Unhide and then choose Unhide Columns. In the future, if you want to hide all the columns, you are better off just hiding the worksheet.
I prepended Buttons with ActiveSheet. and it resolved the issue. I was also able to fix the screwup, thanks!
Think of the different sheets and modules as rooms...You can always see what's in the room you are in, but if you are in room a and you need something from room b, you have to start with "b", otherwise Excel thinks you are talking about the current room you are in.
1

Had to add my answer as was sure I could shorten the lines of code:

If you consider that Selection.EntireColumn.Hidden returns TRUE/FALSE or 0/-1.
CHR(45) is a minus sign.
CHR(43) is a plus sign.

ABS turns -1 into 1.

So: If TRUE (0) then 45-(0*2) = 45
If FALSE (-1) then 45-(1*2) = 43

This will swap the columns from hidden to visible and vice-versa and display the correct button caption in the immediate window: 

Sub HideShowColumns()

    Selection.EntireColumn.Hidden = Not (Selection.EntireColumn.Hidden)
    Debug.Print Chr(45 - (Abs(CLng(Selection.EntireColumn.Hidden)) * 2))

End Sub

This should work in your procedure: 

Sub HideDetails()

    Dim rng As Range
    Set rng = ActiveSheet.Range("3:8")

    rng.EntireColumn.Hidden = Not (rng.EntireColumn.Hidden)
    Buttons("btnToggleDetails").Caption = Chr(45 - (Abs(CLng(rng.EntireColumn.Hidden)) * 2))

End Sub
Improve this answer

1 Comment

This doesn't fix the issue (which was to prepend Buttons with ActiveSheet.) but it's a heck of an improvement on the code!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.