jeudi 27 août 2015

Assign dynamic values to checkbox vba

I have created a runtime checkboxes in vba. This checkboxes are reference to excel rows :-

Equipment Finishing (painting, scratches, damage, etc) Equipment Alignment (doors, removable panels/covers, locks, section-to section, etc) Equipment / Device Labels/Nameplates and Component Layout Torque Marking Component / Device Inspection (damage to parts, missing screws, etc)

I created a form with combo box which have "GIS Swgr" "SF6 Swgr" "MC Swgr"

as list. When the user changes the item in combo box it searches in excel and automatically hides the checboxes where there is "-" in row. I am using the below code to do the same.

Const sSHEET_NAME   As String = "Hour Per Equipment"
Const FORM_TOP      As Integer = 20
Const iPITCH        As Integer = 25
Const FORM_LEFT     As Integer = 12
Const iWidth        As Integer = 350
Dim ctlCheckBox     As MSForms.Control
Dim wks             As Worksheet
Const Elect_Strt     As Integer = 29
Const Elect_End      As Integer = 65
Dim Elect_Top        As Integer
Dim iRow2           As Integer
Dim Elect_left      As Integer

col_num = cboEqpTypes.ListIndex + 2
Set wks = ThisWorkbook.Sheets(sSHEET_NAME)
Vis_Top = FORM_TOP

For iRow = Vis_Strt To Vis_End
    If wks.Cells(iRow, col_num) <> "-" Then
    Set ctlCheckBox = Me.MultiPage1.Pages(0).Controls.Add("Forms.Checkbox.1")

     With ctlCheckBox
        .Caption = Cells(iRow, "A")
        .Name = "cb" & iRow
        .Left = FORM_LEFT
        .Top = Vis_Top
        .Width = iWidth
    End With
       Vis_Top = Vis_Top + iPITCH
    End If
Next iRow    

My requirement now is that when a user selects "GIS Swgr" in combo box, it will show the checkboxes. Now I want to assign numbers to this checkboxes such that when a user checks

Equipment Finishing (painting, scratches, damage, etc) Equipment / Device Labels/Nameplates and Component Layout

then it should add "0.50" and "0.50" and return a msg box with total as "1". Similarly when a user selects "SF6 Swgr" then it should do the same with "0.1" and "0.4" and add them and return Total as msgbox "0.5" Please find the image for better understanding.

Thanks




Aucun commentaire:

Enregistrer un commentaire