Results 1 to 5 of 5

Thread: Updating Cell

  1. #1
    ewatson
    Guest

    Updating Cell

    This is a weird one and not sure if it is possible. I want to have a cell with a number in it and when I want to increase the number I would just type that new number into the same cell and it would add the 2.

    Thanks,
    Ed

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Cell

    You can try the following:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim q As Variant
    If Sh.Name = "Sheet1" Then
    If Target = Range("A1") Then
    q = Range("A1").Value
    Application.EnableEvents = False
    Application.Undo
    q = q + Range("A1").Value
    Range("A1").Value = q
    Application.EnableEvents = True
    End If
    End If
    End Sub

    Be sure that you put this code in the SheetChange event of the ThisWorkbook module. I here assumed that the cell you want to use is "A1" on "Sheet1". Of course, you can change this if you want. The macro works as follows: if you enter a value in cell A1, and you press enter, the SheetChange event is triggered. Then, I read the value you entered; then I disable the events and undo your last action; I read the cell and add the previous contents of the cell to this one and enter the two added values into the cell A1 again. Then I enable the events again to be ready for your next entry.

  3. #3
    ewatson
    Guest

    Re: Updating Cell

    Hans,
    I'm not sure where to put this. I looked under the VB Editor and saw only a thiswoorkbook object and only a module1. I'm still new to this. Do I have to create these items?
    Thanks,
    Ed

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Updating Cell

    Ed, just in case Hans is not online, I advise that you double click on the ThisWorkbook object, and place the code posted by Hansn in the main editing window. That should make it available to all worksheets in that workbook.

    Andrew C

  5. #5
    ewatson
    Guest

    Re: Updating Cell

    Awesome!!!!!!
    thanks to both Andrew and Hans

Posting Permissions

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