inputbox method to get a formula

[xuefang]

I create a macro which ask the user to input a formula(user can select a range in the excel screen), then I copy this formula to a specific row of every worksheets.
The problem is Application.InputBox("Insert a Formula", "This accepts Formula", 0+2) can not return the formula, it will automatically evaluate this formula and return a value!
This code is:

Sub CreateNewItem1()
        Dim bFormula As String
        hi=10
        Application.ScreenUpdating = True
          
        ' here is the INPUTBOX-method :
        On Error Resume Next
        Application.DisplayAlerts = False
        bFormula =Application.InputBox("Insert a Formula", "This accepts Formula", 0+2 )
        On Error GoTo 0
        Application.DisplayAlerts = True
        MsgBox "You have inserted bFormula is:" & bFormula

        'Cells(hi, 2).Select.FormulaLocal = bFormula
        ' MsgBox Cells(hi, 2).Select.FormulaLocal      
        
       Call CreateNewItem2((hi))  'call the copy formula macro                   
End Sub

can somebody solve this problem? Or can give me some suggestion about how to pause the macro a while to let user input a formula? Thanks a lot.

 

[SkipVought]

Hi,

"...input a formula(user can select a range in the excel screen), ..."

How does the user select a range in an InputBox?

What does Call CreateNewItem2((hi)) do?

You have to supply a bit more information.

Are you saying that if I enter A1+B1, then the InputBox returns the evaluated value?


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[xuefang]

hi,thank you for the reply
1.User not select a range in an inputbox.
What I mean is: When inputbox method pop up a prompt window,the user can select a range using a mouse in the excel worksheet screen, and the address will show up in the inputbox window.

2.Call CreateNewItem2((hi))is just invoke the macro which copy this return formula to other cells.You can ignore this command line.

 

[SkipVought]

So how would you select a range to created a formula?

What would you want a typical value in bFormula to be?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

Why not just let the user enter a formula into a cell?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

Anyway...

     bFormula = Application.InputBox("Insert a Formula", "This accepts Formula", 0 + 2[b], Type:=0[/b])

     Cells(hi, 2).Formula[b]R1C1[/b] = bFormula

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[xuefang]

Thank you very much. It works very well.I realized that I made a mistake in inputbox statement, I omitted "type:=0" part, I just copy those codes from exceltip.com.
If as you say: let the user to input a formula into a cell, after he finish inputing. How can I continue my macro? How do I know he finished inputting the formula?

 

[SkipVought]

What does your macro do after the user enters a formula?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[xuefang]

After user enter the formula, I will copy this formula to the cells of the same row in all of the worksheets(about 100 sheets).

 

[SkipVought]

Woa! 100 sheets???

Does the formula ALWAYS go in B10?

Is the formula ABSOLUTE or RELATIVE?

Take a look at the Worksheet_Change event.



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]