Text to formula conversion/calculation

[surya prakash]

Hi,
I am wondering if the following scenario is possible.
The user enter a calculation string in A column, the result should appear in the B column. The values in the B column should change instantaneously when user changes the corresponding value in the A column.


The string is entered in Column A only and values should be updated in B column only.

Thanks
Surya prakash

[surya prakash]

Sorry, there seems to a problem with HTML tools.
I am attaching my file.

[Jacob Hilderbrand]

Sorry Surya, we do not allow html code in the forums for security reasons. We do allow some BB Code for formatting and of course you can attach a zip file with your workbook.

[Jacob Hilderbrand]

So do you want to do something like this.

In A1 type 5+7 and it would display 5+7 and B1 would display 12.

[Zack Barresse]

If this is the case (that DRJ is talking about) check out our KB entry here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=62

You could use this formula in B1 if you entered the formula =1+1 in A1. So in B1 enter ..

=FTEXT(A1)

and you'd see ..

=1+1

To see it w/o the = sign, use something like ..

=RIGHT(FTEXT(A1),LEN(FTEXT(A1))-1)

..which would show ..

1+1

HTH

[surya prakash]

Hello Zack,

Thank you for your response.
I have developed sample code on Worksheet_SelectionChange and attached the same.

I want to try-out Worksheet_SelectionChange function.

The problem I have is, I want the Worksheet_SelectionChange to be applicable in the column C. Right now the values are updated only when the user enters values in B2 and the anwser is available in c2.

I am wondering if it is possible program in such a way that whenever a user enter a string in column b, the value is updated in the corresponding cell of column C

Thanks
surya

Ps: I am not able to upload the excel file, I am copying the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RowNo, ColNo As Integer
With Sheets("Estimate")
            RowNo = 2
            Temp1 = .Cells(RowNo, 2).Text
            .Cells(RowNo, 3).FormulaR1C1 = "=" & Temp1
    End With
End Sub

[surya prakash]

Hello DRJ,

Thank you for your response.
You are right, when the user enter 5+7, the total should be available in the next column.
Only thing is I wanted to use worksheet function instead of a procedure (inserted in module).
The reason being, I don?t have to use text2val(a2) function every time and copy it where ever I want the results. By worksheet change event, the value is automatically calculated & updated in the corresponding next column (say b2), when ever I enter a string in column b.

Thanks
Surya Prakash

[Jacob Hilderbrand]

Try this.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
        Range("B" & Target.Row).Value = _
        Evaluate(Target.Text)
    End If
End Sub

[Ken Puls]

Ps: I am not able to upload the excel file

Hi Surya,

FYI, the file needs to be zipped to upload it. (Saves on board space.)

Cheers,

[surya prakash]

thanks KPuls, I will zip all files in future.

hello DRJ,

the solution works. Thank you.

But, when the string is removed, I am getting #value in the other column.
Sheet is enclosed for your ready reference

thanks
Surya

#VALUE!

[Jacob Hilderbrand]

Well if you remove the string it tries to calculate the value of the new string ("") which doesn't work. Try this.

Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Column = 1 And Target.Value <> "" Then 
Range("B" & Target.Row).Value = _ 
Evaluate(Target.Text) 
End If 
End Sub

[surya prakash]

Thank you DRJ,
There is a minor problem, I the value is "", the value in the target cell is also;
but when you over-write a value to "" and then type a value in the same cell; the target value is still "".
Can you please check.


I have made minor modfication to the code, but it doesnt work.
Thanks
Surya

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Estimate")
If Target.Column = 1 And Target.Value <> "" Then
        Range("B" & Target.Row).Value = Evaluate(Target.Text)
    Else:
        Range("B" & Target.Row).Value = ""
    End If
End With
End Sub

[Jacob Hilderbrand]

Try this.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Val1            As Variant
Application.EnableEvents = False
If Target.Column = 1 Then
        Val1 = Evaluate(Target.Text)
        If IsError(Val1) = True Then
            Range("B" & Target.Row).ClearContents
        Else
            Range("B" & Target.Row).Value = Val1
        End If
    End If
Application.EnableEvents = True
End Sub

[surya prakash]

Hello DRJ,
My problem is solved; thank you very much indeed.

The macro is used only in our estimation workbooks, there are several other workbooks in which this macro should not work.

So instead of embedding the macro in all our estimation sheets, will it be Ok, if I convert the same to an addin, then it is availble to all worksheets.

But then, the macro should work only in estimate workbook, is there any way that I could accomplish that?

I am wondering, if it is possible to code in such a way that if the cell value is "Validate Formula", then the macro works, otherwise no action is taken.
I dont know if there is a better way.

Thanks
-surya

[Jacob Hilderbrand]

Put this code in the ThisWorkbook module.

Option Explicit
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Val1 As Variant
Application.EnableEvents = False
Select Case Sh.Name
Case Is = "Sheet1", "Sheet2"
If Target.Column = 1 Then
Val1 = Evaluate(Target.Text)
If IsError(Val1) = True Then
Range("B" & Target.Row).ClearContents
Else
Range("B" & Target.Row).Value = Val1
End If
End If
End Select
Application.EnableEvents = True
End Sub

Then just add the sheet names that you want the code to run for. Or if there are only a few sheets that you don't want the code to run on we can specify those instead.

[surya prakash]

hello Drj,
You have solved my problem. Thank you so much for your time.

I have a small query:
I have several sheets in which the formula validation has to be done; I am wondering if I could use wild-card in the sheet name; for example I have sheets called as Sheet1, Sheet2, Sheet3 ..... Sheet10.

I made the following modication in the code but it doesnt work; can you please suggest.

Select Case Sh.Name 
    Case Is = "Sheet" & "*"

Thanks again.
Surya

[Jacob Hilderbrand]

Try something like this.
[vba]
Select Case Left(Sh.Name,5)
Case Is = "Sheet"[/vba]

[johnske]

Hi surya,

You could also try

Select Case Sh.Name 
Case Is Like "Sheet*"

(haven't tried Like with a case statement, but it should work)

[surya prakash]

Hello DRJ/ John
I have tried both the options

Select Case Sh.Name 
Case Is Like "Sheet*"

VBA env is not accepting 'like' in case
similaly left function is not giving me the result.
Can you please check?

Thanks
Surya

[johnske]

Hmmm, sorry, I should've checked. Try this variation on Jakes code:

Option Explicit
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Val1 As Variant, i%
Application.EnableEvents = False
'For i = 1 To 2 '< put however many sheets you want here
For i = 1 To Sheets.Count '< if you want it on every sheet
Select Case Sh.Name
Case Is = "Sheet" & i
If Target.Column = 1 Then
Val1 = Evaluate(Target.Text)
If IsError(Val1) = True Then
Range("B" & Target.Row).ClearContents
Else
Range("B" & Target.Row).Value = Val1
End If
End If
End Select
Next i
Application.EnableEvents = True
End Sub