Checkboxes, Strings and Arrays.

**bacardi84uk** · 06-14-2017, 06:18 AM

Hi,

I'm stuck. I have three groups of varying numbers of checkboxes on a form where the user can select 6 out of each group. Each checkbox represents a string value and I plan to pass the string variable corresponding to the user's selection into a fixed size array for use later on in my code. As one checkbox is ticked, the corresponding variable should be passed to the first empty index in the array. As a checkbox is in-ticked, the associated array index is to be cleared and the user's next selection would fill that gap.

Not a clue how to go about this though!

Any suggestions would be massively appreciated.

**davesexcel** · 06-14-2017, 08:42 AM

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.

To add a file to a post

**bacardi84uk** · 06-14-2017, 09:25 AM

I can't attached a sample workbook. This is for a Solidworks 3D CAD VBA macro inserting a table into a manufacturing drawing. The principals of VBA/VSTA programming are the same but I'm not sure anyone could read the Macro without the software and a lot of the declarations are software specific. I'll tyr to attach the macro file and paste the code though. Its a WIP so is messy and needs compressing (tips on that would be appreciated too!)

Option Explicit
Dim i As Integer
Dim swApp As SldWorks.SldWorks
Dim swModel As SldWorks.ModelDoc2
Dim swDrawing As SldWorks.DrawingDoc
Dim swTable As SldWorks.TableAnnotation
Dim swCustPropMgr As SldWorks.CustomPropertyManager
Dim retval As String
Dim cb1a, cb2a, cb3a, cb4a, cb5a, cb6a, cb7a, cb8a, cb9a, cb10a, cb11a, cb12a, cb13a, cb14a, cb15a, cb16a As String
Dim cb1b, cb2b, cb3b, cb4b, cb5b, cb6b, cb7b, cb8b, cb9b, cb10b, cb11b, cb12b, cb13b, cb14b, cb15b, cb16b As String
Dim cb1c, cb2c, cb3c, cb4c, cb5c, cb6c, cb7c, cb8c, cb9c, cb10c, cb11c, cb12c, cb13c, cb14c, cb15c, cb16c As String
Dim PPE_Array(1 To 6) As String
Dim Tools_Array(1 To 6) As String
Dim MPwr_Array(1 To 6) As String

'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' Writing to PPE_Array ---------------------------------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------


'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' Writing to Tools_Array -------------------------------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------


'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' Writing to MPwr_Array --------------------------------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------


'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' CHECKBOX COUNTING (LIMITING TO 6-OFF PER GROUP) ------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------

Function CoCheckBoxCount(aCheckbox As MSForms.CheckBox) As Long
    Dim ctl As MSForms.Control
    For Each ctl In aCheckbox.Parent.Controls
        If TypeName(ctl) = "CheckBox" Then
            If ctl.Value And (ctl.GroupName = aCheckbox.GroupName) Then
                CoCheckBoxCount = CoCheckBoxCount + 1
            End If
        End If
    Next ctl
End Function

Sub LimitCheckBox(aCheckbox As MSForms.CheckBox)
    Dim maxChecked As Long
    maxChecked = 6
    If CoCheckBoxCount(aCheckbox) > maxChecked Then
        aCheckbox.Value = False
    End If
End Sub



'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' GROUP A CHECKBOXES - PPE REQUIREMENTS ----------------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------

Private Sub CheckBox1a_Click()
Call LimitCheckBox(CheckBox1a)

'Maybe use this to control if the variable is blank or not?
    If CheckBox1a.Value = True Then
       cb1a = 0
            Else
            cb1a = ""
    End If
End Sub
 
Private Sub CheckBox2a_Click()
    Call LimitCheckBox(CheckBox2a)
    cb2a = 1
End Sub

Private Sub CheckBox3a_Click()
    Call LimitCheckBox(CheckBox3a)
    cb3a = 2
End Sub

Private Sub CheckBox4a_Click()
    Call LimitCheckBox(CheckBox4a)
    cb4a = 3
End Sub

Private Sub CheckBox5a_Click()
    Call LimitCheckBox(CheckBox5a)
    cb5a = 4
End Sub

Private Sub CheckBox6a_Click()
    Call LimitCheckBox(CheckBox6a)
    cb6a = 5
End Sub

'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' GROUP B CHECKBOXES - TOOL REQUIREMENTS ---------------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------

Private Sub CheckBox1b_Click()
    Call LimitCheckBox(CheckBox1b)
 End Sub
 
Private Sub CheckBox2b_Click()
    Call LimitCheckBox(CheckBox2b)
End Sub

Private Sub CheckBox3b_Click()
    Call LimitCheckBox(CheckBox3b)
End Sub

Private Sub CheckBox4b_Click()
    Call LimitCheckBox(CheckBox4b)
End Sub

Private Sub CheckBox5b_Click()
    Call LimitCheckBox(CheckBox5b)
End Sub

Private Sub CheckBox6b_Click()
    Call LimitCheckBox(CheckBox6b)
End Sub

Private Sub CheckBox7b_Click()
    Call LimitCheckBox(CheckBox7b)
End Sub

Private Sub CheckBox8b_Click()
    Call LimitCheckBox(CheckBox8b)
End Sub

Private Sub CheckBox9b_Click()
    Call LimitCheckBox(CheckBox9b)
End Sub

