Results 1 to 15 of 15

20041108, 06:33 #1
 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.

20041108, 09:09 #2
 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

20041108, 13:20 #3
 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.

20041108, 13:49 #4
 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.

20041108, 14:33 #5
 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.

20041108, 14:42 #6
 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.

20041108, 17:35 #7
 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.

20041109, 14:51 #8
 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.

20041109, 22:56 #9
 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

20041111, 11:17 #10
 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.

20041112, 11:55 #11
 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.

20041112, 11:59 #12
 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.

20041112, 16:57 #13
 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.

20041113, 15:19 #14
 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.

20041113, 18:19 #15
 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.