| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation Question
I have set up Data Validation in cell Q10 that only allows text entered into
it if cell M10 is equal to "Offer Declined". I have noticed though if you put the status of M10 to "Offer Declined", then go to Q10, enter text, then change the status of M10 to something else, it still allows the text. I was wondering if there was something that I could do that would remove the text in Q10 if M10 changes to anything other than "Offer Declined". Any suggestions? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation Question
Alex,
You could use the worksheet change event: copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("$M$10")) Is Nothing Then Exit Sub If Range("$M$10").Value < "Offer Declined" Then Application.EnableEvents = False Range("Q10").ClearContents Application.EnableEvents = True End If End Sub "Alex" wrote in message ... I have set up Data Validation in cell Q10 that only allows text entered into it if cell M10 is equal to "Offer Declined". I have noticed though if you put the status of M10 to "Offer Declined", then go to Q10, enter text, then change the status of M10 to something else, it still allows the text. I was wondering if there was something that I could do that would remove the text in Q10 if M10 changes to anything other than "Offer Declined". Any suggestions? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation Question
Cheers for that, it does work. One question though, if I want to have it
eligable for all cells from M10:M209 and Q10:Q209 how is this done, as I have tried and it bought up an error message on the third line of your formula. "Bernie Deitrick" wrote: Alex, You could use the worksheet change event: copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("$M$10")) Is Nothing Then Exit Sub If Range("$M$10").Value < "Offer Declined" Then Application.EnableEvents = False Range("Q10").ClearContents Application.EnableEvents = True End If End Sub "Alex" wrote in message ... I have set up Data Validation in cell Q10 that only allows text entered into it if cell M10 is equal to "Offer Declined". I have noticed though if you put the status of M10 to "Offer Declined", then go to Q10, enter text, then change the status of M10 to something else, it still allows the text. I was wondering if there was something that I could do that would remove the text in Q10 if M10 changes to anything other than "Offer Declined". Any suggestions? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation Question
Alex,
Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("$M$10:$M$209")) Is Nothing Then Exit Sub For Each myCell In Intersect(Target, Range("$M$10:$M$209")) If myCell.Value < "Offer Declined" Then Application.EnableEvents = False myCell.Offset(0,4).ClearContents Application.EnableEvents = True End If Next myCell End Sub HTH, Bernie MS Excel MVP "Alex" wrote in message ... Cheers for that, it does work. One question though, if I want to have it eligable for all cells from M10:M209 and Q10:Q209 how is this done, as I have tried and it bought up an error message on the third line of your formula. "Bernie Deitrick" wrote: Alex, You could use the worksheet change event: copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("$M$10")) Is Nothing Then Exit Sub If Range("$M$10").Value < "Offer Declined" Then Application.EnableEvents = False Range("Q10").ClearContents Application.EnableEvents = True End If End Sub "Alex" wrote in message ... I have set up Data Validation in cell Q10 that only allows text entered into it if cell M10 is equal to "Offer Declined". I have noticed though if you put the status of M10 to "Offer Declined", then go to Q10, enter text, then change the status of M10 to something else, it still allows the text. I was wondering if there was something that I could do that would remove the text in Q10 if M10 changes to anything other than "Offer Declined". Any suggestions? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation Question
Excellent cheers for that.
To be even more annoying if for example I wanted only some text within a cell, ie. anything in Column C10:C209 which contains the text "full time", and using the same principal as below wanted it to remove anything in the cells O10:O209 if full time did not appear in Column C, how would i got about placing that in the formula? "Bernie Deitrick" wrote: Alex, Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("$M$10:$M$209")) Is Nothing Then Exit Sub For Each myCell In Intersect(Target, Range("$M$10:$M$209")) If myCell.Value < "Offer Declined" Then Application.EnableEvents = False myCell.Offset(0,4).ClearContents Application.EnableEvents = True End If Next myCell End Sub HTH, Bernie MS Excel MVP "Alex" wrote in message ... Cheers for that, it does work. One question though, if I want to have it eligable for all cells from M10:M209 and Q10:Q209 how is this done, as I have tried and it bought up an error message on the third line of your formula. "Bernie Deitrick" wrote: Alex, You could use the worksheet change event: copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("$M$10")) Is Nothing Then Exit Sub If Range("$M$10").Value < "Offer Declined" Then Application.EnableEvents = False Range("Q10").ClearContents Application.EnableEvents = True End If End Sub "Alex" wrote in message ... I have set up Data Validation in cell Q10 that only allows text entered into it if cell M10 is equal to "Offer Declined". I have noticed though if you put the status of M10 to "Offer Declined", then go to Q10, enter text, then change the status of M10 to something else, it still allows the text. I was wondering if there was something that I could do that would remove the text in Q10 if M10 changes to anything other than "Offer Declined". Any suggestions? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Data Validation Question
Alex,
Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("$M$10:$M$209")) Is Nothing Then Goto TryB For Each myCell In Intersect(Target, Range("$M$10:$M$209")) If myCell.Value < "Offer Declined" Then Application.EnableEvents = False myCell.Offset(0,4).ClearContents Application.EnableEvents = True End If Next myCell TryB: If Intersect(Target, Range("$C$10:$C$209")) Is Nothing Then Exit Sub For Each myCell In Intersect(Target, Range("$C$10:$C$209")) If myCell.Value < "Full time" Then Application.EnableEvents = False myCell.Offset(0,12).ClearContents Application.EnableEvents = True End If Next myCell End Sub -- HTH, Bernie MS Excel MVP "Alex" wrote in message ... Excellent cheers for that. To be even more annoying if for example I wanted only some text within a cell, ie. anything in Column C10:C209 which contains the text "full time", and using the same principal as below wanted it to remove anything in the cells O10:O209 if full time did not appear in Column C, how would i got about placing that in the formula? "Bernie Deitrick" wrote: Alex, Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range If Intersect(Target, Range("$M$10:$M$209")) Is Nothing Then Exit Sub For Each myCell In Intersect(Target, Range("$M$10:$M$209")) If myCell.Value < "Offer Declined" Then Application.EnableEvents = False myCell.Offset(0,4).ClearContents Application.EnableEvents = True End If Next myCell End Sub HTH, Bernie MS Excel MVP "Alex" wrote in message ... Cheers for that, it does work. One question though, if I want to have it eligable for all cells from M10:M209 and Q10:Q209 how is this done, as I have tried and it bought up an error message on the third line of your formula. "Bernie Deitrick" wrote: Alex, You could use the worksheet change event: copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("$M$10")) Is Nothing Then Exit Sub If Range("$M$10").Value < "Offer Declined" Then Application.EnableEvents = False Range("Q10").ClearContents Application.EnableEvents = True End If End Sub "Alex" wrote in message ... I have set up Data Validation in cell Q10 that only allows text entered into it if cell M10 is equal to "Offer Declined". I have noticed though if you put the status of M10 to "Offer Declined", then go to Q10, enter text, then change the status of M10 to something else, it still allows the text. I was wondering if there was something that I could do that would remove the text in Q10 if M10 changes to anything other than "Offer Declined". Any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation Question? | Excel Worksheet Functions | |||
| Macro question | Excel Worksheet Functions | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| Data validation question | New Users to Excel | |||
Linear Mode
