VBA Code to Reset Multiple DropDown Lists

[waterstone]

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.

 

[diddi]

hi and welcome to MrExcel.
just try this and see how you go

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]

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]

i made an error. change this line and try again

        If OleObj.progID = "Forms.ComboBox.1" Then

 

[waterstone]

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?

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]

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.

 

[waterstone]

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.

Thank you! Will take a look.

 

[diddi]

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]

re the code tags, i do it like this:

Sub Sample()
End Sub

Select the code and click

 

[waterstone]

Hi Diddi, I understand the posting code now. Here is the dropbox link to the file. It is sheet 2 I need help with. I left a note in the sheet explaining what I am trying to do. Thanks for your help.

VTANR O_M Cost Matrix 20210227.working copy.xlsm

Shared with Dropbox

www.dropbox.com