Clear Form and Reset Toggles on Multiple Sheets

Olivia Grant

New Member
Joined
Mar 11, 2015
Messages
5
I am trying to reset/clear multiple sheets in an excel document as well as resetting the toggle buttons to "True".
I can get the information to clear from all sheets and the toggles to reset on the active sheet but can't get the toggles from the 2 others sheets to reset. My clear button is on the "Paid" Sheet. I get Run-time error '1004': Select method of Range class failed. When I debug, it takes me to where I initially created my toggle and highlights ".Select":

Private Sub ToggleButton1_Click()
With Rows("11:11")
.Select
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub

Below is the macro attached to my clear form button and the lines giving me issues.

Private Sub CommandButton1_Click()
Sheets("Paid").Range("E10:E296").Value = ""
Sheets("Unpaid").Range("E10:E186").Value = ""
Sheets("Pending").Range("E10:E26").Value = ""
ActiveSheet.CommunityRelations.Value = True
ActiveSheet.Digital.Value = True
Sheets("Unpaid").ToggleButton1.Value= True

Sheets("Pending").ToggleButton1.Value= True

MsgBox "Form has been cleared."
End Sub



Thanks for any help you can give!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
you would have to tell excel where to find that row , so 'with rows("11:11")...select" becomes the below code. Also there is no need to select it.
Code:
With Sheets(name of sheet).Rows(11)
[I][SIZE=2][FONT=arial].EntireRow.Hidden = Not .EntireRow.Hidden[/FONT][/SIZE][/I]
[I][SIZE=2][FONT=arial]    End With[/FONT][/SIZE][/I]
[I][SIZE=2][FONT=arial]End Sub[/FONT][/SIZE][/I]

Now to do this with each sheet you would have to create a for each loop.
 
Upvote 0
thank you Dendro!
I was able to rewrite the code for the toggles and they now reset correctly.

Appreciate the help.
 
Upvote 0
When I email the spreadsheet to another computer the toggle buttons are no longer active. Any idea how to get them working again?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top