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

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

This code isa only a small variation on my reply to your post 423089 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.

Reply | Quote

This code isa only a small variation on my reply to your post 423089 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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote