My goal; have the user select choices on a page of checkboxes and
capture the labels for those boxes and concatenate them into one large
string.  When I use brute force like multiple if then statements in
the property definition behind the form to check the value of a each
checkbox by name for true/false  ala

 if chkBoxPrimary=True then MyBins=MyBins+"Primary"

then everything works.  Problem is i have 50 checkboxes so I want to
capture the caption and add that to my string.  so I cycle thru the
checkboxes and if they are true I add the caption.  unfortunately i
get an error


' object doesn't support the property or method'

the offending line the regular code module is

Application.ActiveCell = .MyBins

I have followed the very informative tutorial at
http://peltiertech.com/Excel/PropertyProcedures.html to pass
information from a form.  The following is in a regular code module

Option Base 1

Sub GetRecipients()

Dim frmGetBins As FGetBins

'start up the form

Set frmGetBins = New FGetBins

With frmGetBins

'show the form
 .Show

'get new value back from the form
Application.ActiveCell = .MyBins

End With

'got the information, now close the form Unload frmGetBins

End Sub


Here is the code behind the form:

Public Property Get MyBins() As String

Dim c As Control

For Each c In Me.Controls

If TypeName(c) = "CheckBox" And c.Value = True Then MyBins = MyBins +
Trim(c.Caption.Text) +" " End If

Next

End Property

Private Sub CommandButtonFinished Click()

Me.Hide

End Sub



Private Sub CommandButtonReset Click()

Dim c As Control

For Each c In Me.Controls

If TypeName(c) = "CheckBox" Then c.Value = False

End If

Next

End Sub



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)

If CloseMode = 0 Then

, user clicked the X button

, cancel unloading the form, use close button procedure instead Cancel
= True

CommandButtonFinished Click

End If

End Sub



any help appreciated

dtshedd expressed precisely :
> My goal; have the user select choices on a page of checkboxes and
> capture the labels for those boxes and concatenate them into one large
> string.  When I use brute force like multiple if then statements in
> the property definition behind the form to check the value of a each
> checkbox by name for true/false  ala
>
>  if chkBoxPrimary=True then MyBins=MyBins+"Primary"
>
> then everything works.  Problem is i have 50 checkboxes so I want to
> capture the caption and add that to my string.  so I cycle thru the
> checkboxes and if they are true I add the caption.  unfortunately i
> get an error
>
>
> ' object doesn't support the property or method'
>
> the offending line the regular code module is
>
> Application.ActiveCell = .MyBins
>
> I have followed the very informative tutorial at
> http://peltiertech.com/Excel/PropertyProcedures.html to pass
> information from a form.  The following is in a regular code module
>
> Option Base 1
>
> Sub GetRecipients()
>
> Dim frmGetBins As FGetBins
>
> 'start up the form
>
> Set frmGetBins = New FGetBins
>
> With frmGetBins
>
> 'show the form
>  .Show
>
> 'get new value back from the form
> Application.ActiveCell = .MyBins
>
> End With
>
> 'got the information, now close the form Unload frmGetBins
>
> End Sub
>
>
> Here is the code behind the form:
>
> Public Property Get MyBins() As String
>
> Dim c As Control
>
> For Each c In Me.Controls
>
> If TypeName(c) = "CheckBox" And c.Value = True Then MyBins = MyBins +
> Trim(c.Caption.Text) +" " End If
>
> Next
>
> End Property
>
> Private Sub CommandButtonFinished Click()
>
> Me.Hide
>
> End Sub
>
>
>
> Private Sub CommandButtonReset Click()
>
> Dim c As Control
>
> For Each c In Me.Controls
>
> If TypeName(c) = "CheckBox" Then c.Value = False
>
> End If
>
> Next
>
> End Sub
>
>
>
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
> Integer)
>
> If CloseMode = 0 Then
>
> , user clicked the X button
>
> , cancel unloading the form, use close button procedure instead Cancel
> = True
>
> CommandButtonFinished Click
>
> End If
>
> End Sub
>
>
>
> any help appreciated

ActiveCell is a property of the Worksheet object, NOT the Application 
object. If the cell is on the ActiveWorksheet then you can use:

  ActiveCell = ...

