How to use a macro to change the selected item in data validation

[Guest]

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

 

[Gord Dibben]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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.

 

[Roger Govier]

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.

 

[Guest]

Thanyou Very much, the code worked.

sincerly,
niki