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

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

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

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

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

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

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

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

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