Update cell using vba

Discussion in 'Excel Programming' started by Pete Provencher, Mar 26, 2007.

  1. Using Excel 2003:

    What I would like to do is type a number into a cell and then when I hit
    enter I would like vba to update that number through a formula. I know I
    could just add another column but if possible I would like to do it the way
    mentioned.

    Pete Provencher
     
    Pete Provencher, Mar 26, 2007
    #1
    1. Advertisements

  2. I'd use the other cell--just because it might make it easier to update the cell
    when I make a typing change.

    But if you want, rightclick on the worksheet tab that should behave this way.
    Select View Code and paste this into the code window:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

    On Error GoTo ErrHandler:

    If IsNumeric(Target.Value) = False Then
    Exit Sub
    End If

    Application.EnableEvents = False
    Target.Value = Target.Value * 2 + 3 + Target.Value ^ 4

    ErrHandler:
    Application.EnableEvents = True

    End Sub

    I check column A -- you may want to change this.

    And I bet my formula isn't what you wanted.

    Pete Provencher wrote:
    >
    > Using Excel 2003:
    >
    > What I would like to do is type a number into a cell and then when I hit
    > enter I would like vba to update that number through a formula. I know I
    > could just add another column but if possible I would like to do it the way
    > mentioned.
    >
    > Pete Provencher


    --

    Dave Peterson
     
    Dave Peterson, Mar 26, 2007
    #2
    1. Advertisements

  3. Thanks. It worked for what I needed.

    Pete Provencher
    "Dave Peterson" <> wrote in message
    news:...
    > I'd use the other cell--just because it might make it easier to update the
    > cell
    > when I make a typing change.
    >
    > But if you want, rightclick on the worksheet tab that should behave this
    > way.
    > Select View Code and paste this into the code window:
    >
    > Option Explicit
    > Private Sub Worksheet_Change(ByVal Target As Range)
    >
    > If Target.Cells.Count > 1 Then Exit Sub
    > If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
    >
    > On Error GoTo ErrHandler:
    >
    > If IsNumeric(Target.Value) = False Then
    > Exit Sub
    > End If
    >
    > Application.EnableEvents = False
    > Target.Value = Target.Value * 2 + 3 + Target.Value ^ 4
    >
    > ErrHandler:
    > Application.EnableEvents = True
    >
    > End Sub
    >
    > I check column A -- you may want to change this.
    >
    > And I bet my formula isn't what you wanted.
    >
    > Pete Provencher wrote:
    >>
    >> Using Excel 2003:
    >>
    >> What I would like to do is type a number into a cell and then when I hit
    >> enter I would like vba to update that number through a formula. I know I
    >> could just add another column but if possible I would like to do it the
    >> way
    >> mentioned.
    >>
    >> Pete Provencher

    >
    > --
    >
    > Dave Peterson
     
    Pete Provencher, Mar 26, 2007
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Michael Beckinsale

    Update links using VBA

    Michael Beckinsale, Aug 1, 2003, in forum: Excel Programming
    Replies:
    0
    Views:
    89
    Michael Beckinsale
    Aug 1, 2003
  2. jrh

    using vba to update vba form

    jrh, Sep 23, 2003, in forum: Excel Programming
    Replies:
    3
    Views:
    218
    Vickie
    Sep 23, 2003
  3. Gummy
    Replies:
    3
    Views:
    205
    Jon Peltier
    Jun 12, 2007
  4. CAPTGNVR
    Replies:
    2
    Views:
    289
    Gary Keramidas
    Jul 8, 2007
  5. Marie
    Replies:
    6
    Views:
    271
    Marie
    May 23, 2008
  6. Replies:
    5
    Views:
    257
    Rick Rothstein \(MVP - VB\)
    Jun 28, 2008
  7. Replies:
    1
    Views:
    154
    TomPl
    Aug 21, 2008
  8. Forgone
    Replies:
    3
    Views:
    136
    Forgone
    Apr 8, 2010
Loading...