Passing Information to and from Forms

D

dtshedd

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
 
G

GS

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
Click to expand...

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,
 
D

Dave Peterson

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

But it doesn't belong to a worksheet.




GS wrote:
 
D

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.
 
K

ker_01

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
 
D

Dave Peterson

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.
 
G

GS

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:
Click to expand...

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!
 
D

dtshedd

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- Hide quoted text -

- Show quoted text -
Click to expand...

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.
 
D

dtshedd

Does this mean that you got it working?

I can't tell.

dtshedd wrote:
Click to expand...

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
 
D

Dave Peterson

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to remove error from conflicting code 0
Dynamic Naming of Form Button 3
Macro to Select text from a point given to a specific word 2
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Prompt For Replace in Selection And More... 0
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
WORD VBA Search and switch to advanced ... 1
Listbox with Check Box Option?? 5

Top