In the VBA window for select the sheet where the change is being made

Here is the code:

Part 1

Private Sub Worksheet_Activate()
sheets("Sheet1").range("A1").value = ""
End Sub


Part 2
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
select case target.value
case "Dog"
target.offset(1,0).value = "Cat"
case "Cat"
target.offset(1,0).value = "Mouse"
case "Mouse"
target.offset(1,0).value = "Cheese"

End If
End Sub

You can get the list of events by choosing Worksheet Where is says general
in the VBA Window. The list of events is in the Drop down to the right of
General.

You should include option explicit at the beginning of your code to force
explicit varaible declartions.

HTH

"MaBell" wrote:

I want the drop down box to be empty before the user clicks on it. I don;t
want the box to display the last selection next time the workbook is opened.

Second question: I know access VBA but I can't seem to figure out how to
initiate any excel change events. For example. A user selects dogs from the
drop down list. I want the cells next to the drop down list to list the
names of dogs from worksheet2. Is that possible?


"Jim Thomlinson" wrote:

Refresh the drop downs with what? Where is the list you want in you drop
downs coming from?

Once you have the drop downs populated how many different alternatives could
there be in the drop downs. That is how many possible answers will the code
have to react to.

"MaBell" wrote:

Hi Guys,

I know nothing about excel macros so I am looking for good help. I want to
know how to refresh my drop down boxes (data validation, not combo boxes) in
a excel spreadsheet whenever the workbook is opened. ALso, I want to
populate 5 cells on the dropdown worksheet with data from another worksheet
depending on what i choose in my drop down box. Any help and examples would
be greatly appreciated. I am used to Access and I can't seem to figure out
how create events, functions, subs, etc.
--

Thanks in advance,
Matt
(access 2000)