My addin function wont run twice.

[Ben Crinion]

Hi

Im reposting this because i still havent fixed the problem and I have new
code to demonstrate

I have an addin application that takes a selection and strips out all
strings are not numbers, then it takes out strings which are too short of
too long and then displays the removed items in one listbox and the
remaining items in another listbox.

The code below seems to work ok but when it gets to the line where it calls
WorksheetFunction.IsText() it goes all the way back to the
AddinInstance_OnConnection method of the Connect.dsr. Why would it do this?

Another problem i am haveing with this code is that the first time i click
the button it works ok, the second time i click the button it acts as though
the selection is 'Nothing'.

I dont know if it is important to know in the context of this post but the
OnAction property of the button is set to the ProgID of the AddInInst passed
to the AddinInstance_OnConnection method.

Code is listed below.

Thanks
Ben

Friend Sub buttSendSMS_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)

On Error GoTo err_ranges
Dim rngTotal As Excel.Range
Dim cell As Excel.Range

'set range to selected cells
Set rngTotal = oXL.Selection

On Error GoTo err_listboxes

'remove strings and short/long numbers
'everything else goes is assumed to be an ok phone number
For Each cell In rngTotal.Cells
If WorksheetFunction.IsText(cell.Value) Then
frmInvalidNumbers.lstText.AddItem cell.Value
ElseIf Len(cell.Value) < 8 Or Len(cell.Value) > 15 Then
frmInvalidNumbers.lstText.AddItem cell.Value
Else
frmSendSMS.lstNumbers.AddItem cell.Value
End If
Next cell

If frmInvalidNumbers.lstText.ListCount > 0 Then
frmInvalidNumbers.Show
Else
frmSendSMS.Show
End If

rngTotal.Select

Exit Sub
err_ranges:
If Err.Number = 1004 Then
Resume Next
Else
MsgBox Err.Number & " " & Err.Description & ": occured when setting
ranges in clsAddIn.buttSendSMS_Click"
Resume Next
End If
err_listboxes:
MsgBox Err.Number & " " & Err.Description & ": occured adding when
spliting valid from in-invalid numbers"
Resume Next
End Sub

 

[keepITcool]

Ben..

The worksheetfunction is a method of Excel's application
object.
when i normally code it i ALWAYS precede it with
the application qualifier as for instance xl97
wont accept if without)

By not specifying it in your code it may just inadvertently
create another application instance..

so rewrite your line to
assuming you've defined and set oXL as application object then
for REAL worksheetfunctions rewrite like:

oXL.worksheetfunction.


HOWEVER do you really need Excel's ISTEXT function?
why not use :
if Not isnumeric(cell.value)







keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Ben Crinion]

Thanks

I knew there would be a VB function somewhere that did that but i found the
worksheet function first.

You dont know about my other problem do you?

 

[Ben Crinion]

Actually the one fix solved both problems.

For anyone whos is interesed in the future calling the worksheetfunction
without preceeding the application instance before it does create another
instance of the addin. This in turn changed my oXL object and made it refer
to some undefined thing, so when i accessed oXL.selection it was set to
nothing.

Thaks