Reset Button to reset values to default in Drop List (created in Form Control)

[saguilera]

Hi everybody, I got a worksheet in Microsoft excel 365 that contain , let's say 2 drop list. Those drop list were created using developer tab - insert List Box (Form Control). Now, I need to create a "RESET FORM" button that once is clicked, reset the fields of those 2 drop list to first value that are "- Select - ".
I have been trying to assign a few codes to this button ( reading a lot of post in this forum) but none of them seem to works in my case. Also, I noticed that in others posts, answers assign a drop list to a cell, but in my case, mine drop list are like floating in my sheet, and have a range of value... i.e. I found this code:
Sub ResetDataVal()
Sheets("Sheet1").Range("B5").Value = "- Select -"
End Sub

First, one of my drop list is on top of B5 but like I say, is like floating, I can move it to anywhere...
Second, I have assigned values from a range of cells (i.e $N$2:$N15$) and if I change B5 by my range... then when I click on button, all the range cells change to "- Select -"... and I don't want that

What I need: is a RESET button (already created but not working) to my form that reset all my drop list from "number values" choose by user to "- Select -" when they click on RESET button.
In other words, I don't have idea how to do it... please, help !

 

[BlueAure]

Hi Saquilera,

You can address a control that is floating on a sheet like this:

Sheets("Sheet1").ListBox1.Value = "- Select -"

 

[saguilera]

Thanks BlueAure... I did what you say but still not working, when I click RESET button nothing... none of the values selected change to the first row of drop list (which is - Select -), and shows "Run-time error '9' : Subscript out of range" ... don't know what's going on!!

 

[Fluff]

You can use something like

ActiveSheet.Shapes("[COLOR=#ff0000]List Box 1[/COLOR]").ControlFormat.ListIndex = 1

Change value in red to suit.

 

[saguilera]

Nope... doesn't work yet... Maybe I did something wrong from the very beginning creating those drop-list, or I really don't know what else could be. Anyway, thanks for your help. If I don't figure out soon, I'm going to start over with only 1 drop-list and see what happen. Thanks again...

 

[BlueAure]

Try mixing the two solutions.

Sheets("Sheet1").ListBox1.listindex = 0

Here is the MSDN for ListIndex:
https://docs.microsoft.com/en-us/office/vba/api/access.listbox.listindex

 

[Fluff]

@BlueAure
That code is for an ActiveX listbox, not a Forms Control listbox.

 

[Fluff]

Nope... doesn't work yet...

Did you get any error messages?

 

[BlueAure]

@BlueAure
That code is for an ActiveX listbox, not a Forms Control listbox.

Oops, misread.

 

[saguilera]

This is what shown:
https://www.dropbox.com/s/py5ai1olcafxgnt/error.jpg?dl=0