Toggle buttons have disappeared

[LukeScholtes]

Hey,

I'm putting together a form that involves using a lot of toggle buttons. In a nutshell there are 63 buttons that are each supposed to hide/unhide 1 of 63 rows that in turn contain six buttons. I wrote the VBA code for all of them, everything was working fine. Then when I reopened the file the next day none of the toggle buttons in the rows were appearing. The rows appeared fine but I couldn't see the buttons anywhere.
Excel did come up with a message that it was out of memory so I figure that has something to do with it, but I went into an earlier version of the spreadsheet that has far less code in it and the same thing happened.

Any help would be very greatly appreciated.

Luke

 

[Michael M]

What format did you save the file in ??
Are the rows where the toggle buttons reside, Hidden or reduced in size ??
And rather than have 63 buttons, why not have an InputBox that asks which rows to Unhide / Hide and use 1 Button ???

 

[LukeScholtes]

Thanks for getting back to me.
I saved it as .xlsm
I can't remember exactly what I did when I placed the buttons in the cells but it would have been some combination of selecting 'snap to', holding down Alt while moving them and selecting 'move and resize with cells'. While I was putting it together I would set the button above the row to false and it would hide the row and all of the buttons in that row, then they would come back if I set the button to true. When I closed Excel I had set all of the buttons to false. Then when I reopened Excel and set them back to true the rows appeared but the buttons in the row didn't. I still see traces of them like in the VBA code and in the selection pane.
Can you give me more info on how an input box works? I'm only about two days into doing this sort of thing with Excel so if you have a better idea for achieving the same outcome I'd be all ears.

Luke

 

[MARK858]

@LukeScholtes, Try running the code below and see if it helps / gives you another option (obviously follow the instruction in the inputbox).

You attach the code to a button if it works for you and you can obviously make another button to unhide the rows or use a toggle button etc.

Sub HideRws()
    Dim myRws, mySpl As String, iArr As Long
    
    mySpl = InputBox("Separate Row numbers with a  ,")
    myRws = Split(mySpl, ",")
    
    For iArr = LBound(myRws) To UBound(myRws)
        Rows(myRws(iArr)).Hidden = True
    
    Next iArr
End Sub

 

[LukeScholtes]

Hey Mark858,

Thanks for the tip. I tried it and it didn't do what I needed it to but that's more likely because I'm in over my head with this VBA stuff at the moment.
I'm thinking I might try using grouped options for now and see if that works any better.

Thanks anyway,

Luke

 

[Michael M]

Marks code is about as simple as it can get...Just type the first and last row number seperated by a comma and press OK
You then only need 1 button as I suggested in the earlier post

 

[MARK858]

Just type the first and last row number seperated by a comma and press OK

Actually the code in the previous post is designed to hide individual rows separated with a comma i.e. if you typed 3,8 then only rows 3 and 8 would be hidden or 3,8,11,15 would hide rows 3,8,11 and 15 only.

To hide from the first row to the end row then using similar syntax it would be something like the code below where 3,8 would hide rows 3 through 8.

Sub HideRws2()
    Dim myRws, mySpl As String, i As Long, x As Long, y As Long
    
    mySpl = InputBox("Separate startRow and endrow with a  ,")
    myRws = Split(mySpl, ",")

    i = myRws(LBound(myRws))
    x = myRws(UBound(myRws))

    For y = i To x
        Rows(y).Hidden = True
    Next y
End Sub

or simpler still in the code below 3:8 would hide rows 3 through 8.

Sub HideRows3()
    Dim myRws As String
    myRws = InputBox("Separate startRow and endrow with a  :")
    Rows(myRws).EntireRow.Hidden = True
End Sub

 

[LukeScholtes]

Thanks for all your help guys but I'm still having the problem with the buttons not reappearing. I read somewhere that it might be a bug in Excel. I'm going to rethink the design of the spreadsheet and start from scratch.