Otherwise, specify the worksheet the active cell is on:

  Sheets("SheetName").ActiveCell = ...

regards,

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Actually, activecell can apply to either a window or the application.

But it doesn't belong to a worksheet.




GS wrote:
<<snipped>>
> 
> ActiveCell is a property of the Worksheet object, NOT the Application
> object. If the cell is on the ActiveWorksheet then you can use:
> 
>   ActiveCell = ...
> 
> Otherwise, specify the worksheet the active cell is on:
> 
>   Sheets("SheetName").ActiveCell = ...
> 
> regards,
> 
> --
> Garry
> 
> Free usenet access at http://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

-- 

Dave Peterson

Public Property Get MyBins() As String
    Dim c As Control
    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" _
            And c.Value = True Then
                MyBins = MyBins & Trim(c.Caption) & " "
        End If
    Next c
End Property

The .caption is the property you want.  It doesn't have a deeper .text property.



dtshedd wrote:
> 
> My goal; have the user select choices on a page of checkboxes and
> capture the labels for those boxes and concatenate them into one large
> string.  When I use brute force like multiple if then statements in
> the property definition behind the form to check the value of a each
> checkbox by name for true/false  ala
> 
>  if chkBoxPrimary=True then MyBins=MyBins+"Primary"
> 
> then everything works.  Problem is i have 50 checkboxes so I want to
> capture the caption and add that to my string.  so I cycle thru the
> checkboxes and if they are true I add the caption.  unfortunately i
> get an error
> 
> ' object doesn't support the property or method'
> 
> the offending line the regular code module is
> 
> Application.ActiveCell = .MyBins
> 
> I have followed the very informative tutorial at
> http://peltiertech.com/Excel/PropertyProcedures.html to pass
> information from a form.  The following is in a regular code module
> 
> Option Base 1
> 
> Sub GetRecipients()
> 
> Dim frmGetBins As FGetBins
> 
> 'start up the form
> 
> Set frmGetBins = New FGetBins
> 
> With frmGetBins
> 
> 'show the form
>  .Show
> 
> 'get new value back from the form
> Application.ActiveCell = .MyBins
> 
> End With
> 
> 'got the information, now close the form Unload frmGetBins
> 
> End Sub
> 
> Here is the code behind the form:
> 
> Public Property Get MyBins() As String
> 
> Dim c As Control
> 
> For Each c In Me.Controls
> 
> If TypeName(c) = "CheckBox" And c.Value = True Then MyBins = MyBins +
> Trim(c.Caption.Text) +" " End If
> 
> Next
> 
> End Property
> 
> Private Sub CommandButtonFinished Click()
> 
> Me.Hide
> 
> End Sub
> 
> Private Sub CommandButtonReset Click()
> 
> Dim c As Control
> 
> For Each c In Me.Controls
> 
> If TypeName(c) = "CheckBox" Then c.Value = False
> 
> End If
> 
> Next
> 
> End Sub
> 
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
> Integer)
> 
> If CloseMode = 0 Then
> 
> , user clicked the X button
> 
> , cancel unloading the form, use close button procedure instead Cancel
> = True
> 
> CommandButtonFinished Click
> 
> End If
> 
> End Sub
> 
> any help appreciated

-- 

Dave Peterson

Without going through all your code, the first thing that strikes me is that 
I would expect the cell reference to be something like the following if you 
are trying to assign a text string:

Application.ActiveCell.value = .MyBins
or
Application.ActiveCell.text = .MyBins

HTH,
Keith

"dtshedd" wrote:

