+ Reply to Thread
Results 1 to 14 of 14

If one cell chanes values, clear another cells data

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    If one cell chanes values, clear another cells data

    I need to clear contents of cell H6 (P/N Config, i.e. -01, -02 etc.), each time there is a change to cell G6 (base P/N). Cell H6 data input is List showing P/N configurations to choose from. When base P/N in G6 is changed, I need to auto clear data contents of H6, P/N configuration, so user is required to select new P/N config. Thank you.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: If one cell chanes values, clear another cells data

    Try this:

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-01-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: If one cell chanes values, clear another cells data

    How do I call this in an auto setting? For example, I make a part description change, the base P/N will automatically change and I want the P/N configuration, cell H6 to automatically clear the existing contents. Can I call the 'change' macro from within my IF function? My goal is to see cell H6 automatically clear cell contents, if the base P/N, cell G6 changes. Thanks for your time and help.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: If one cell chanes values, clear another cells data

    copy my code. Open your spreadsheet. Click on Alt +11. The VBA window will open. In the left window, click on your spreadsheet tab to highlight it. Paste my code into the main window. Save and close. Now everytime that G6 is changed, H6 will automatically be erased.

  5. #5
    Registered User
    Join Date
    03-01-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: If one cell chanes values, clear another cells data

    I am still having issue. The ‘Change’ macro doesn’t seem to run. Not sure why or what I need to do differently? For some reason the Macro doesn’t show up on my Macros dialogue box. See two attached print screens.

    Thank you,
    Lynn

    VB_ClearCell_Issue.pngMacros In my file.png

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: If one cell chanes values, clear another cells data

    It doesn't show up because it is not a macro. It is a VBA solution that generates the action automatically when you change the value in G6. IN your first thread you indicated you wanted it to change automatically. Have you put data in H6 and then changed the value in G6 and the value in H6 should be erased. Have you tested that? Also, if you are using EX2010 have you change the name of your spreadsheet to an .xlsm file to make sure VBA works. Have you enabled macros in your trust section?

  7. #7
    Registered User
    Join Date
    03-01-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: If one cell chanes values, clear another cells data

    I have saved as xlsm. Macros are enabled. I have tested for value change. I am not fully able to apply in my application. I have adjusted my approach. At present I will hold off on any further discussion in this area. Thank you for your time and assistance.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: If one cell chanes values, clear another cells data

    If you upload a copy of your spreadsheet, I will analyze and help to determine the non-execution. Click on the Go Advanced button and follow the wizard.

  9. #9
    Registered User
    Join Date
    03-01-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: If one cell chanes values, clear another cells data

    Within the attached workbook, within the 'STA High Power ASSY' spreadsheet:

    Cell H7 is a list, which cell I7 (Base P/N) changes based on selected CCA Description (cell H7). The issue is, data contents of cell J7 (P/N Configuration) remain the same, even when the 'Base P/N' cell I7 change. However the drop down list for J7 is changed for user to select new value. But if user does not select new value from changed dropdown list the origional dat in J7 remains, which can be miss leading. What I'm seeking for is means to clear contents of cell J7, whenevery cell I7 changes. The way user is required to select new value from changed list in cell J7.

    One option is to assign value in cell I7 to a variables X and Y (for example) then monitor X. If X <> Y, because X changed then clear contents of J7 and update new value to be assigned to X and Y. Just a thought.

    Please help me out on my macro structure in knowing how to call the code you are suggestiong etc.

    Thank you,
    Lynn
    Attached Files Attached Files

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: If one cell chanes values, clear another cells data

    Lynn;
    I don't understand the situation now. I have requested additional help within the forum. Hopefully, someone with more skills than me, will follow up with this. Good luck.
    Alan

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: If one cell chanes values, clear another cells data

    This is what you need now:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: If one cell chanes values, clear another cells data

    I'm a little bemused by your statement:

    What I'm seeking for is means to clear contents of cell J7, whenevery cell I7 changes. The way user is required to select new value from changed list in cell J7.
    The sheet is protected and cell I7 is locked so you can't select it in order to change it. Range J7 doesn't appear to have a Data Validation Drop down or any means to choose anything from a list.


    The following code, specifically monitors cell I7 and, if it changes, clears cell J7

    Please Login or Register  to view this content.

    As the sheet is protected, you would need to un-protect it with your password before you try to make the change and then re-protect it again afterwards.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  13. #13
    Registered User
    Join Date
    03-01-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: If one cell chanes values, clear another cells data

    For clarification and better communication of open issue, please view attached 'CCL Spreadsheet Clarification_Clearing Drop Down List.docx' file.

    In advance, thank you for everyone's time and support.

    Lynn Porter

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: If one cell chanes values, clear another cells data

    OK, I'm still not sure how you plan to make changes to the cell(s) on the sheet because you can't do it manually and there doesn't appear to be any code that will change a value on a cell.

    However, to make the code we've given you work, as it stands, or in a modified format, it needs to go in the sheet class module for the sheet where you want to monitor changes. So, in this case, it needs to go in the class module for ClearOutDatainCells, STA High Power ASSY.

    An example of changing I7 in code:

    Please Login or Register  to view this content.

    This goes in: ClearOutDatainCells, STA High Power ASSY.

    Please Login or Register  to view this content.

    And note that the Workbook_Open event code goes in the Workbook module, not a sheet module.

    Please Login or Register  to view this content.

    And that code only allows the code to modify Sheet1.

    Note that, without cracking the sheet password, I am unable to test the functionality on the actual sheets.


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1