mardi 18 août 2015

Userform commandbutton to copy based on CheckBox True/False

Hi I am new to Excel VBA and I am stuck at below problem,

My workbook has sheet1 = Chandler_Endorsements (contains button to launch UserForm1). My Userform has 26 CheckBox and 2 CommandButton (Copy and Reset).

Each CheckBox is associated with a cell in sheet2 in the workbook. Each of the subsequent cells has a value eg. ABc, XYz, etc in a specific format.

My goal is to have the user check the CheckBox for each of the items they want to select, then select "Copy" (CommandButton1) and then they can paste those on word file. Eg. If they have checked checkbox1 & 2, cell associated with these checkboxes get selected and copied and later they paste on a word file.

Also if a checkbox is later unselected by user in between that cell should get selected and copied.

I have used below codes to give value to each checkbox:

Private Sub CheckBox1_Click()
CheckBox1.Caption = Range("A3").Value
End Sub

Private Sub CheckBox10_Click()
CheckBox10.Caption = Range("A14").Value
End Sub

And, for coping the relevant cell I am using below codes:

If CheckBox1.Value = True Then
    ActiveSheet.Range("A3").Copy
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Else: CheckBox1.Value = False
  End If

  If CheckBox2.Value = True Then
    ActiveSheet.Range("A4").Copy
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Else: CheckBox2.Value = False
  End If

But by using above codes it is not coping the every cells which I have selected (checked in checkbox). Kindly help me with the correct codes.




Aucun commentaire:

Enregistrer un commentaire