> My goal; have the user select choices on a page of checkboxes and
> capture the labels for those boxes and concatenate them into one large
> string.  When I use brute force like multiple if then statements in
> the property definition behind the form to check the value of a each
> checkbox by name for true/false  ala
> 
>  if chkBoxPrimary=True then MyBins=MyBins+"Primary"
> 
> then everything works.  Problem is i have 50 checkboxes so I want to
> capture the caption and add that to my string.  so I cycle thru the
> checkboxes and if they are true I add the caption.  unfortunately i
> get an error
> 
> 
> ' object doesn't support the property or method'
> 
> the offending line the regular code module is
> 
> Application.ActiveCell = .MyBins
> 
> I have followed the very informative tutorial at
> http://peltiertech.com/Excel/PropertyProcedures.html to pass
> information from a form.  The following is in a regular code module
> 
> Option Base 1
> 
> Sub GetRecipients()
> 
> Dim frmGetBins As FGetBins
> 
> 'start up the form
> 
> Set frmGetBins = New FGetBins
> 
> With frmGetBins
> 
> 'show the form
>  .Show
> 
> 'get new value back from the form
> Application.ActiveCell = .MyBins
> 
> End With
> 
> 'got the information, now close the form Unload frmGetBins
> 
> End Sub
> 
> 
> Here is the code behind the form:
> 
> Public Property Get MyBins() As String
> 
> Dim c As Control
> 
> For Each c In Me.Controls
> 
> If TypeName(c) = "CheckBox" And c.Value = True Then MyBins = MyBins +
> Trim(c.Caption.Text) +" " End If
> 
> Next
> 
> End Property
> 
> Private Sub CommandButtonFinished Click()
> 
> Me.Hide
> 
> End Sub
> 
> 
> 
> Private Sub CommandButtonReset Click()
> 
> Dim c As Control
> 
> For Each c In Me.Controls
> 
> If TypeName(c) = "CheckBox" Then c.Value = False
> 
> End If
> 
> Next
> 
> End Sub
> 
> 
> 
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
> Integer)
> 
> If CloseMode = 0 Then
> 
> , user clicked the X button
> 
> , cancel unloading the form, use close button procedure instead Cancel
> = True
> 
> CommandButtonFinished Click
> 
> End If
> 
> End Sub
> 
> 
> 
> any help appreciated
> .
> 

Ps.  This was the complete code:

In a General module:

Option Explicit
Option Base 1
Sub GetRecipients()
    Dim frmGetBins As fGetBins
    'start up the form
    Set frmGetBins = New fGetBins
    With frmGetBins
        'show the form
         .Show
        'get new value back from the form
        Application.ActiveCell = .MyBins
        'Unload
    End With
    Unload frmGetBins
End Sub


And behind the userform named fGetBins:

Option Explicit
Public Property Get MyBins() As String
    Dim c As Control
    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" _
            And c.Value = True Then
                MyBins = MyBins & Trim(c.Caption) & " "
        End If
    Next c
End Property
Private Sub CommandButtonFinished_Click()
    Me.Hide
End Sub
Private Sub CommandButtonReset_Click()
    Dim c As Control
        For Each c In Me.Controls
            If TypeName(c) = "CheckBox" Then c.Value = False
        End If
    Next c
End Sub

It's better to use the & operator to concatenate strings.  VBA will sometimes
let + work, but if the strings look like numbers, you may find that the results
are not what you expect.

