+ Reply to Thread
Results 1 to 9 of 9

how to delete contents of 2 adj cells when one cell is changed or cleared

  1. #1
    Registered User
    Join Date
    07-18-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    how to delete contents of 2 adj cells when one cell is changed or cleared

    I am trying to build a task check list check with a couple of “intelligent cells.
    Below is a screenshot of the spreadsheet.

    As you can see below, I have created a a button that lives in column E for each row.
    The basic button, which is in E2, calls “Button6_Click” (code below), and basically does what I want it to. There may be an easier way (and am willing to be shown how), but I have grabbed the user name and input it into column “C” of the same row, and put the date/time stamp in column ‘D” of the same row.

    I have another button, called “Button86_Click”. I have it called from column E for the rows that required “data”, in column “B”. Primarily I have not allowed the button to autofill the user name and date int columns “C” and “D” respectively if the “data” cell is blank. That appears to work. What I also need to do is blank out the username and date in the columns “C” and “D” if the user makes any change or totally deletes the value in the data column “B”.

    I am sure there is probably a way to also combine both of my code segments below to a procedure call, which could then be called by each button click, since they basically do the same thing, except for needing to determine if there is any data in the “C” column for the respective row.

    Thanks in advance for any help.

    ...
    [attach]excel ss.PNG[/attach]
    screen image of worksheet here:(this did not appear work for some reason, and I have attached a word doc with question that also contains the screen shot of my worksheet



    …………………………………………
    Please Login or Register  to view this content.
    …………………………………………..
    Please Login or Register  to view this content.
    ………………………………………….
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Cutter; 07-20-2012 at 09:53 AM. Reason: Added code tags

  2. #2
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    I would probably replace both with a worksheet_change macro:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-18-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    Thanks for the reply.

    I am such a newbe here, I am sure I am over looking something really basic, so I apologize ahead of time.
    what I did:
    I opened up my test3.xls file
    I clicked the Macro icon on developer ribbon: Macro window opened
    I entered "Worksheet_Change" into the top space, expecting the "Create" button to un-grey, but it did not, so I clicked the "edit"button.
    This opened up the VBA project and a test3.xls Module2 (Code) window with the following in it already:

    Please Login or Register  to view this content.
    I commented out the "With" clause above, and copied your code into the area below, and did a save.

    I then went back to my Excel spreadsheet and right-clicked one of my buttons that originally called the "Button6_Click" macro, and selected "assign Macro".
    The Assign Macro window appeared, and I expected the Worksheet_Change Macro to be there so I could select it, but it was not. Only the "Button6_Click", "Button86_CLick" marcos that I previously worked with.

    How do I assign this "Worksheet_Change" Macro to the button I have on the spreadsheet, or do I need to do it a totally different way?

    Thanks in advance for your help.
    Larry
    Last edited by Cutter; 07-20-2012 at 09:54 AM. Reason: Added code tags

  4. #4
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    Sorry, I jumped ahead a bit. Activate/view the sheet you want this to work on, and press alt-F11 to go to the VBA IDE. It should, by default, open the worksheet's module. Paste the code in there, then just change a value in column B to see its effect.

  5. #5
    Registered User
    Join Date
    07-18-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    Thanks again for your reply.
    I have to continue to apologize for my incompetence with BVA and Excell.
    I tried what you said, but did not see any change in the worksheet behavior.

    What I did:
    I opened up the VBA IDE as you suggested with alt-F11. It came up but opened up all the code I had already written. I was not sure “which” “module” to put the code in.
    I tried to create a new module and pasted the code into it. But no change. I tried to “validate” the “B” cell and assign the “Workshhet_change” procedure, as the “formula” for the custom “validation. No Luck. I even tried to edit the “assign macro” part of the “button” I have in the “E” column, to “worksheet_change”, but when I clicked the button, I got the error: “Argument not optional”.
    So…
    I just started over, I created another workbook, and worksheet, and all I did was copy over the description text in column one, as well as the 1st row of Colum headings I had in the previous worksheet.
    I then opened the VBA IDE as you said to with alt-F11. The IDE came up, again with all the code from the original workbook, but I noticed in the left column my new workbook (named lo1.xls).
    I did not see any “modules” listed, so I clicked on the workbook icon and blank page presented itself to me, labeled “ThisWorkbook (Code)”. I pasted your code into this window and saved the file. Went over to the spreadsheet and made changes to one of the cells in column “B”. Nothing.
    I then went back to the IDE and created a “module”, in which I was presented with another blank page, this time labeled “Module1 (Code). I pasted your code into this module and saved it. Still nothing.

    I am sure that I am missing something really simple here, but just do not know what to do. Maybe initial set up is wrong.
    Don’t I still have to somehow “assign” this module, or “code”, or subroutine to the cells or the column (“B”), or something? Don’t I still need to create a button somewhere, where the user can click on it to populate their name and date for rows where they do not have access and cannot even modify or change the value in the “B” cell on that row? (See the pic of my original sheet in my post).

    Thanks in advance for your continued help and patience,
    Larry

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    @ larryoslund

    Welcome to the forum.

    Please notice that code tags have been added to your post(s). The forum rules (Rule #3) require them so please keep that in mind and add them yourself whenever showing code in any of your future posts.
    Thanks.

  7. #7
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    I guess I misspoke, it doesn't automatically open the worksheet module. In the VBA IDE, which you will see when you press alt-F11, there should be a Project explorer. If not press Ctrl-R to open in. In Project explorer you will see a tree similar with any add-ins listed and a VBAProject associated with your workbook. With the VBAProject expanded, you should see a worksheet module associated with each worksheet in your workbook, as well as the ThisWorkbook module. Double-click on the worksheet module of the worksheet where you want to catch the changes, I expect it will be blank. Paste the code in here.

  8. #8
    Registered User
    Join Date
    07-18-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    Thanks –
    That works great now. I guess the subroutine just has to live in the sheet that is using it.
    This solution has introduced another small issue.
    I do not want to allow anyone to just select either the name or the date column and type “just anything” there. But, when I try and “protect” then, I get a run-time error “1104” that says the sheet is protected. Is there a way you can “unprotect” the sheet in the procedure, make the changes and then “protect” it back at the end.
    I have tried various things, but they have all failed up to this point, so I figured I would ask.
    Thanks again,
    Larry

    p.s an odd behavior also: I commented out the line of code you gave me that did the number format, (since I protect the sheet), to see if it would just format to what I have previously formatted the column to, and then the entire worksheet_change sub appeared to not work any longer, and no debug errors came up. Even when I changed it back, the sub still did not work. I did a “save: each time. I had to start over on an entirely new workbook to get it to work again. Any ideas of what is up with that?

  9. #9
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: how to delete contents of 2 adj cells when one cell is changed or cleared

    I have couple of routines I use to protect sheets, probably a bit of overkill for what you are doing:

    Please Login or Register  to view this content.
    Overkill because you really only need the .protect and .unprotect with the password. But, when you do this a bunch of times in a worksheet, this allows you to change the password twice, instead of twice for every instance.

    Insert a new module and paste in this code. If you have a password on it, change "mypass" to match yours. Then, in your original worksheet_change module, tweak it like this:

    Please Login or Register  to view this content.
    I also inserted the application.enableevents on the first half, to cut down on the event noise a bit.

    As for your second question, I suspect that you entered break mode after the "application.enableevents = false" ran and before it set it back to true. All you need to do is go to the Immediate Window (ctrl-G) and execute the command:

    application.enableevents = true

    Just paste this in and hit the enter key from somewhere on the line.
    Last edited by wallyeye; 07-20-2012 at 02:24 PM.

+ 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