Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    inputbox that shows formula & edit formula purpose (excel 2002)

    hi. i would like to create an inputbox that shows the existing formula in a selected cell and from the inputbox i too can edit the formula. can this be ok?? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    Try this:

    Sub InputFormula()
    Dim varResult As Variant
    varResult = Application.InputBox("Edit the formula", , ActiveCell.Formula, , , , , 0)
    If varResult = False Then
    ' canceled - ignore
    Else
    ActiveCell.Formula = varResult
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    wah... very nice. i will put that in my work when the pc stops running from another macro.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    This code isa only a small variation on my reply to your <post#=423089>post 423089</post#> from last week. If you click in the word InputBox (in the Visual Basic Editor) and press F1, you'll get help for this method, and an example. You can use the online help to learn how to adapt the code further.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    Hi. where do you mean the online help is, is it the MSDN on The Web link in the Help menu in the Visual Basic Editor? Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    The online help is installed as part of Microsoft Office. You can obtain help in several ways in the Visual Basic Editor:
    - Select Help | Microsoft Visual Basic Help.
    - Click in a word in a module, and press F1.
    - Activate the Object Browser (F2), enter a search term, press Enter, click on one of the listed items, press F1.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    Hi Hans. Thanks. now i realise the comma marks in the InputBox() are the 8 information input into the dialog box. and the real input by the user is the Default info and its Type is goes with the Default info. others are optional with compulsory wordings in the Prompt info. Thanks.

    about the inputbox, where i edit my formula, without clicking the formula with mouse at the place i desire to edit, can the edit function be done with by typing any keys relevent in the keyboard? any keyboard keys can too edit my formula in the input dialog box without using mouse? the formula displayed at active cell is auto highlighted with black color and if i hit left,right,top, or bottom, it won't be right. any keys to edit without disturbing the original formula?
    Thanks again.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    Can you try to explain that again? I don't understand.

  9. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: inputbox that shows formula & edit formula pur

    Boat

    The inputbox command that Hans has suggested is modeless so using the keyboard is either going to wipe the current selection or move around the excell cells in the background. I couldn't find a way to unselect the formula (without the mouse) in the inputbox dialog so I think you may have to find another way to display the cell contents.

    Is there a reason you don't want to use the formula bar to edit the cell contents?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula pur

    you can go into edit mode by pressing F2. if you want this to be the standard behavior of the inputbox, you could try sending the f2 key with the vba.sendkeys method before you call the inputbox, but i don't know (and haven't tried) if this would work.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula pur

    hi Pieter. Thanks for your post. yes, i realise F2 helps in editing formula at active cell. but i couldn't get that either in inputbox as you suggested. Thanks.

  12. #12
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula pur

    hi Andrew. Thanks for your reply. yes, i understand. your experience same with me. since i use the macros, (about 30 minutes run macros), i use keyboard shortcut to use them, i press Alt + a small letter key to run macro, writing formulas using keyboard, copy&paste function use keyboard, except editing formulas (using the mouse require point the cursor at end of formula, which i realise the formula gets unseen in the inputbox since it is long. so what do i do to edit the formula? i point mouse cursor at the end of inputbox where there is first part of whole formula, press '+' sign to get the 'behind' part formula, delete the "+" sign, use mouse carefully precise cursor at end formula and & use keyboard to key formula). and i realise using keyboard to edit formula in inputbox (if that realised) i can move right left cursor freely).

    i use keyboard, in fact faster i realise. and have fingers exercise. (my hand feels stiff using mouse, and gets warm holding it longer, especially there is one optical). THANKS. any suggestion? THANKS.

  13. #13
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    Hi Hans. yes, i can explain again about using keyboard to edit inputbox. when the inputbox appears in my pc screen asking to edit formula, i have to edit by clicking the place i want to edit using mouse. i cannot find ways to edit using keyboard. with keyboard, i can move to left and right freely to edit my formula in inputbox. with mouse, i can edit my formula by clicking at the exact place i want to edit because if i move the cursor with keyboard, the formula gets edited of unwanted cells address. with long formula that extends unseen in the display inputbox, editing using mouse can be with some extra efforts, like i edit a 'dummy' key at the end of the inputbox to get the remaining formula seen and delete the dummy key later before real editing comes in. i hope my explanation makes sense. thanks. bye.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    Perhaps you should change

    varResult = Application.InputBox("Edit the formula", , ActiveCell.Formula, , , , , 0)

    to

    varResult = InputBox("Edit the formula", , ActiveCell.Formula)

    You will lose the ability to use the mouse to point to cells, but you can use the keyboard, including the arrow keys.

  15. #15
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: inputbox that shows formula & edit formula purpose (excel 2002)

    hi again. thanks again. i have keyboard function, i can forgo mouse.

Posting Permissions

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