Dave Peterson wrote:
> 
> Public Property Get MyBins() As String
>     Dim c As Control
>     For Each c In Me.Controls
>         If TypeName(c) = "CheckBox" _
>             And c.Value = True Then
>                 MyBins = MyBins & Trim(c.Caption) & " "
>         End If
>     Next c
> End Property
> 
> The .caption is the property you want.  It doesn't have a deeper .text property.
> 
> dtshedd wrote:
> >
> > My goal; have the user select choices on a page of checkboxes and
> > capture the labels for those boxes and concatenate them into one large
> > string.  When I use brute force like multiple if then statements in
> > the property definition behind the form to check the value of a each
> > checkbox by name for true/false  ala
> >
> >  if chkBoxPrimary=True then MyBins=MyBins+"Primary"
> >
> > then everything works.  Problem is i have 50 checkboxes so I want to
> > capture the caption and add that to my string.  so I cycle thru the
> > checkboxes and if they are true I add the caption.  unfortunately i
> > get an error
> >
> > ' object doesn't support the property or method'
> >
> > the offending line the regular code module is
> >
> > Application.ActiveCell = .MyBins
> >
> > I have followed the very informative tutorial at
> > http://peltiertech.com/Excel/PropertyProcedures.html to pass
> > information from a form.  The following is in a regular code module
> >
> > Option Base 1
> >
> > Sub GetRecipients()
> >
> > Dim frmGetBins As FGetBins
> >
> > 'start up the form
> >
> > Set frmGetBins = New FGetBins
> >
> > With frmGetBins
> >
> > 'show the form
> >  .Show
> >
> > 'get new value back from the form
> > Application.ActiveCell = .MyBins
> >
> > End With
> >
> > 'got the information, now close the form Unload frmGetBins
> >
> > End Sub
> >
> > Here is the code behind the form:
> >
> > Public Property Get MyBins() As String
> >
> > Dim c As Control
> >
> > For Each c In Me.Controls
> >
> > If TypeName(c) = "CheckBox" And c.Value = True Then MyBins = MyBins +
> > Trim(c.Caption.Text) +" " End If
> >
> > Next
> >
> > End Property
> >
> > Private Sub CommandButtonFinished Click()
> >
> > Me.Hide
> >
> > End Sub
> >
> > Private Sub CommandButtonReset Click()
> >
> > Dim c As Control
> >
> > For Each c In Me.Controls
> >
> > If TypeName(c) = "CheckBox" Then c.Value = False
> >
> > End If
> >
> > Next
> >
> > End Sub
> >
> > Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
> > Integer)
> >
> > If CloseMode = 0 Then
> >
> > , user clicked the X button
> >
> > , cancel unloading the form, use close button procedure instead Cancel
> > = True
> >
> > CommandButtonFinished Click
> >
> > End If
> >
> > End Sub
> >
> > any help appreciated
> 
> --
> 
> Dave Peterson

-- 

Dave Peterson

Dave Peterson wrote on 6/1/2010 :
> Actually, activecell can apply to either a window or the application.
>
> But it doesn't belong to a worksheet.
>
>
>
>
> GS wrote:
> <<snipped>>
>> 
>> ActiveCell is a property of the Worksheet object, NOT the Application
>> object. If the cell is on the ActiveWorksheet then you can use:
>> 
>>   ActiveCell = ...
>> 
>> Otherwise, specify the worksheet the active cell is on:
>> 
>>   Sheets("SheetName").ActiveCell = ...
>> 
>> regards,
>> 
>> --
>> Garry
>> 
>> Free usenet access at http://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks! Actually, you're rightt and I stand corrected. It's been a 
rather long time since I've used it and so I responded too quickly to 
this thread. My bad!

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 1, 8:11=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Ps. =A0This was the complete code:
>
> In a General module:
>
> Option Explicit
> Option Base 1
> Sub GetRecipients()
> =A0 =A0 Dim frmGetBins As fGetBins
> =A0 =A0 'start up theform
> =A0 =A0 Set frmGetBins =3D New fGetBins
> =A0 =A0 With frmGetBins
> =A0 =A0 =A0 =A0 'show theform
> =A0 =A0 =A0 =A0 =A0.Show
> =A0 =A0 =A0 =A0 'get new value back from theform
> =A0 =A0 =A0 =A0 Application.ActiveCell =3D .MyBins
> =A0 =A0 =A0 =A0 'Unload
> =A0 =A0 End With
> =A0 =A0 Unload frmGetBins
> End Sub
>
> And behind the userform named fGetBins:
>
> Option Explicit
> Public Property Get MyBins() As String
> =A0 =A0 Dim c As Control
> =A0 =A0 For Each c In Me.Controls
> =A0 =A0 =A0 =A0 If TypeName(c) =3D "CheckBox" _
> =A0 =A0 =A0 =A0 =A0 =A0 And c.Value =3D True Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MyBins =3D MyBins & Trim(c.Caption) & " "
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 Next c
> End Property
> Private Sub CommandButtonFinished_Click()
> =A0 =A0 Me.Hide
> End Sub
> Private Sub CommandButtonReset_Click()
> =A0 =A0 Dim c As Control
> =A0 =A0 =A0 =A0 For Each c In Me.Controls
> =A0 =A0 =A0 =A0 =A0 =A0 If TypeName(c) =3D "CheckBox" Then c.Value =3D Fa=
lse
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 Next c
> End Sub
>
> It's better to use the & operator to concatenate strings. =A0VBA will som=
etimes
> let + work, but if the strings look like numbers, you may find that the r=
esults
> are not what you expect.
>
>
>
>
>
> Dave Peterson wrote:
>
> > Public Property Get MyBins() As String
> > =A0 =A0 Dim c As Control
> > =A0 =A0 For Each c In Me.Controls
> > =A0 =A0 =A0 =A0 If TypeName(c) =3D "CheckBox" _
> > =A0 =A0 =A0 =A0 =A0 =A0 And c.Value =3D True Then
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MyBins =3D MyBins & Trim(c.Caption) & "=
 "