Private Sub CheckBox10b_Click()
    Call LimitCheckBox(CheckBox10b)
End Sub

Private Sub CheckBox11b_Click()
    Call LimitCheckBox(CheckBox11b)
End Sub

Private Sub CheckBox12b_Click()
    Call LimitCheckBox(CheckBox12b)
End Sub

Private Sub CheckBox13b_Click()
    Call LimitCheckBox(CheckBox13b)
End Sub

Private Sub CheckBox14b_Click()
    Call LimitCheckBox(CheckBox14b)
End Sub

'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' GROUP C CHECKBOXES - MANPOWER REQUIREMENTS -----------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------

Private Sub CheckBox1c_Click()
    Call LimitCheckBox(CheckBox1c)
 End Sub
 
Private Sub CheckBox2c_Click()
    Call LimitCheckBox(CheckBox2c)
 End Sub
 
 Private Sub CheckBox3c_Click()
    Call LimitCheckBox(CheckBox3c)
 End Sub
 
 Private Sub CheckBox4c_Click()
    Call LimitCheckBox(CheckBox4c)
 End Sub
 
 Private Sub CheckBox5c_Click()
    Call LimitCheckBox(CheckBox5c)
 End Sub
 
 Private Sub CheckBox6c_Click()
    Call LimitCheckBox(CheckBox6c)
 End Sub
 
 Private Sub CheckBox7c_Click()
    Call LimitCheckBox(CheckBox7c)
 End Sub
 
 Private Sub CheckBox8c_Click()
    Call LimitCheckBox(CheckBox8c)
 End Sub
 
 Private Sub CheckBox9c_Click()
    Call LimitCheckBox(CheckBox9c)
 End Sub
  
 Private Sub CheckBox10c_Click()
    Call LimitCheckBox(CheckBox10c)
 End Sub
 
 Private Sub CheckBox11c_Click()
    Call LimitCheckBox(CheckBox11c)
 End Sub
 
 Private Sub CheckBox12c_Click()
    Call LimitCheckBox(CheckBox12c)
 End Sub



'-------------------------------------------------------------------------
'-------------------------------------------------------------------------
' OK / CANCEL BUTTONS ----------------------------------------------------
'-------------------------------------------------------------------------
'-------------------------------------------------------------------------

Private Sub cmdOK_Click()

Set swApp = Application.SldWorks
Set swModel = swApp.ActiveDoc
Set swCustPropMgr = swModel.Extension.CustomPropertyManager(Empty)
Set swDrawing = swModel

retval = swModel.AddCustomInfo3("", "A:1", swCustomInfoText, PPE_Array(1))
retval = swModel.AddCustomInfo3("", "A:2", swCustomInfoText, PPE_Array(2))
retval = swModel.AddCustomInfo3("", "A:3", swCustomInfoText, PPE_Array(3))
retval = swModel.AddCustomInfo3("", "A:4", swCustomInfoText, PPE_Array(4))
retval = swModel.AddCustomInfo3("", "A:5", swCustomInfoText, PPE_Array(5))
retval = swModel.AddCustomInfo3("", "A:6", swCustomInfoText, PPE_Array(6))

retval = swModel.AddCustomInfo3("", "B:1", swCustomInfoText, Tools_Array(1))
retval = swModel.AddCustomInfo3("", "B:2", swCustomInfoText, Tools_Array(2))
retval = swModel.AddCustomInfo3("", "B:3", swCustomInfoText, Tools_Array(3))
retval = swModel.AddCustomInfo3("", "B:4", swCustomInfoText, Tools_Array(4))
retval = swModel.AddCustomInfo3("", "B:5", swCustomInfoText, Tools_Array(5))
retval = swModel.AddCustomInfo3("", "B:6", swCustomInfoText, Tools_Array(6))

retval = swModel.AddCustomInfo3("", "C:1", swCustomInfoText, MPwr_Array(1))
retval = swModel.AddCustomInfo3("", "C:2", swCustomInfoText, MPwr_Array(2))
retval = swModel.AddCustomInfo3("", "C:3", swCustomInfoText, MPwr_Array(3))
retval = swModel.AddCustomInfo3("", "C:4", swCustomInfoText, MPwr_Array(4))
retval = swModel.AddCustomInfo3("", "C:5", swCustomInfoText, MPwr_Array(5))
retval = swModel.AddCustomInfo3("", "C:6", swCustomInfoText, MPwr_Array(6))

Call Create_Table

End

End Sub

'-------------------------------------------------------------------------

Private Sub cmdCancel_Click()

End

End Sub

'-------------------------------------------------------------------------

Private Sub Create_Table()

Set swApp = Application.SldWorks
Set swModel = swApp.ActiveDoc
Set swDrawing = swModel
Set swTable = swDrawing.InsertTableAnnotation2(False, 0.135, 0.04, swBOMConfigurationAnchor_BottomLeft, "M:\CAD Shared\97 SolidWorks Data\SW Templates\Tables\Info Table Template.sldtbt", 3, 1)

End Sub

The Strings to be passed to the arrays are "0 to 9" for PPE_Array, "A to Z" for Tools_Array and "a to z" for MPwr_Array (yes, case sensitive). The String corresponds to a character in a custom Font I created in order to display a symbol associated with that particular character.

EDIT: nope the *.swp macro file will not upload (Invalid File). Try a copy of the exported Form itself.