# Excel Macro to reset validation Lists



## jbaretta (Feb 15, 2008)

Hi,
I'm looking for ideas on how to create ( if possible :-\ ) a macro that could reset validation lists I have on a form to the blank cell located in the list.

The form is on worksheet 1 & has 8 dropdown validation lists.

The data Validation lists is on worksheet2 (same workbook).

The lists are validated on worksheet 1 using Names (= "listname"). 

Each list has a blank cell at the top of the alphabetized list. 

The validated lists on worksheet 1 are not crossing merged cells. 

I also have a macro that runs which gernerates a new sequentail "form number" in a specific cell which works fine as long as I "save" the form before closing......which is why I need to reset the dropdown list data.

The form is to be used on my tablet pc where I can use the stylus pen to pull down the data instead of write it.

I have been unable to record a macro to make this happen. This is my first attempt at creating a form/validation lists/macros.

Any assistance would be appreciated.
Thank You, John B


----------



## Glaswegian (Sep 16, 2005)

Hi John and welcome to TSF.

I'm assuming you want the validation list reset to blank (I also use a blank cell to start a validation list). 

Normally you just set the cell value to blank. So if your validation cell is B2 you would use

*Range("B2").Value = ""*

This could be added in to existing code or run separately.

Let me know if you need any more help with this.


----------



## jbaretta (Feb 15, 2008)

Hi Glaswegian,
You're code worked perfectly...I was able to use that code to "clear" all the data in my 8 lists by pushing a button with a Macro assigned to it. Thank You very much.

Is there a way to have it clear when the form is opened or closed? 

I do have to "save" the document when closing to allow the next sequential number to move forward on my form. 

Thank you for your time and knowledge.
John B


----------



## Glaswegian (Sep 16, 2005)

Hi John

Sure - you can use a Worksheet Event - you have a choice of Open or Before Close. When creating input forms on a spreadsheet, I usually reset values and hide menus etc when the workbook opens. In the VB Editor double click on ThisWorkbook and choose Workbook from the (General) dropdown. Workbook Open will be the default Event.


----------



## jbaretta (Feb 15, 2008)

Hi Glaswegian,
Perfect! Works just like you said....I got rid of my Macro button 
Have a great day (night?)
THANK YOU!
John B


----------



## lsutton (Apr 21, 2008)

Glaswegian said:


> Hi John and welcome to TSF.
> 
> I'm assuming you want the validation list reset to blank (I also use a blank cell to start a validation list).
> 
> ...


Hi,

Could anyone share the exact code to do this? I would like to do this very thing. Thanks in advance!


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome to TSF.

What do you mean by "the exact code"? To do what? You have to be a bit more specific otherwise we won't be able to help you.

BTW, please start a new thread the next time you have a query - thanks.


----------



## lsutton (Apr 21, 2008)

Okay, I'll be more specific. I am using Excel and have several columns in my worksheet. Each column has several rows containing drop down menus with up to 6 items to choose. I would like to use a "Reset Button" to clear all the data. I can make a button and I can make a macro...but I don't know how to do it all together. It sounds like the other post had a similar issue.

I don't know if it's possible, but I would like to add another feature as well. Once I click an item from the drop down menu it would be helpful to have the drop downs lists in that column to propogate the same item. This way the I won't have to click on each and every item. 

Thanks and duly noted on the new thread.

:4-dontkno


----------



## Glaswegian (Sep 16, 2005)

Assuming that they all contain Data Validation dropdowns, your macro would look something like this

```
Sub ResetAll()
Dim myRng As Range

Set myRng = Sheets("Sheet1").Range("A1:D4")

myRng.Value = ""

End Sub
```
You'll need to change the sheet name and the ranges to suit. You can add a button to your sheet and use it to run this macro.

If I understand you correctly, you want all these cells to have the same value when you click and select just one? What about the other cells? Will each cell value always be the same? Does it matter which cell is the 'master'?


----------



## lsutton (Apr 21, 2008)

Thanks, it worked great! I really appreciate it!!!

As far as the other question, let's discuss one column. Say cell A1 through A30 each have a drop down menu containing a list of 3 choices...say Apple, Orange, Lemon. I would like to click on A1 and choose Lemon and have Lemon populate the the cells below. This way each cell does not have to clicked individually which could be time consuming. If I need A15 to be a different choice then I would just change it. This will be an ordering form. Thanks!!!!!


----------



## Glaswegian (Sep 16, 2005)

Hi

What version of Excel are you using? I ask because you could not trap the cell change from Data Validation in older versions of Excel.

I'll get something back to you this evening (I'm at work just now and time is limited...:grin


----------



## lsutton (Apr 21, 2008)

Hi,

I have Excel 2003 Sp2. Hopefully, it will work! 

Have a great day at work!


----------



## Glaswegian (Sep 16, 2005)

That's fine.

I'll be offline for the next 24 hours or so, as we are having a new carpet fitted and this includes the PC room. I'll get something for you after that. Apologies but I didn't expect to be able to find a carper fitter so quickly.


----------



## Glaswegian (Sep 16, 2005)

Here you go.

This uses the Worksheet Change Event to capture the change to cell A1. This code goes in the Worksheet Module for whichever sheet you need to run this from.

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range

Set myRng = Range("A1:A30")

If Target.Address <> "$A$1" Then Exit Sub

myRng.Value = Target.Value

End Sub
```
Any problems please post back.


----------