> > =A0 =A0 =A0 =A0 End If
> > =A0 =A0 Next c
> > End Property
>
> > The .caption is the property you want. =A0It doesn't have a deeper .tex=
t property.
>
> > dtshedd wrote:
>
> > > My goal; have the user select choices on a page of checkboxes and
> > > capture the labels for those boxes and concatenate them into one larg=
e
> > > string. =A0When I use brute force like multiple if then statements in
> > > the property definition behind theformto check the value of a each
> > > checkbox by name for true/false =A0ala
>
> > > =A0if chkBoxPrimary=3DTrue then MyBins=3DMyBins+"Primary"
>
> > > then everything works. =A0Problem is i have 50 checkboxes so I want t=
o
> > > capture the caption and add that to my string. =A0so I cycle thru the
> > > checkboxes and if they are true I add the caption. =A0unfortunately i
> > > get an error
>
> > > ' object doesn't support the property or method'
>
> > > the offending line the regular code module is
>
> > > Application.ActiveCell =3D .MyBins
>
> > > I have followed the very informative tutorial at
> > >http://peltiertech.com/Excel/PropertyProcedures.htmlto pass
> > >informationfrom aform. =A0The following is in a regular code module
>
> > > Option Base 1
>
> > > Sub GetRecipients()
>
> > > Dim frmGetBins As FGetBins
>
> > > 'start up theform
>
> > > Set frmGetBins =3D New FGetBins
>
> > > With frmGetBins
>
> > > 'show theform
> > > =A0.Show
>
> > > 'get new value back from theform
> > > Application.ActiveCell =3D .MyBins
>
> > > End With
>
> > > 'got theinformation, now close theformUnload frmGetBins
>
> > > End Sub
>
> > > Here is the code behind theform:
>
> > > Public Property Get MyBins() As String
>
> > > Dim c As Control
>
> > > For Each c In Me.Controls
>
> > > If TypeName(c) =3D "CheckBox" And c.Value =3D True Then MyBins =3D My=
Bins +
> > > Trim(c.Caption.Text) +" " End If
>
> > > Next
>
> > > End Property
>
> > > Private Sub CommandButtonFinished Click()
>
> > > Me.Hide
>
> > > End Sub
>
> > > Private Sub CommandButtonReset Click()
>
> > > Dim c As Control
>
> > > For Each c In Me.Controls
>
> > > If TypeName(c) =3D "CheckBox" Then c.Value =3D False
>
> > > End If
>
> > > Next
>
> > > End Sub
>
> > > Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
> > > Integer)
>
> > > If CloseMode =3D 0 Then
>
> > > , user clicked the X button
>
> > > , cancel unloading theform, use close button procedure instead Cancel
> > > =3D True
>
> > > CommandButtonFinished Click
>
> > > End If
>
> > > End Sub
>
> > > any help appreciated
>
> > --
>
> > Dave Peterson
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Thanks everyone for the help.  Acrtually I started using c.Caption
without ".Text " but this  did not work either probably because I used
a "+" concatenation  operator.

Does this mean that you got it working?

I can't tell.

dtshedd wrote:
<<snipped>> 
> Thanks everyone for the help.  Acrtually I started using c.Caption
> without ".Text " but this  did not work either probably because I used
> a "+" concatenation  operator.

-- 

Dave Peterson