VBA Code to Reset Multiple DropDown Lists

waterstone

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi, I have been reading through some of the other posts on creating a reset button for drop down lists and have not had any luck getting it to work. I could really use some help.

I have a worksheet that has 7 rows and 2 columns with dependent dropdowns with 45 different options. I am trying to create a "Reset Button" using a Command Button Active X Control.

I am good at excel but not familiar with VBA. I have had luck recording macros but in this case the worksheet has many dependent dropdowns so it would take a VERY long time to record all of the options.

Seems like it should be pretty simple, but no such luck.

the code Im using is

Private Sub CommandButton3_Reset()

Range("B10:C15").Value = “Please Select”

End Sub

Ive tried variations with functions "Reset_Dropdowns()" and "ResetDataVal()" all with the same range and value but not luck.

Any ideas would be appreciated.

Thanks in advance.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,263
Office Version
  1. 2010
Platform
  1. Windows
hi and welcome to MrExcel.
just try this and see how you go
VBA Code:
Sub CommandButton3_click()
    Dim OleObj As OLEObject
    
    For Each OleObj In ActiveSheet.OLEObjects
        If OleObj.progID = "Forms.ListBox.1" Then
            OleObj.Object.ListIndex = 0 ' or it might be -1
        End If
    Next OleObj
End Sub
 

waterstone

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thank you Diddi! Much appreciated for the quick response.

For some reason it still doesnt work. When I push the putton nothing happens. No "yellow" error in the code shows up as it had done in the past.

Does there need to be a range and value that it is targeting?

For example in this case there are 7 rows and 2 columns and it needs to revert to the top value or title.

Range("B10:C15").Value = “Please Select”

Thank you for your help
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,263
Office Version
  1. 2010
Platform
  1. Windows
i made an error. change this line and try again
VBA Code:
        If OleObj.progID = "Forms.ComboBox.1" Then
 

waterstone

New Member
Joined
Feb 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Diddi, Thanks for helping. Still no luck. Not sure why its not working. The code is below. I think Ive got it the same as you have listed. On a side note, I keep indenting it but when it posts it seems to remove the indents. How are you doing it?

VBA Code:
Sub CommandButton1_click()
    Dim OleObj As OLEObject
   
    For Each OleObj In ActiveSheet.OLEObjects
        If OleObj.progID = "Forms.ComboBox.1" Then
            OleObj.Object.ListIndex = 0 ' or it might be -1
        End If
    Next OleObj
End Sub


Is there a reason that we arent using a function like Reset_Dropdowns() or ResetDataVal() or Reset()? Most of the posts I was reading did it like that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Have a look here at how to post your code How to Post Your VBA Code
I have added the code tags for you & the indent is now there.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,263
Office Version
  1. 2010
Platform
  1. Windows
can you upload your sheet? i think there is something in the translation between what you believe is the task and i believe is the potential solution :) you can use dropbox for example to share a link to the uploaded file. we can get it sorted much quicker if i can see why it does not behave
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,263
Office Version
  1. 2010
Platform
  1. Windows
re the code tags, i do it like this:

VBA Code:
Sub Sample()
End Sub

Select the code and click
1614408133533.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,000
Messages
5,622,120
Members
415,878
Latest member
jjj12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top