Results 1 to 5 of 5
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Restoring a Formula

    A worksheet cell named DefaultValue contains a formula, say =2*Foo

    Another cell named SelectedValue contains the formula =DefaultValue

    The integer in SelectedValue is used for further processing on the worksheet. When a user first sees the worksheet, SelectedValue contains the default value of 2*Foo. But the user can enter any number he wants into the SelectedValue cell, so he can see the effect of various numbers on the worksheet.

    If he enters a number, that removes the formula from the cell. He can type the default value back in, but once he enters any number into SelectedValue, the worksheet is forever changed.

    I want to add a command button to put the original formula back into SelectedValue. I can add the button, but I can't figure out how to make it restore the formula.

    Any ideas?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    Range("SelectedValue").formula = "=DefaultValue"
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2011-08-06)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Works perfectly! Thanks.

    Now what I'd REALLY like to do is to have the background of SelectedValue change color when somebody enters a number into it (even if that number is the default value), or maybe if they even just select the cell, and change back to its original color when the Reset button is pushed.


    I'm picking this stuff up again, but I've never done anything that changes font color or background color.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    Here's a start although I don't think it will cover all situations, i.e. if the enter text vs number.
    Create a module and enter this code:

    Code:
    Option Explicit
    
    Public Function IsDefault(zTestRng As String, zTestVal As String) As Boolean
    
       IsDefault = (Range(zTestRng).Formula = zTestVal)
    
    End Function
    Then setup conditional formatting for the cell Named: SelectedValue as shown.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2011-08-07)

  7. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    This looks like it will work fine! If somebody enters text instead of a number, the rest of the worksheet won't work, anyway.

    Thanks SO much for your help with these little things. Without it, I'd be digging forever through the books. I had forgotten about it, but several years ago I did a few small projects with VBA. So I'm not TOTALLY in the dark about it -- just inexperienced and out of practice.

    This whole project is for making and solving small cryptogram puzzles. It's coming along very nicely. I can encrypt 'em really well, and with a bit more work, I'll have Excel helping me to solve 'em.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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