Excel macro reset button
Solved
Yvance77
Posted messages
273
Registration date
Status
Membre
Last intervention
-
Mike-31 Posted messages 18405 Registration date Status Contributeur Last intervention -
Mike-31 Posted messages 18405 Registration date Status Contributeur Last intervention -
Hello,
I am looking for help in creating a macro in Excel to reset various cells across different worksheets (10 in total).
These cells are not necessarily continuous or contiguous, either.
The wish, therefore, is that by clicking this button, they would magically reset to zero, as this workbook is updated daily.
The help would be especially welcome since I am not a Windows user; this is for my job.
One last point, I am using a British keyboard, so I apologize for any mistakes.
Best regards.
I am looking for help in creating a macro in Excel to reset various cells across different worksheets (10 in total).
These cells are not necessarily continuous or contiguous, either.
The wish, therefore, is that by clicking this button, they would magically reset to zero, as this workbook is updated daily.
The help would be especially welcome since I am not a Windows user; this is for my job.
One last point, I am using a British keyboard, so I apologize for any mistakes.
Best regards.
18 réponses
Hello,
can you write this macro that refers to sheets and cells in each sheet
Sub Macro1()
Sheets("Sheet1").Range("B4").ClearContents
Sheets("Sheet2").Range("D21,C7,D21").ClearContents
Sheets("Sheet3").Range("G17,D2:G2,G17").ClearContents
End Sub
Once you understand the process, it is possible to simplify or rather compact the formula
A+
________________________________________________________________________
You were looking for help. Forum members, voluntarily, were by your side.
Please let us know if your problem is resolved. In order to classify the
Request.
can you write this macro that refers to sheets and cells in each sheet
Sub Macro1()
Sheets("Sheet1").Range("B4").ClearContents
Sheets("Sheet2").Range("D21,C7,D21").ClearContents
Sheets("Sheet3").Range("G17,D2:G2,G17").ClearContents
End Sub
Once you understand the process, it is possible to simplify or rather compact the formula
A+
________________________________________________________________________
You were looking for help. Forum members, voluntarily, were by your side.
Please let us know if your problem is resolved. In order to classify the
Request.
Yes, you understood correctly, that's exactly it.
You have placed the macro correctly in the Visual Basic for Excel. You can also rename it, but do not change Sub and ().
For the macro button, there are two possibilities:
1/ Simply in the drawing toolbar, select the circle, or the square, or even a clipart, an image, place it on your Sheet 1 and right-click on it, then assign it to a macro by selecting your macro in the dialog box that appears. When you click on this object, the macro will activate.
2/ With the Visual Basic editor toolbar that you have displayed by going to View/Toolbars and checking Visual Basic, click on the control toolbox, and if you don’t know which is the button, hover the mouse pointer over it and read the tooltips.
In the control toolbox that opened, select a command button, double-click on it, and it will open a macro page with
Private Sub CommandButton1_Click()
In this space, enter the name of the macro, such as macro1.
End Sub
If you want to change the text on the button, still from this macro sheet, on the right you should have properties for CommandButton1, and below in the list you will find Caption, and opposite CommandButton1, that is what's on the button. Replace this text with the one you want to appear on the button.
Close the Visual Basic dialog boxes and you're done.
See you later!
If your problem is resolved or when it is, don't forget to mark your status as resolved at the top of your post. Thank you.
You have placed the macro correctly in the Visual Basic for Excel. You can also rename it, but do not change Sub and ().
For the macro button, there are two possibilities:
1/ Simply in the drawing toolbar, select the circle, or the square, or even a clipart, an image, place it on your Sheet 1 and right-click on it, then assign it to a macro by selecting your macro in the dialog box that appears. When you click on this object, the macro will activate.
2/ With the Visual Basic editor toolbar that you have displayed by going to View/Toolbars and checking Visual Basic, click on the control toolbox, and if you don’t know which is the button, hover the mouse pointer over it and read the tooltips.
In the control toolbox that opened, select a command button, double-click on it, and it will open a macro page with
Private Sub CommandButton1_Click()
In this space, enter the name of the macro, such as macro1.
End Sub
If you want to change the text on the button, still from this macro sheet, on the right you should have properties for CommandButton1, and below in the list you will find Caption, and opposite CommandButton1, that is what's on the button. Replace this text with the one you want to appear on the button.
Close the Visual Basic dialog boxes and you're done.
See you later!
If your problem is resolved or when it is, don't forget to mark your status as resolved at the top of your post. Thank you.
Hello,
Write your macro like this and it will be smooth
Sub ZERO()
Sheets("DAILY TILL").Select
Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheets("Reception1").Select
Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
See you
________________________________________________________________________
You were looking for help. The forum members, voluntarily, stood by your side.
Please be kind enough to let us know if your problem is resolved. To classify the
Request.
Write your macro like this and it will be smooth
Sub ZERO()
Sheets("DAILY TILL").Select
Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheets("Reception1").Select
Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
See you
________________________________________________________________________
You were looking for help. The forum members, voluntarily, stood by your side.
Please be kind enough to let us know if your problem is resolved. To classify the
Request.
Hi Mike
Back to business as usual. Here’s the start of my project
Sub ZERO()
Sheet1(DAILY TILL).Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheet2(Reception1).Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
But as soon as I want to apply or press the button, I get an error message worthy of the Nuls = syntax error
Thank you and see you soon
Back to business as usual. Here’s the start of my project
Sub ZERO()
Sheet1(DAILY TILL).Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheet2(Reception1).Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
But as soon as I want to apply or press the button, I get an error message worthy of the Nuls = syntax error
Thank you and see you soon
Hi,
Yes, of course, but you should have opened your own discussion with explanations about what you want to do in order to get a suitable response.
For this discussion, the code placed in a Visual Basic module and associated with a button was
On the sheet named DAILY TILL, you needed to clear the cells from B4 to B6, from B8 to B16, etc., and also on the sheet Reception1.
Sub ZERO()
Sheets("DAILY TILL").Select
Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheets("Reception1").Select
Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
--
Cheers
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Yes, of course, but you should have opened your own discussion with explanations about what you want to do in order to get a suitable response.
For this discussion, the code placed in a Visual Basic module and associated with a button was
On the sheet named DAILY TILL, you needed to clear the cells from B4 to B6, from B8 to B16, etc., and also on the sheet Reception1.
Sub ZERO()
Sheets("DAILY TILL").Select
Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheets("Reception1").Select
Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
--
Cheers
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Mike, a big thank you for your contribution that sheds some light on me (a lot, actually) :-)
Clarification: Sheets("Feuil1") Feuil1 should be changed to the name of my sheet, I suppose?
D2:G2 = in case I have consecutive cells
("B4") = case of an isolated cell
ClearContents = Function that allows cleaning up the mess
Then, how do I link this to a button that I want to put on the first page of the workbook and that I will name zero?
Thanks again.
Clarification: Sheets("Feuil1") Feuil1 should be changed to the name of my sheet, I suppose?
D2:G2 = in case I have consecutive cells
("B4") = case of an isolated cell
ClearContents = Function that allows cleaning up the mess
Then, how do I link this to a button that I want to put on the first page of the workbook and that I will name zero?
Thanks again.
Mike, you are a blessing :-)
Your explanations are clear and I will practice this Saturday; right now I'm taking a break for 48 hours. I will keep you updated and the others as well, as this could be useful for many people.
Best regards, Mike
Your explanations are clear and I will practice this Saturday; right now I'm taking a break for 48 hours. I will keep you updated and the others as well, as this could be useful for many people.
Best regards, Mike
Ok Mike, I can tell this is not going to be easy, so thank you for your patience.
Now I have the error message: Run time error "438" "Object doesn't support this property or method" when I click on the icon.
And I am prompted to debug.
For your information, here is the modified macro text:
Sub ZERO()
Sheet1("DAILY TILL").Select
Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheet2("Reception1").Select
Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
IMPORTANT POINT:
The part "Sheet1("DAILY TILL").Select" is highlighted in yellow???
Thanks for your quick response, Mike.
Talk to you later.
Now I have the error message: Run time error "438" "Object doesn't support this property or method" when I click on the icon.
And I am prompted to debug.
For your information, here is the modified macro text:
Sub ZERO()
Sheet1("DAILY TILL").Select
Range("B4:B6,B8:B16,C4:C6,C8:C16,C20,A22").ClearContents
Sheet2("Reception1").Select
Range("B7:B21,B37,C4,E7:E21,I7:I11,I19:I22").ClearContents
End Sub
IMPORTANT POINT:
The part "Sheet1("DAILY TILL").Select" is highlighted in yellow???
Thanks for your quick response, Mike.
Talk to you later.
Hi,
if your file doesn't contain any confidential data, please attach it using the address I see
https://www.cjoint.com/
Cheers!
if your file doesn't contain any confidential data, please attach it using the address I see
https://www.cjoint.com/
Cheers!
Thank you again, Mike.
I wasn't familiar with cjoint either. Super useful.
https://www.cjoint.com/?foqjX2Mruv
Looking forward to hearing from you soon.
Best regards
Yvance77
I wasn't familiar with cjoint either. Super useful.
https://www.cjoint.com/?foqjX2Mruv
Looking forward to hearing from you soon.
Best regards
Yvance77
Hi,
it's normal your sheets are protected.
I'm writing you a code including unprotection with the planned deletion and reprotection of your sheets, and I'll send it all back to you.
Talk to you later!
it's normal your sheets are protected.
I'm writing you a code including unprotection with the planned deletion and reprotection of your sheets, and I'll send it all back to you.
Talk to you later!
A big thank you Mike.
You had to know about the protection thing
I will post the result here anyway so that it can benefit as many people as possible
Best regards
Yvance77
You had to know about the protection thing
I will post the result here anyway so that it can benefit as many people as possible
Best regards
Yvance77
Hello,
you can retrieve your file at this link. In the macro code, after each apostrophe, you have the explanations of the macro.
The code for the button is in the code of the DAILY sheet; to access it, right-click on the sheet tab and view the code.
https://www.cjoint.com/?fouDx5Qcte
See you later
__________________________________________________________________
Need help. In the forum, volunteers take the time to decipher your sometimes confusing explanations and offer their knowledge.
Once a solution is found, please have the courtesy to mark your status as resolved. This way, your problem will be categorized and serve as a reference for other similar cases.
you can retrieve your file at this link. In the macro code, after each apostrophe, you have the explanations of the macro.
The code for the button is in the code of the DAILY sheet; to access it, right-click on the sheet tab and view the code.
https://www.cjoint.com/?fouDx5Qcte
See you later
__________________________________________________________________
Need help. In the forum, volunteers take the time to decipher your sometimes confusing explanations and offer their knowledge.
Once a solution is found, please have the courtesy to mark your status as resolved. This way, your problem will be categorized and serve as a reference for other similar cases.
Mike,
A BIG BIG THANK YOU.
I have some additional questions to ask you; I will do it later, I am in a bind.
But thanks again, know that your help was invaluable.
Respect
Yvance77
A BIG BIG THANK YOU.
I have some additional questions to ask you; I will do it later, I am in a bind.
But thanks again, know that your help was invaluable.
Respect
Yvance77
Re Mike,
Please clarify
1) I can't quite see how you created the button on the first page and the link with the macro
2) How do I delete an old macro as well?
Thank you for these final clarifications, then I will come back with the work you did to share it.
Best regards
Please clarify
1) I can't quite see how you created the button on the first page and the link with the macro
2) How do I delete an old macro as well?
Thank you for these final clarifications, then I will come back with the work you did to share it.
Best regards
Hi,
for the button, there are several tips
you can open the control toolbar, select your button, and double-click to access its VBA code, where you can copy the titles of your macros.
you can also draw your button from your drawing toolbar using a circle or a rectangle, or even an image that you insert, or you can export a button and right-click on it to assign a macro.
To delete a macro, display the Visual Basic toolbar, then in the center of that toolbar, click on the Visual Basic Editor icon which will open the editor page. display/project explorer, you need to find Modules and under it, Module1, Module2, etc... the macro codes are inside there.
Don't forget to mark your status as resolved once you've finished this discussion
See you!
for the button, there are several tips
you can open the control toolbar, select your button, and double-click to access its VBA code, where you can copy the titles of your macros.
you can also draw your button from your drawing toolbar using a circle or a rectangle, or even an image that you insert, or you can export a button and right-click on it to assign a macro.
To delete a macro, display the Visual Basic toolbar, then in the center of that toolbar, click on the Visual Basic Editor icon which will open the editor page. display/project explorer, you need to find Modules and under it, Module1, Module2, etc... the macro codes are inside there.
Don't forget to mark your status as resolved once you've finished this discussion
See you!