Data Validation starts change event

[Kristen]

Hi - I currently have a change event for a column (D) with rows less than
500. I also use a data validation in that column for the end user to select
form a drop down list. Depending on their choice, more rows are inserted
below the drop down. I would like the end user to be able to manually add to
column D if they desire without the hcnage event starting.

Is there any way to restrict a change event to only occur when a choice is
selected from a data validation drop down list?

 

[Kristen]

I've thought this through and have been checkng the message boards some more.
I think if I could check the value of the cell that was changed and validate
if it is on the list (another worksheet in same workbook) and if it is launch
the macro. I am now having problems detrmining how to write if a cell equals
one of the values of another range of cells then launch the sub. If anyone
could help, I would appreciate it.

 

[Incidental]

Hi Kristen

The code below would be one way of doing what you want. It will take
the value entered in the changed cell and then search for that value
in a given range on sheet3, if the value is found it will show a
msgbox (replace this code with a call to your own macro) if not it
won't do anything.

Paste the code below into the module for the worksheet you are working
on.

Option Explicit
Dim FoundCell, MyRng As Range

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyRng = Worksheets("Sheet3").[A1:A20]

Set FoundCell = Worksheets("Sheet3").Cells.Find _
(What:=Target.Value, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then

MsgBox Target.Value & " was found in the list on sheet3"

End If

End Sub

Hope this helps

Steve

 

[Incidental]

Hi Again Kristen

Sorry there is a reference to a range in the previous code that is not
required, i forgot to remove it before posting... the code below is
correct...

Option Explicit
Dim FoundCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)

Set FoundCell = Worksheets("Sheet3").Cells.Find _
(What:=Target.Value, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then

MsgBox Target.Value & " was found in the list on sheet3"

End If

End Sub

Steve

 

[Kristen]

Thanks for all of your help. I just tried it on a sample and it looks like
it is exactly what I wanted. I have bought a couple of books, but there is
still alot that the books don't include and I have been tryiing to figure out.

You have been a huge help!

 

[Kristen]

OK. It worked and now it does not. I don't know what I did. I'm pasting
below the code that is in my worksheet, not module.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim foundCell As Range

Set foundCell = Worksheets("All Items").Cells.Find _
(What:=Target.Value, LookAt:=xlWhole)

If Not foundCell Is Nothing Then
MsgBox Target.Value


End If


End Sub

 

[Incidental]

Hi Kristen

I have looked at the code and it still works fine the only thing i can
think of is if you have the code in the right place? This code should
be in the module for the worksheet that you will be using i.e. if you
are running the code from sheet1 the code will need to be in the
module for sheet1. and if you are checking the exact sheet (i.e. "All
Items") make sure the name is spelt exactly as it is in your find
code.

Steve