Selection is set to Nothing on second run of a AddIn function
 

Hi

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

frmInvalidNumbers.Show
rngTotal = Nothing
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

Selection is set to Nothing on second run of a AddIn function
 

does anyone have any ideas why this might occur?

"Ben Crinion" wrote in message
...
Hi

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

frmInvalidNumbers.Show
rngTotal = Nothing
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