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. Advertising

  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. Advertising

  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. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. StephenT
    Replies:
    2
    Views:
    36
  2. jrh

    using vba to update vba form

    jrh, Sep 23, 2003, in forum: Excel Programming
    Replies:
    3
    Views:
    40
    Vickie
    Sep 23, 2003
  3. CAPTGNVR
    Replies:
    2
    Views:
    92
    Gary Keramidas
    Jul 8, 2007
  4. Replies:
    5
    Views:
    72
    Rick Rothstein \(MVP - VB\)
    Jun 28, 2008
  5. Paul
    Replies:
    2
    Views:
    50
Loading...

Share This Page