Excel Macro to reset validation Lists

jbaretta · 02-24-2008, 09:53 AM

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** · 02-24-2008, 10:25 AM

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 · 02-24-2008, 02:27 PM

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** · 02-24-2008, 02:39 PM

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 · 02-24-2008, 03:18 PM

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 · 04-21-2008, 12:40 PM

Quote:

Originally Posted by Glaswegian

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.

Hi,

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

**Glaswegian** · 04-21-2008, 12:52 PM

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 · 04-21-2008, 01:30 PM

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.

**Glaswegian** · 04-21-2008, 01:48 PM

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

Code:

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 · 04-21-2008, 07:34 PM

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** · 04-22-2008, 06:33 AM

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...

)

lsutton · 04-23-2008, 09:46 AM

Hi,

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

Have a great day at work!

**Glaswegian** · 04-23-2008, 01:03 PM

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** · 04-24-2008, 01:12 PM

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.

Code:

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.

02-24-2008, 09:53 AM	#1 (permalink)
jbaretta Registered User Join Date: Feb 2008 Location: California, USA Posts: 5 OS: XP Pro / Vista Bus My System	Excel Macro to reset validation Lists 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

02-24-2008, 10:25 AM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,389 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Macro to reset validation Lists 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

02-24-2008, 02:27 PM	#3 (permalink)
jbaretta Registered User Join Date: Feb 2008 Location: California, USA Posts: 5 OS: XP Pro / Vista Bus My System	Re: Excel Macro to reset validation Lists 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

02-24-2008, 02:39 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,389 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Macro to reset validation Lists 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

02-24-2008, 03:18 PM	#5 (permalink)
jbaretta Registered User Join Date: Feb 2008 Location: California, USA Posts: 5 OS: XP Pro / Vista Bus My System	Re: Excel Macro to reset validation Lists Hi Glaswegian, Perfect! Works just like you said....I got rid of my Macro button :-) Have a great day (night?) THANK YOU! John B

04-21-2008, 12:52 PM	#7 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,389 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Macro to reset validation Lists 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

04-21-2008, 01:30 PM	#8 (permalink)
lsutton Registered User Join Date: Apr 2008 Posts: 4 OS: xp	Re: Excel Macro to reset validation Lists 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.

04-21-2008, 01:48 PM	#9 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,389 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Macro to reset validation Lists Assuming that they all contain Data Validation dropdowns, your macro would look something like this Code: 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'? __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

04-21-2008, 07:34 PM	#10 (permalink)
lsutton Registered User Join Date: Apr 2008 Posts: 4 OS: xp	Re: Excel Macro to reset validation Lists 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!!!!!

04-22-2008, 06:33 AM	#11 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,389 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Macro to reset validation Lists 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...) __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

04-23-2008, 09:46 AM	#12 (permalink)
lsutton Registered User Join Date: Apr 2008 Posts: 4 OS: xp	Re: Excel Macro to reset validation Lists Hi, I have Excel 2003 Sp2. Hopefully, it will work! Have a great day at work!

04-23-2008, 01:03 PM	#13 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,389 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Macro to reset validation Lists 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

04-24-2008, 01:12 PM	#14 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,389 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Macro to reset validation Lists 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. Code: 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. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner