Recommend
 
 Thumb up
 Hide
8 Posts

BoardGameGeek» Forums » Everything Else » Chit Chat

Subject: quick Excel 2007 question rss

Your Tags: Add tags
Popular Tags: [View All]
Exit 191
United States
Buckeye
Arizona
flag msg tools
Look to the past and learn for the future.
badge
Let go Jets!
Avatar
mbmbmbmbmb
I am wondering if it is possible to have a "clear" button on a worksheet that will delete all contents in a 10 cell area. Does anyone know the coding for this?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Exit 191
United States
Buckeye
Arizona
flag msg tools
Look to the past and learn for the future.
badge
Let go Jets!
Avatar
mbmbmbmbmb
I should have said I need to clear A2 through B6.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Blorb Plorbst
United States
Bloomington
Indiana
flag msg tools
badge
I think we're all bozos on this bus.
Avatar
mbmbmb
public sub clearcells()

range("A2:B6").clearcontents

end sub


untested
3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Exit 191
United States
Buckeye
Arizona
flag msg tools
Look to the past and learn for the future.
badge
Let go Jets!
Avatar
mbmbmbmbmb
OK, How do I put this in?
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Blorb Plorbst
United States
Bloomington
Indiana
flag msg tools
badge
I think we're all bozos on this bus.
Avatar
mbmbmb
Easiest thing to do is to open up your "Forms" toolbar.

There is a button tool there (mouse hover for helptext if you have trouble finding it)

Select the button tool and draw a button on your sheet where you want it.

You'll be given an "Assign Macro" dialog.

Select the "Record..." option.

Give your macro a name or leave it as is. Click OK. You're now recording all actions you take in Excel.

Select the range you want to delete. and press the delete key.

You might have seen a "Stop Recording" toolbar appear when you started recording. You can click the stop button now and you're done. If no Stoop Recording bar appeared, go to Tools|Macro and choose stop recording.

Test it and save.


For added fun you can go into Tools|Macro|Visual Basic Editor and clean up the code so that it doesn't select the cells but just clears them (like my code above does)


PS: You've just taken your first step down the path to Macro Hell. Code clean up is the only way to find your way back. Never leave a recorded macro alone, it will breed.
4 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Exit 191
United States
Buckeye
Arizona
flag msg tools
Look to the past and learn for the future.
badge
Let go Jets!
Avatar
mbmbmbmbmb
Got it! Awesome and thanks!
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Andrew Simpson
United Kingdom
Leeds
West Yorkshire
flag msg tools
badge
Avatar
mbmbmbmbmb
I use a different trick for clearing cells when I'm designing a form.

I will colour the entry cells (pale green normally), then add a macro that clears all pale green cells. This way you don't need to update the macro every time you change the form, just add/remove the cells background colour and indicate the extent of the form with some tags.
Also mark the data cells as unlocked, then protect the form, so it stops people accidentally over-typing any formulae etc.

Example:
www.fredlawton.com/cleargreen.xls

It also means you can reuse the macro on any form within the workbook.

3 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Geeky McGeekface
United States
Manassas
Virginia
flag msg tools
designer
badge
It's time for baseball, people! Pitchers and catchers report soon and the national pastime is with us again!
Avatar
mbmbmbmbmb
While I'm sure the above answers will allow you to do what you requested, the correct response of course is that Excel 2007 is the Devil's own spawn and that all right-thinking people should stick to using Excel 2002 (like moi). That is all.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.