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

saguilera

New Member
Joined
May 20, 2019
Messages
8
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 !
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi Saquilera,

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

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




 
Upvote 0
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!! :(
 
Upvote 0
You can use something like
Code:
ActiveSheet.Shapes("[COLOR=#ff0000]List Box 1[/COLOR]").ControlFormat.ListIndex = 1
Change value in red to suit.
 
Upvote 0
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...
 
Upvote 0
@BlueAure
That code is for an ActiveX listbox, not a Forms Control listbox.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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
Back
Top