Text to formula conversion/calculation

[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 )

[surya prakash]

Thank you john...