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*pm, Dave Peterson wrote:
Ps. *This was the complete code:

In a General module:

Option Explicit
Option Base 1
Sub GetRecipients()
* * Dim frmGetBins As fGetBins
* * 'start up theform
* * Set frmGetBins = New fGetBins
* * With frmGetBins
* * * * 'show theform
* * * * *.Show
* * * * 'get new value back from theform
* * * * 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 theformto 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.htmlto pass
informationfrom aform. *The following is in a regular code module

Option Base 1

Sub GetRecipients()

Dim frmGetBins As FGetBins

'start up theform

Set frmGetBins = New FGetBins

With frmGetBins

'show theform
*.Show

'get new value back from theform
Application.ActiveCell = .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) = "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 theform, use close button procedure instead Cancel
= 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

On Jun 2, 5:00*pm, Dave Peterson wrote:
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

Sorry for not responding sooner, I had to wait to go back to work to
try it. Unfortunately these changes did not fix the problem.

while in debug stepping thru the code behind the form, everything
works and using the Application.WorksheetFunction.IsText function i
confirm that the variable mybins is text, however the minute control
is returned to the regular code module the string is null and it fails
the istext test.

out of ideas

I think it's time to post your current code.

And you did try that suggestion in the other message that contained the more
complete code, right?

On 06/07/2010 18:18, dtshedd wrote:
<<snipped

Sorry for not responding sooner, I had to wait to go back to work to
try it. Unfortunately these changes did not fix the problem.

while in debug stepping thru the code behind the form, everything
works and using the Application.WorksheetFunction.IsText function i
confirm that the variable mybins is text, however the minute control
is returned to the regular code module the string is null and it fails
the istext test.

out of ideas

--
Dave Peterson