CheckBox values

A

Allan

I have created a user dialog form with three tabs. Each of
the tabs list on of 20 different companies for which a
user can print a financial statement (P&L, Bal Sht, CF).
Therefore, by design, I have 60 different checkboxes. Each
check box is named in accordance with the company name and
the statement type (IE: plComp1cb, bsComp1cb...).

To print the selection of reports, I am using arrays for
each of the variables. Rather than list each of the 60
checkboxes I though I might be able to concatenate the
name, then attain the value for that check box. therefore,
I have two arrays that would merge to create a checkbox
name:

Array1(pl, bs, cf)
Array2(Comp1, Comp2, Comp3...)

In short, if I were to use loops and if statements to
check the value (true / false), I should be able to attain
the value of each check box, ascertain the value and print
(or skip printing). I have not been able to determine the
correct syntax to check the checkbox. I have done similar
techniques using worksheets - sheet(array1 & array2).value
but have been unsuccessful in using the same technique for
checkboxers - checkbox(array1 & array2).value

Any suggestions?

Allan
 
D

Dave Peterson

Maybe just split the checkbox name (I put the code under a button on the
userform):

Option Explicit
Private Sub CommandButton1_Click()

Dim ctrl As MSForms.Control
Dim rptName As String
Dim pfxType As String

For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Object.Value = True Then
pfxType = LCase(Left(ctrl.Name, 2))
rptName = Mid(ctrl.Name, 3, Len(ctrl.Name) - 4)
Select Case pfxType
Case Is = "pl": Call doPL(rptName)
Case Is = "bs": Call doBS(rptName)
Case Is = "cf": Call doCF(rptName)
Case Else: MsgBox "design error!"
End Select

End If
End If
Next ctrl
End Sub


Then in your general module, do the work:

Option Explicit
Sub doPL(myRpt As String)
MsgBox myRpt
End Sub
Sub doBS(myRpt As String)
MsgBox myRpt
End Sub
Sub doCF(myRpt As String)
MsgBox myRpt
End Sub
 

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

Top