Addin Function that doesnt work

[surya prakash]

I have a converted an excel file into an addin.

While the vba code works alright in my excel file, it doesnt execute, when I convert the sheet into excel addin.

Can somebody have a look; I am attaching both the files for ready reference...


thanks

[Killian]

Your code is in the workbook's sheet change event. When you convert to an addin, the workbooks sheets are hidden and can't be changed by the user directly so this event will never fire.
What you need is an event at application level (rather than the workbook), so set up an application variable WithEvents at the top of the module [VBA]Dim WithEvents myApp As Application[/VBA]and on the Addin's WorkBook_Open event, initialize it[VBA]Set myApp = ThisWorkbook.Application[/VBA]Now, in the dropdown at the top left of the code window, you can select the "myApp" object, and in the right dropdown, you'll have the application events available and your code can then go in myApp_SheetChange

[surya prakash]

Hi Killian,
I am getting Compile error: Invalid attribute in sub or function in the line
Dim WithEvents myApp As Application

I am reproducing the code for your ready reference.

[VBA]

Option Explicit

Private Sub Workbook_Open()
Set myApp = ThisWorkbook.Application
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim WithEvents myApp As Application
Dim Val1 As Variant
Application.EnableEvents = False

If Sh.Name Like "LHE*" 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


[/VBA]

[surya prakash]

Hi Killian,
Oops just made a small correction but am getting a new error
could you have a look at my code
thanks

[VBA]
Option Explicit
Dim WithEvents myApp As Application

Private Sub myApp_NewWorkbook(ByVal Wb As Workbook)
Dim Val1 As Variant
Dim sh As Object
Application.EnableEvents = False

If sh.Name Like "LHE*" 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

Private Sub Workbook_Open()
Set myApp = ThisWorkbook.Application
End Sub

[/VBA]

[Killian]

What's the error?
I think things aer getting confused because you now using the NewWorkbook event???
You now declare sh as an ojbect but it's not set anywhere.
When using myApp_SheetChange, the sheet is passed as an argument.

[surya prakash]

Hi Killian,

changed it back to myApp_SheetChange; but nothing seems to happening when I enter data

Thanks

[VBA]
Option Explicit
Dim WithEvents myApp As Application

Private Sub Workbook_Open()
Set myApp = ThisWorkbook.Application
End Sub

Private Sub myApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Val1 As Variant
Application.EnableEvents = False

If Sh.Name Like "LHE*" 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



[/VBA]

[Killian]

Is the event firing?
to test this, set a breakpoint on the first line of the code, run the workbook_open code and change a cell value.
The code should fire and break at the breakpoint - you can then step through line by line (F8) to debug and see what's (not) happening.
Works fine for me...

[surya prakash]

Many thanks Killian, the program is working.