Excel macro - reset check boxes and drop down menu

  StuFromPoole 18:55 12 May 06
Locked

I am playing with an Excel spreadsheet that I created as a form for returning a concise report of complex information. The form need not be saved as a file so I would usually just print it, close it without saving and then re-open the blank form again. There has to be a better way.

So, I created a macro that clears data from pre-selected cells and returns the cursor to the top line cell, ready to start again. Now, the spreadsheet is a little complex and there are dozens of inputs including drop down menus and check boxes.

Whilst my macro clears data, what I would really like is for all the check boxes to be cleared and all the drop-down menu options to be reset to blank too. This is, for now, beyond me.

Anyone help?

With thanks - Stu.

  VoG II 19:11 12 May 06

Is there VBA code to read data from the check boxes and drop downs or are they simply linked to cells on the worksheet?

The chances of resolving this by a discussion on the forum are pretty remote. Can you blank your worksheet then click my yellow envelope to e-mail me. I'll reply and you'll be able to send the worksheet back to me as an attachment.

Basically I need to know the names of all of these objects to clear them. Also, if you made your macros using the macro recorder I can probably neaten them up.

  Simsy 01:15 13 May 06

"I would usually just print it, close it without saving and then re-open the blank form again"

the best way is probably to save the "empty" version as a template...

Then whe you want a new blank version you just go file>new> and choose the template.

Regards,

Simsy

I hope this meakes sense... I've just been out with the boys at the pub!

  StuFromPoole 11:43 13 May 06

Special thanks to VoG for re-writing the macro to do exactly what I needed!

Simsy, I haven't used the Template option before but will consider it for future worksheets.

Thanks guys

Stu.

  VoG II 11:51 13 May 06

For info here is the code:

Sub cleardata()
Dim drop As DropDown, check As CheckBox
Application.ScreenUpdating = False
Application.Cursor = xlWait
For Each drop In Sheets("form").DropDowns
drop.ListIndex = 0
Next drop
For Each check In Sheets("form").CheckBoxes
check.Value = False
Next check
'Original code starts here...
Range("E2:T2, Y2:AD2,e3:h3,o3:r3,e4:j4,e5:j5,o4:t4,o5:t5,y4:ad4,k6:ad6,x9:ad9,i10:ad10,g11:j11,q11:s11,y11:ad11,q13:ad13,i14:p14,i17:o17,o18:ad18,k21:k26,a39:ad50").Select
Selection.ClearContents
Range("E2").Select
'... and finishes here.
Application.ScreenUpdating = True
Application.Cursor = xlNormal
End Sub

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Fresh New Fonts of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced