Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation starts change event
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? -- Thanks - K |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation starts change event
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. -- Thanks - K "Kristen" wrote: 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? -- Thanks - K |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation starts change event
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation starts change event
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation starts change event
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! -- Thanks - K "Incidental" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation starts change event
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 -- Thanks - K "Kristen" wrote: 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! -- Thanks - K "Incidental" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation starts change event
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet change event for data validation?? | Excel Programming | |||
In what version was the change event on a data validation list fixed? | Excel Programming | |||
Data Validation Listbox and the Worksheet Change Event | Excel Programming | |||
Change event for data validation listbox | Excel Programming | |||
Data Validation & ControlSource & Change event | Excel Programming |