Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Clearing Cells With a Button

0

I have a workbook with 11 tabs, plus one at the beginning marked as "Start Here". I want to create one button in that tab that will clear up to 9 individual cells in each of these tabs. Is this possible and how is it done?

Answer
Discuss

Answers

0

In the attached workbook please look for the procedure ClearCells_Click in the standard code module ClearCells. There you will find the line of code
Const CellsToClear As String = "A3 B17 C5 D7 E8"
This is where you can specify the cells you want to clear. It can be any number of cells, and you can specify ranges also, like D4:E5. Just make sure that each cell or range address is separated from the previous by a single space and there are no extra spaces in the specification.

The code will run when you click the button on Sheet 1. Actually, the name of Sheet1 is immaterial, as are the names of all other sheets. The cellls specified will be cleared on all sheets except the one on which the button resides. However, if you run the code from the VB Editor the excepted sheet will be the one which is active at the time of calling the code, which may not be the one with the button.

Discuss

Discussion

Thanks for the help, but I want to confirm what I need to do with this code in order to make it function as advertised on my workbook? I entered all of the individual cells on each tab in a long string and that was all the changes I did. When I "Viewed" the my available Macros, I selected and and got a "Compile Error" message. Was there something else I should have done?
Stan Aug 23, '18 at 12:23 pm
A compile error occurs when there is a mistake in the code syntax. Since there is no such mistake in the workbook I posted, and since the only modification you did was to the cell addresses, the wrong syntax must be in that line. As a guess: did you enclose the list of cell addresses in quotation marks?
Variatus (rep: 4889) Aug 23, '18 at 7:56 pm
Hi Stan,
How did you make out? Did you get the code to run?
Variatus (rep: 4889) Aug 29, '18 at 8:33 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login