Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Erasing cells when the validation dropdown choice changes
I have a validation dropdown box in cell D11. Cells D16 and D17 are
"regular" cells. Each time a user selects a different choice in D11, I need to have the contents in D16 & D17 erased (i.e., revert to being blank cells). Being relatively new to VBA, I have no idea how to program this. Can anyone help me? Thanks, Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Erasing cells when the validation dropdown choice changes
Can be done, but how do D16 and D17 get filled to start with?
Once cleared, what's to clear if another choice is made from the DV dropdown? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("D16:D17").ClearContents endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Tue, 18 Aug 2009 12:51:01 -0700, Bob wrote: I have a validation dropdown box in cell D11. Cells D16 and D17 are "regular" cells. Each time a user selects a different choice in D11, I need to have the contents in D16 & D17 erased (i.e., revert to being blank cells). Being relatively new to VBA, I have no idea how to program this. Can anyone help me? Thanks, Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Erasing cells when the validation dropdown choice changes
Gord,
Your code works exactly the way I want it to. Thanks! To answer your questions: Cell D11 = Change Type (e.g., Add, Drop, Move) Cell D16 = Reason Code Cell D17 = Explanation D16 and D17 get filled in by manual entry AFTER a choice has been made in D11. If a user changes their mind and goes back to D11 to select a different choice, then D16 and D17 should be erased since the previously inputted Reason Code and Explanation no longer apply to the newly selected choice. One final question: if I want cell D21 erased as well, would I simply add the following line: Me.Range("D21").ClearContents Thanks again for all your help. Bob "Gord Dibben" wrote: Can be done, but how do D16 and D17 get filled to start with? Once cleared, what's to clear if another choice is made from the DV dropdown? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("D16:D17").ClearContents endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Tue, 18 Aug 2009 12:51:01 -0700, Bob wrote: I have a validation dropdown box in cell D11. Cells D16 and D17 are "regular" cells. Each time a user selects a different choice in D11, I need to have the contents in D16 & D17 erased (i.e., revert to being blank cells). Being relatively new to VBA, I have no idea how to program this. Can anyone help me? Thanks, Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Erasing cells when the validation dropdown choice changes
Just add it into the existing range.
Me.Range("D16:D17,D21").ClearContents Thanks for answering my questions. I thought it might be something like that. Gord On Tue, 18 Aug 2009 19:32:01 -0700, Bob wrote: Gord, Your code works exactly the way I want it to. Thanks! To answer your questions: Cell D11 = Change Type (e.g., Add, Drop, Move) Cell D16 = Reason Code Cell D17 = Explanation D16 and D17 get filled in by manual entry AFTER a choice has been made in D11. If a user changes their mind and goes back to D11 to select a different choice, then D16 and D17 should be erased since the previously inputted Reason Code and Explanation no longer apply to the newly selected choice. One final question: if I want cell D21 erased as well, would I simply add the following line: Me.Range("D21").ClearContents Thanks again for all your help. Bob "Gord Dibben" wrote: Can be done, but how do D16 and D17 get filled to start with? Once cleared, what's to clear if another choice is made from the DV dropdown? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("D16:D17").ClearContents endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Tue, 18 Aug 2009 12:51:01 -0700, Bob wrote: I have a validation dropdown box in cell D11. Cells D16 and D17 are "regular" cells. Each time a user selects a different choice in D11, I need to have the contents in D16 & D17 erased (i.e., revert to being blank cells). Being relatively new to VBA, I have no idea how to program this. Can anyone help me? Thanks, Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Erasing cells when the validation dropdown choice changes
Gord,
Thanks again for your help. I sincerely appreciate it. Regards, Bob "Gord Dibben" wrote: Just add it into the existing range. Me.Range("D16:D17,D21").ClearContents Thanks for answering my questions. I thought it might be something like that. Gord On Tue, 18 Aug 2009 19:32:01 -0700, Bob wrote: Gord, Your code works exactly the way I want it to. Thanks! To answer your questions: Cell D11 = Change Type (e.g., Add, Drop, Move) Cell D16 = Reason Code Cell D17 = Explanation D16 and D17 get filled in by manual entry AFTER a choice has been made in D11. If a user changes their mind and goes back to D11 to select a different choice, then D16 and D17 should be erased since the previously inputted Reason Code and Explanation no longer apply to the newly selected choice. One final question: if I want cell D21 erased as well, would I simply add the following line: Me.Range("D21").ClearContents Thanks again for all your help. Bob "Gord Dibben" wrote: Can be done, but how do D16 and D17 get filled to start with? Once cleared, what's to clear if another choice is made from the DV dropdown? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("D16:D17").ClearContents endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Tue, 18 Aug 2009 12:51:01 -0700, Bob wrote: I have a validation dropdown box in cell D11. Cells D16 and D17 are "regular" cells. Each time a user selects a different choice in D11, I need to have the contents in D16 & D17 erased (i.e., revert to being blank cells). Being relatively new to VBA, I have no idea how to program this. Can anyone help me? Thanks, Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Erasing cells when the validation dropdown choice changes
Gord,
Please forgive me, but may I impose on you again? In cell D5, the user inputs their full name. Is there a way to modify your code below such that after the user has inputted their name and moved to a another cell, the PROPER function can somehow be employed to change the inputted name in cell D5 to Title Case format (i.e., john smith or JOHN SMITH is automatically changed to John Smith)? Thanks, Bob "Gord Dibben" wrote: Just add it into the existing range. Me.Range("D16:D17,D21").ClearContents Thanks for answering my questions. I thought it might be something like that. Gord On Tue, 18 Aug 2009 19:32:01 -0700, Bob wrote: Gord, Your code works exactly the way I want it to. Thanks! To answer your questions: Cell D11 = Change Type (e.g., Add, Drop, Move) Cell D16 = Reason Code Cell D17 = Explanation D16 and D17 get filled in by manual entry AFTER a choice has been made in D11. If a user changes their mind and goes back to D11 to select a different choice, then D16 and D17 should be erased since the previously inputted Reason Code and Explanation no longer apply to the newly selected choice. One final question: if I want cell D21 erased as well, would I simply add the following line: Me.Range("D21").ClearContents Thanks again for all your help. Bob "Gord Dibben" wrote: Can be done, but how do D16 and D17 get filled to start with? Once cleared, what's to clear if another choice is made from the DV dropdown? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("D16:D17").ClearContents endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Tue, 18 Aug 2009 12:51:01 -0700, Bob wrote: I have a validation dropdown box in cell D11. Cells D16 and D17 are "regular" cells. Each time a user selects a different choice in D11, I need to have the contents in D16 & D17 erased (i.e., revert to being blank cells). Being relatively new to VBA, I have no idea how to program this. Can anyone help me? Thanks, Bob |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Erasing cells when the validation dropdown choice changes
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range Set rng = Me.Range("D5") If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("D16:D17,D21").ClearContents rng.Formula = Application.Proper(rng.Formula) endit: Application.EnableEvents = True End Sub Gord On Tue, 18 Aug 2009 21:15:01 -0700, Bob wrote: Gord, Please forgive me, but may I impose on you again? In cell D5, the user inputs their full name. Is there a way to modify your code below such that after the user has inputted their name and moved to a another cell, the PROPER function can somehow be employed to change the inputted name in cell D5 to Title Case format (i.e., john smith or JOHN SMITH is automatically changed to John Smith)? Thanks, Bob "Gord Dibben" wrote: Just add it into the existing range. Me.Range("D16:D17,D21").ClearContents Thanks for answering my questions. I thought it might be something like that. Gord On Tue, 18 Aug 2009 19:32:01 -0700, Bob wrote: Gord, Your code works exactly the way I want it to. Thanks! To answer your questions: Cell D11 = Change Type (e.g., Add, Drop, Move) Cell D16 = Reason Code Cell D17 = Explanation D16 and D17 get filled in by manual entry AFTER a choice has been made in D11. If a user changes their mind and goes back to D11 to select a different choice, then D16 and D17 should be erased since the previously inputted Reason Code and Explanation no longer apply to the newly selected choice. One final question: if I want cell D21 erased as well, would I simply add the following line: Me.Range("D21").ClearContents Thanks again for all your help. Bob "Gord Dibben" wrote: Can be done, but how do D16 and D17 get filled to start with? Once cleared, what's to clear if another choice is made from the DV dropdown? Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("D16:D17").ClearContents endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Tue, 18 Aug 2009 12:51:01 -0700, Bob wrote: I have a validation dropdown box in cell D11. Cells D16 and D17 are "regular" cells. Each time a user selects a different choice in D11, I need to have the contents in D16 & D17 erased (i.e., revert to being blank cells). Being relatively new to VBA, I have no idea how to program this. Can anyone help me? Thanks, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list based on choice of another dropdown | Excel Discussion (Misc queries) | |||
Need to link dropdown box choice to another cell | Excel Discussion (Misc queries) | |||
Multiple choice in dropdown list | Excel Discussion (Misc queries) | |||
populating a dropdown based on choice from a previous dropdown | Excel Programming | |||
Dropdown list/Multiple choice?? | Excel Discussion (Misc queries) |