Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validation
I have a CT project due this coming up friday. It this quiz thing, no matter.
The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validation
There many events to trigger a macro or event code.
Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validat
I am a bit of a noob when it comes to excel, i know where everything is, but
please don't use abbreviations. And can you explain the this I must do, instead of just naming them. At any rate i am happy to see that you replied so quickly. sincerly, niki "Gord Dibben" wrote: There many events to trigger a macro or event code. Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validat
Niki
You must first explain what you want to do before I can explain how to do it. I mentioned this in my first resonse, but apparently you missed that part. Post the code from the macro that is "not working" and and some detail on the results you expect. Gord On Sat, 19 May 2007 11:17:05 -0700, Niki wrote: I am a bit of a noob when it comes to excel, i know where everything is, but please don't use abbreviations. And can you explain the this I must do, instead of just naming them. At any rate i am happy to see that you replied so quickly. sincerly, niki "Gord Dibben" wrote: There many events to trigger a macro or event code. Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validat
Sorry I miss understood.
The code: Sub Macro1() ' ' Macro1 Macro ' ' Range("A103").Select ActiveWindow.SmallScroll Down:=-7 Range("A94").Select ActiveWindow.SmallScroll Down:=-9 Range("A85").Select ActiveWindow.SmallScroll Down:=-9 Range("A73").Select ActiveWindow.SmallScroll Down:=-26 Range("A51").Select ActiveWindow.SmallScroll Down:=-9 Range("D41").Select ActiveWindow.SmallScroll Down:=-21 Range("F14").Select ActiveWindow.SmallScroll Down:=-10 Range("A7:M8").Select End Sub Now thing I am trying to do is to not to change the data in the lists but to change what is selected in the drop-down lists. But it never seems to work. And just as some interesting fact, the code above is missing one entry, cell E112 (Bonus Question), I don't know why exactlly, but i think it has to do with the fact that I am using conditional formating on cells E108 - E114 to G108 - G114, to hide the data until a certain score is met. the code above also does not contain A112 (Question 10), and for this I have no clue. sincerly, niki "Gord Dibben" wrote: Niki You must first explain what you want to do before I can explain how to do it. I mentioned this in my first resonse, but apparently you missed that part. Post the code from the macro that is "not working" and and some detail on the results you expect. Gord On Sat, 19 May 2007 11:17:05 -0700, Niki wrote: I am a bit of a noob when it comes to excel, i know where everything is, but please don't use abbreviations. And can you explain the this I must do, instead of just naming them. At any rate i am happy to see that you replied so quickly. sincerly, niki "Gord Dibben" wrote: There many events to trigger a macro or event code. Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validat
Still have no idea what you need.
The code you posted just goes through and selects a series of cells, does nothing with them then ends with A7:M8 selected. Hopefully someone can figure out your needs. I am at a loss based upon your description and posted code. Where are the dropdowns and lists that need changing from "The Answer" to "Please Select" located? Gord On Sat, 19 May 2007 23:39:01 -0700, Niki wrote: Sorry I miss understood. The code: Sub Macro1() ' ' Macro1 Macro ' ' Range("A103").Select ActiveWindow.SmallScroll Down:=-7 Range("A94").Select ActiveWindow.SmallScroll Down:=-9 Range("A85").Select ActiveWindow.SmallScroll Down:=-9 Range("A73").Select ActiveWindow.SmallScroll Down:=-26 Range("A51").Select ActiveWindow.SmallScroll Down:=-9 Range("D41").Select ActiveWindow.SmallScroll Down:=-21 Range("F14").Select ActiveWindow.SmallScroll Down:=-10 Range("A7:M8").Select End Sub Now thing I am trying to do is to not to change the data in the lists but to change what is selected in the drop-down lists. But it never seems to work. And just as some interesting fact, the code above is missing one entry, cell E112 (Bonus Question), I don't know why exactlly, but i think it has to do with the fact that I am using conditional formating on cells E108 - E114 to G108 - G114, to hide the data until a certain score is met. the code above also does not contain A112 (Question 10), and for this I have no clue. sincerly, niki "Gord Dibben" wrote: Niki You must first explain what you want to do before I can explain how to do it. I mentioned this in my first resonse, but apparently you missed that part. Post the code from the macro that is "not working" and and some detail on the results you expect. Gord On Sat, 19 May 2007 11:17:05 -0700, Niki wrote: I am a bit of a noob when it comes to excel, i know where everything is, but please don't use abbreviations. And can you explain the this I must do, instead of just naming them. At any rate i am happy to see that you replied so quickly. sincerly, niki "Gord Dibben" wrote: There many events to trigger a macro or event code. Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validat
I see, well then I think I will need to actually show it to you, hopefully
when you see it, you'll understand. http://rapidshare.com/files/32476827/quiz.xls the file is safe, with only one macro. It was made in office 2007. When you see it, there will be cells where one can select a option from a drop-down list. My goal is to change all the selected options in all the cells countaining the drop-down lists back to "Please Select". sincerly, niki P.s. If you can't download it, please replie, I'll upload an image. "Gord Dibben" wrote: Still have no idea what you need. The code you posted just goes through and selects a series of cells, does nothing with them then ends with A7:M8 selected. Hopefully someone can figure out your needs. I am at a loss based upon your description and posted code. Where are the dropdowns and lists that need changing from "The Answer" to "Please Select" located? Gord On Sat, 19 May 2007 23:39:01 -0700, Niki wrote: Sorry I miss understood. The code: Sub Macro1() ' ' Macro1 Macro ' ' Range("A103").Select ActiveWindow.SmallScroll Down:=-7 Range("A94").Select ActiveWindow.SmallScroll Down:=-9 Range("A85").Select ActiveWindow.SmallScroll Down:=-9 Range("A73").Select ActiveWindow.SmallScroll Down:=-26 Range("A51").Select ActiveWindow.SmallScroll Down:=-9 Range("D41").Select ActiveWindow.SmallScroll Down:=-21 Range("F14").Select ActiveWindow.SmallScroll Down:=-10 Range("A7:M8").Select End Sub Now thing I am trying to do is to not to change the data in the lists but to change what is selected in the drop-down lists. But it never seems to work. And just as some interesting fact, the code above is missing one entry, cell E112 (Bonus Question), I don't know why exactlly, but i think it has to do with the fact that I am using conditional formating on cells E108 - E114 to G108 - G114, to hide the data until a certain score is met. the code above also does not contain A112 (Question 10), and for this I have no clue. sincerly, niki "Gord Dibben" wrote: Niki You must first explain what you want to do before I can explain how to do it. I mentioned this in my first resonse, but apparently you missed that part. Post the code from the macro that is "not working" and and some detail on the results you expect. Gord On Sat, 19 May 2007 11:17:05 -0700, Niki wrote: I am a bit of a noob when it comes to excel, i know where everything is, but please don't use abbreviations. And can you explain the this I must do, instead of just naming them. At any rate i am happy to see that you replied so quickly. sincerly, niki "Gord Dibben" wrote: There many events to trigger a macro or event code. Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validat
Hi Niki
Try Sub Macro1() ' Dim ps As String ps = "Please Select" Range("A103") = ps Range("A94") = ps Range("A85") = ps Range("A73") = ps Range("A51") = ps Range("D41") = ps Range("F14") = ps Range("F21") = ps Range("G42") = ps Range("E112") = ps End Sub Incidentally, I scored 10. -- Regards Roger Govier "Niki" wrote in message ... I see, well then I think I will need to actually show it to you, hopefully when you see it, you'll understand. http://rapidshare.com/files/32476827/quiz.xls the file is safe, with only one macro. It was made in office 2007. When you see it, there will be cells where one can select a option from a drop-down list. My goal is to change all the selected options in all the cells countaining the drop-down lists back to "Please Select". sincerly, niki P.s. If you can't download it, please replie, I'll upload an image. "Gord Dibben" wrote: Still have no idea what you need. The code you posted just goes through and selects a series of cells, does nothing with them then ends with A7:M8 selected. Hopefully someone can figure out your needs. I am at a loss based upon your description and posted code. Where are the dropdowns and lists that need changing from "The Answer" to "Please Select" located? Gord On Sat, 19 May 2007 23:39:01 -0700, Niki wrote: Sorry I miss understood. The code: Sub Macro1() ' ' Macro1 Macro ' ' Range("A103").Select ActiveWindow.SmallScroll Down:=-7 Range("A94").Select ActiveWindow.SmallScroll Down:=-9 Range("A85").Select ActiveWindow.SmallScroll Down:=-9 Range("A73").Select ActiveWindow.SmallScroll Down:=-26 Range("A51").Select ActiveWindow.SmallScroll Down:=-9 Range("D41").Select ActiveWindow.SmallScroll Down:=-21 Range("F14").Select ActiveWindow.SmallScroll Down:=-10 Range("A7:M8").Select End Sub Now thing I am trying to do is to not to change the data in the lists but to change what is selected in the drop-down lists. But it never seems to work. And just as some interesting fact, the code above is missing one entry, cell E112 (Bonus Question), I don't know why exactlly, but i think it has to do with the fact that I am using conditional formating on cells E108 - E114 to G108 - G114, to hide the data until a certain score is met. the code above also does not contain A112 (Question 10), and for this I have no clue. sincerly, niki "Gord Dibben" wrote: Niki You must first explain what you want to do before I can explain how to do it. I mentioned this in my first resonse, but apparently you missed that part. Post the code from the macro that is "not working" and and some detail on the results you expect. Gord On Sat, 19 May 2007 11:17:05 -0700, Niki wrote: I am a bit of a noob when it comes to excel, i know where everything is, but please don't use abbreviations. And can you explain the this I must do, instead of just naming them. At any rate i am happy to see that you replied so quickly. sincerly, niki "Gord Dibben" wrote: There many events to trigger a macro or event code. Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use a macro to change the selected item in data validat
Thanyou Very much, the code worked.
sincerly, niki "Roger Govier" wrote: Hi Niki Try Sub Macro1() ' Dim ps As String ps = "Please Select" Range("A103") = ps Range("A94") = ps Range("A85") = ps Range("A73") = ps Range("A51") = ps Range("D41") = ps Range("F14") = ps Range("F21") = ps Range("G42") = ps Range("E112") = ps End Sub Incidentally, I scored 10. -- Regards Roger Govier "Niki" wrote in message ... I see, well then I think I will need to actually show it to you, hopefully when you see it, you'll understand. http://rapidshare.com/files/32476827/quiz.xls the file is safe, with only one macro. It was made in office 2007. When you see it, there will be cells where one can select a option from a drop-down list. My goal is to change all the selected options in all the cells countaining the drop-down lists back to "Please Select". sincerly, niki P.s. If you can't download it, please replie, I'll upload an image. "Gord Dibben" wrote: Still have no idea what you need. The code you posted just goes through and selects a series of cells, does nothing with them then ends with A7:M8 selected. Hopefully someone can figure out your needs. I am at a loss based upon your description and posted code. Where are the dropdowns and lists that need changing from "The Answer" to "Please Select" located? Gord On Sat, 19 May 2007 23:39:01 -0700, Niki wrote: Sorry I miss understood. The code: Sub Macro1() ' ' Macro1 Macro ' ' Range("A103").Select ActiveWindow.SmallScroll Down:=-7 Range("A94").Select ActiveWindow.SmallScroll Down:=-9 Range("A85").Select ActiveWindow.SmallScroll Down:=-9 Range("A73").Select ActiveWindow.SmallScroll Down:=-26 Range("A51").Select ActiveWindow.SmallScroll Down:=-9 Range("D41").Select ActiveWindow.SmallScroll Down:=-21 Range("F14").Select ActiveWindow.SmallScroll Down:=-10 Range("A7:M8").Select End Sub Now thing I am trying to do is to not to change the data in the lists but to change what is selected in the drop-down lists. But it never seems to work. And just as some interesting fact, the code above is missing one entry, cell E112 (Bonus Question), I don't know why exactlly, but i think it has to do with the fact that I am using conditional formating on cells E108 - E114 to G108 - G114, to hide the data until a certain score is met. the code above also does not contain A112 (Question 10), and for this I have no clue. sincerly, niki "Gord Dibben" wrote: Niki You must first explain what you want to do before I can explain how to do it. I mentioned this in my first resonse, but apparently you missed that part. Post the code from the macro that is "not working" and and some detail on the results you expect. Gord On Sat, 19 May 2007 11:17:05 -0700, Niki wrote: I am a bit of a noob when it comes to excel, i know where everything is, but please don't use abbreviations. And can you explain the this I must do, instead of just naming them. At any rate i am happy to see that you replied so quickly. sincerly, niki "Gord Dibben" wrote: There many events to trigger a macro or event code. Change, selectionchange, doubleclick, etc. Right-click on your sheet tab and "View Code" Select Worksheet from the left-hand dialog and then scroll into the Declarations dialog to view. Selecting an item from a DV dropdown list will trigger a change event to fire the code. Let us know what you need doing and we can aid further. Gord Dibben MS Excel MVP On Sat, 19 May 2007 10:01:23 -0700, Niki wrote: I have a CT project due this coming up friday. It this quiz thing, no matter. The problem is that I am tring to make a macro that would, for example, change the list from "The Answer" to "Please Select". I don't know why it is not working, but from what I've learned, macros only work if there is an actual phisical change to the spread sheet, such as writing text, or change text. But a drop down list has no physical representation in that so named cell, i would have to change the data table (which are located at the bottom of my quiz sheet, hidden). So if there is a way to make this work, please send in your replies. And if not, then please suggest some other ways. This is really important, I could fail middle school if my grade got to low. Sincerly, Niki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to run when validation choice selected | Excel Discussion (Misc queries) | |||
Excluding VAT for selected item | Excel Worksheet Functions | |||
using data validation and inserting more than one item in a field | Excel Discussion (Misc queries) | |||
Help! Selecting item on data validation list switches windows | Excel Discussion (Misc queries) | |||
data validation list selected from a range | Excel Discussion (Misc queries) |