Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    grab delete (excel xp)

    hello all yet again,

    I have a column, in each cell in that column I put data, now if I clear the contents in a specific cell I need to know about so that I can refresh another cell that depends on that cell. I am assuming that this would go in the Worksheet-change event. So how do I do this??

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: grab delete (excel xp)

    Could you be a little more explicit on what you need?

    If that "Other cell" you want to change is some reference to the orignal cell, it should "recalc/refresh" when you delete the contents.

    A "worksheet change event" macro will run whenever anything in the worksheet is changed, so it can drramatically affect performance depending on how complicated the task is So if you write a macro to do something ("refresh a cell") it will run WHENEVER anything on the sheet is changed

    Here is a simple example
    Copy it in VB (alt-f11) into the worksheet of interest by dbl-clicking its worksheet objects.

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Range("a1").Value = "" Then
    Range("b1").Value = "A1 has no value"
    Else
    Range("b1").Value = "A1 has a value"
    End If
    End Sub
    </pre>


    In the example whenever the sheet is change it will put in cell B1 that "A1 has a value" (even if it is already there!). If you delete cell A1 it will put "A1 has no value" in cell B1. (I notice that when running this, the performance gets a little sluggish!)

    You could accomplish the SAME thing by instead putting in B1:
    <pre>=if(isblank(a1), "A1 has no value", "A1 has a value")
    </pre>

    and the performance of your spreadsheet will work better, since B1 is NOT constantly updating whenver anything on the sheet changes.

    If you have more specific questions, please add a fe more details so we can better answer your needs.
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    Unfortuntaly this will not work, I will try ato add some more details with examples.

    I will use a1 and b1 as the cells.

    Okay....so the user enters the number 10 in a1, b1 ALREADY has a formula that says a1*10, therefore b1 = 100. Then the user desides that b1 is not the number they want so they change it to 90 instead thereby overwriting the formula a1*10. Note: because b1 changed DOES NOT mean that a1 changes, therefor NO circular reference. Anyway, then the user decides that they do not need a value in a1 at all, so the clear the contents of a1, so now a1 = "". This is what I need the program to do now.......once the user clears the contents of a1, the formula in b1 must now go back to the original formula of a1*10. That's it, plain and simple...I hope!!

    I hope this makes it easier to understand.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    The following code for the Worksheet Change event will put the formula =10*A1 into cell B1 whenever cell A1 is cleared.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)

    For Each cell In Target.Cells
    If cell.Address = "$A$1" And cell.Value = "" Then
    Range("b1").Formula = "=10*A1"
    Exit For
    End If
    Next cell

    End Sub
    </pre>


    Is this what you want?

    Ian.

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    Why resort to VBA? If you protect the worksheet you can prevent users from overwriting the formula in cell B1 in the first place.

    1) select cell A1 and click Format | Cells, select the Protection tab and uncheck the 'Locked' tick box.
    2) select the entire sheet and click Tools | Protection | Protect Sheet
    3) enter a password if desired and OK

    Now the ONLY cell user can do anything to is cell A1. The error message that appears if a user then tries to butcher your carefully crafted spreadsheet will be a valuable training aid!

    NB If your actual spreadsheet contains other VBA code you may have to check out the userinterfaceonly argument of the protect method to prevent that code from dying once the sheet is protected.

    (not)stuck (just now)

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    The following code, placed in the Change Event procedure for the worksheet in question, will insert the formula into column B whenever the corresponding cell in column A is cleared to multiply the corresponding cell in column A by 10.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then
    For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
    If oCell.Value = "" Then
    oCell.Offset(0, 1).FormulaR1C1 = "=10*RC[-1]"
    End If
    Next oCell
    End If
    End Sub
    </pre>

    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    Legare,

    This is almost exactly what i want it to do. There is a slight problem though, the changes don't take place until after I have left the a1 cell. Therefor, i have to leave a1 then come back to it in order for the formula in b1 to be there again. What I want to be able to do is clear the contents in a1, then imediatly have the formula in b1 ready for a new input in to a1. This then effectively "grabs" the delete button.

    is this possible?

    thanks

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    In my XL2K it happens as soon as I hit the Delete key. Don't have XP (and never will as long as it includes the registration/activation wizard), so I don't know what happens there. If it doesn't happen then on XP, I don't know of any other way to do it since there is no specific delete or clear event.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    Try with this, this is a complement to the code of Legare:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then
    For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
    If oCell.Value = "" Or oCell.Offset(0, 1).Value = "" Then
    oCell.Offset(0, 1).FormulaR1C1 = "=10*RC[-1]"
    End If
    Next oCell
    End If
    If Not Intersect(Target, ActiveSheet.Range("B:B")) Is Nothing Then
    For Each oCell In Intersect(Target, ActiveSheet.Range("B:B"))
    If oCell.Value = "" Then
    oCell.FormulaR1C1 = "=10*RC[-1]"
    End If
    Next oCell
    End If

    End Sub

  10. #10
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: grab delete (excel xp)

    Ahah!!!!!!!

    I know why it was not working for me, originally I had put Legare's code into the Worksheet_SelectionChange event rather than the Worksheet_Change event. That was the problem, now that I moved it to the Change event, it works perfectly!!!!! Thanks!!!

    But now I am a bit confused, what is the difference between the two? Is the selection change event have to lose focus before it initiates code or what?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: grab delete (excel xp)

    As you noticed the "selection Change" does NOT occur UNTIL the selection changes. You must LEAVE cell A1. Deleting the contents of A1 does not "change the selection" You STILL have A1 selected!

    Worksheet change is a change to the worksheet, even if you stay in the same cell.

    If on "Enter" you do not move the selection, it will register a worksheet change on enter, BUT not a selection change

    Steve

Posting Permissions

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