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

Ben,

Are you referencing your forms directly? In your code I read references
to: frmInvalidNumbers and frmSendSMS

But I cannot determine if these are (Public) variables, or
direct references to the Project's components.

If it's the latter.. then that might be your problem.


Public ufINV as frmInvalidNumbers
Public ufSMS as frmSendSMS


On click
if ufINV IS nothing then
Set ufINV = new frmInvalidNumbers
else
ufinv.lstText.clear
endif

ufINV.LstText.additem etc..

ufINV.Show vbmodeless



keepITcool

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


"Ben Crinion" wrote:

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