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.

Select Case Left(Sh.Name,5)
Case Is = "Sheet"

[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

[surya prakash]

Hello John,
thank you for your response.
We have different excel file templates for different purposes.
The above function should not be available to all the sheets of a workbook.
For example I have sheets called as Master, Calc1, Calc1, split1, split2, split3, split4 etc.
The macro should work only in Calc1, calc2 sheets and not in all the sheets. It should not disturb data in other sheets.

So if the macro takes a wildcard * such as Calc*, it will be very userful.

thanks again
Surya

[johnske]

In that case you would just use


For i = 1 To 2 '< put however many sheets you want here
Select Case Sh.Name
Case Is = "Calc" & i
'etc.
Next i

[surya prakash]

Hello John,
still there seems to a problem.
I am attaching the excel file for your ready reference.

The macro should work only in 'Calc Pre' & "Calc Post" for example.
I want to use a wildcard in the form of 'Calc*'

Thanks
Surya

[johnske]

Hi surya,

I had a look at your attachment, you currently only have 2 sheets in there with "Calc" as the 1st part of the name - if there's only 2 sheets, the solution that Jacob gave you is more than adequate for your purposes, all you have to do is change "Sheet1", "Sheet2" to "CalcPre", "CalcPost" or whatever other name you care to give the sheets. If there are a couple more sheets like that, you just add their names to the case statement.

However, I'm going to assume that maybe there will eventually be many sheets added later with "Calc" as the first part of the name, and change the case statemant to an If statement so we can use wildcards:


Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Val1 As Variant
Application.EnableEvents = False
If Sh.Name Like "Calc*" Then
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 If
Application.EnableEvents = True
End Sub

HTH
John

[surya prakash]

hello John,

Thank you very for the solution. My post is solved...

You are right, there are multiple sheets such as Calc Pre, Calc Post, Calc Tree etc.

I have noted that you have used if statement instead of Case to use wildcards.

Can the Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) converted to a add-in, so that it is macro is available to all the sheets with name "Calc". (I know this can be new post all-together)


thanks again.
surya

[surya prakash]

Hello John,

I have saved the workbook into Excel Addin, to avoid mulitple copies of macros.

My doubt is, will the Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) be available thro the addin.

Is there any way out?
Thanks again.
surya

[johnske]

Sorry, surya, I'm no help to you there - I don't use add-ins at all (except for a code indenter) 'cos I've only got a slow old machine and add-ins slow down Excel startup too much for my liking - so, without trying it, I really dont know whether or not you can do it.

All I can do is say, wait for someone else to answer your question, or post it as a new thread...

Regards,
John

[surya prakash]

Thank you John, for all the time you could spare; I really appreciate.

I am not sure myself if Workbook_SheetChange() can be added in a addin.
The reason I want to add the macro to addin, is that the macro should be globally available to all the workbooks without having to add the module to each workbook.

I dont know if the converting the same into a add-in is the only way.

Anyways, I will just wait and see if someone like DRJ can suggest anything?

Thanks a million
surya

[surya prakash]

Hello john

btw, what is code indenter?

regards
surya

[johnske]

When you have a lot of nested "If" statements, it indents the code so the "End If"s are directly below the "If"s they're related to, so you know which "If" statement you're actually working with. (have a look at my code above and then look here (http://bmsltd.ie/Excel/SBXLPage.asp) ) :devil:

[surya prakash]

Thank you john...