lundi 19 octobre 2015

Checkbox / Cell link with multiple userforms

I am having difficulties understanding the VBA methods of coding (I only have R experience). I am trying to link the value of a userform checkbox to a specific cell in a worksheet using VBA.

Sub x()
    Dim Range As Range

    If userfrom.checkbox1.Value = True Then
        Range.Offset(0, 7).Value = 1
    Else
        Range.Offset(0, 7).Value = ""
    End If


    If userform.checkbox2.Value = True Then
        Range.Offset(0, 8).Value = 1
    Else
        Range.Offset(0, 8).Value = ""
    End If


    If userform.checkbox3.Value = True Then
        Range.Offset(0, 9).Value = 1
    Else
        Range.Offset(0, 9).Value = ""
    End If

    Unload userform

End Sub

I have come across the method of allocating checkboxes to arrays, but am unable to understand the example in the threads. Ideally, what I imagine doing is to declare an array of check boxes as well as an array of cells - and link each element in each array together, i.e. checkboxArray(1) -> cellArray(1); checkboxArray(2) -> cellArray(2); etc.

A colleague of mine examined my code and said that it can be made better, i.e. completed in one line.

Is this achievable?




Aucun commentaire:

Enregistrer un commentaire