Macro help - reseting form with drop down lists and data cells

[meridith71]

Hi there,
I am new to macros and do not really know much so please bear with me and forgive me if I need to be "taught" in simple terms.

I have created some forms in Excel (by cutting and pasting them from an internal company website). The drop down lists copied nicely into Excel. It was not feasible to re-type the drop down lists since some have over 100 choices.

My boss has asked me to create a macro so that the form can be "reset" or "cleared" either by clicking a button or hitting "enter". Please note that some cells will have typed data and others will have drop down lists. I would prefer a button that when clicked resets the data. All of the drop downs and data fields are in Column B. For example B4:B58 are the cells that I will need reset.
I have multiple sheets in each workbook that will need to have this macro.
Sorry if I have given too much (or not enough) info. Any help will be appreciated! Thank you.
-Meridith

 

[Worf]

Hello and welcome to the Board

First thing we need to determine is the exact nature of your drop down lists, it's not clear for me, I wonder if they are ActiveX controls like ComboBoxes. So I ask you to activate the sheet where they are and to run the macro below, and report the result here.
This macro also shows how to clear an "ordinary" range of cells.
If you need more detailed guidance please ask.

Option Explicit


Sub Meri()
Dim obj As OLEObject, act As Worksheet, mylist$


Set act = ThisWorkbook.ActiveSheet
mylist = ""


For Each obj In act.OLEObjects
    
    mylist = mylist & obj.Name & vbNewLine
            
Next


MsgBox mylist, vbInformation, act.OLEObjects.Count & " objects found"
MsgBox "Clearing b15:b20..."
act.Range("b15:b20").ClearContents


End Sub