Excel macro - reset check boxes and drop down menu

  StuFromPoole 18:55 12 May 2006

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 2006

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 2006

"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.



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

  StuFromPoole 11:43 13 May 2006

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


  VoG II 11:51 13 May 2006

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
'... and finishes here.
Application.ScreenUpdating = True
Application.Cursor = xlNormal
End Sub

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

Elsewhere on IDG sites

Honor 9 Lite review

How Social Media has Propelled Political Graphic Design and Art in the Last Decade

The best kids apps for iPhone & iPad 2018

HomePod d’Apple : date de sortie, prix et fiche technique