How do i select/activate multiple sheets to transfer Userform data into cells?

1
votes

I have checkboxes in Userform and depdning on which checkboxes are selected i want to select/activate excel sheets corresponding to the checkboxes.

Ex. Checkbox A, B, C is clicked i want to select/activate tabs A,B,C so i can trasnfer information to those sheets. I know how to trasnfer data but i'm unsure how to select multiple sheets given the condition of checkboxes.

If A_Checkbox.value = True Then
Cells(emptyRow, 1).value=NOD_Text.value

but the problem is i have about 8 checkboxes and i'm unsure how to transfer data into multiple sheets depending on which checkboxes are clicked...

Is there a function where I can say "If any checkboxes values are true then transfer userform data into corresponding sheets?

so i've used the code from the response but i can't seem to get it to work? (i'm not very familiar with vba..sorry...)

Private Sub Add_Button_Click ()
    Dim ctrl As Control
    Dim emptyRow As Long
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

For Each ctrl In UserForm1.Controls
      If TypeName(ctrl) = "Checkbox" Then
    Transfervalues ctrl, emptyRow
End If
Next
End Sub

Function Transfervalues(cb As MSForms.CheckBox, emptyRow As Long)
Dim ws As Worksheet

     If cb Then
        Select Case cb.Name
            Case "A"
               Sheets("A").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("A").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("A").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("A").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("A").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("A").Cells(emptyRow, 6).Value = CPN_Text.Value
           Case "B"
               Sheets("B").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("B").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("B").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("B").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("B").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("B").Cells(emptyRow, 6).Value = CPN_Text.Value
          Case "C"
               Sheets("C").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("C").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("C").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("C").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("C").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("C").Cells(emptyRow, 6).Value = CPN_Text.Value
          Case "D"
               Sheets("D").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("D").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("D").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("D").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("D").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("D").Cells(emptyRow, 6).Value = CPN_Text.Value
          Case "E"
               Sheets("E").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("E").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("E").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("E").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("E").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("E").Cells(emptyRow, 6).Value = CPN_Text.Value
          Case "F"
               Sheets("F").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("F").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("F").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("F").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("F").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("F").Cells(emptyRow, 6).Value = CPN_Text.Value
          Case "G"
               Sheets("G").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("G").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("G").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("G").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("G").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("G").Cells(emptyRow, 6).Value = CPN_Text.Value
          Case "H"
               Sheets("H").Cells(emptyRow, 1).Value = NOD_Text.Value
               Sheets("H").Cells(emptyRow, 2).Value = TOD_Text.Value
               Sheets("H").Cells(emptyRow, 3).Value = Program_Text.Value
               Sheets("H").Cells(emptyRow, 4).Value = email_Text.Value
               Sheets("H").Cells(emptyRow, 5).Value = OPN_Text.Value
               Sheets("H").Cells(emptyRow, 6).Value = CPN_Text.Value
     End Select
End If

End Function
2
vbaexceluserform
Try Select Case Left(cb.Name, 1). Also I will make some revisions to mine based on what you have done. – David Zemens

2 Answers

3
votes

Assuming your checkbox objects named A_Checkbox, B_Checkbox, etc., correspond to worksheets named exactly like "A", "B", etc., then something like:

Private Sub Add_Button_Click()
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "CheckBox" Then
        'Pass this CheckBox to the subroutine below:
        TransferValues ctrl
    End If
Next
End Sub

REVISED

It looks like you are dumping the same data from the user form to each sheet, based on the check box selection(s). You won't need a case select statement for this, just define a worksheet variable based on the CheckBox.Name. Note that I changed this from a Function to a Sub although that shouldn't matter really. I also change this so the value of emptyRow is calculated each time, since that will change depending on what worksheet you are acting on.

Sub TransferValues(cb As MSForms.CheckBox)
Dim ws As Worksheet
Dim emptyRow as Long

    If cb Then
       'Define the worksheet based on the CheckBox.Name property:
        Set ws = Sheets(Left(cb.Name, 1))
        emptyRow = WorksheetFunction.CountA(ws.Range("A:A")) + 1
           With ws
               .Cells(emptyRow, 1).Value = NOD_Text.Value
               .Cells(emptyRow, 2).Value = TOD_Text.Value
               .Cells(emptyRow, 3).Value = Program_Text.Value
               .Cells(emptyRow, 4).Value = email_Text.Value
               .Cells(emptyRow, 5).Value = OPN_Text.Value
               .Cells(emptyRow, 6).Value = CPN_Text.Value
           End With
    End If

End Sub

Edit to clarify based on comments from OP

TypeName is a built-in method that returns a string that identifies the type of object. In this case, we iterate over all controls on the user form, so you need some logic to make sure the function only operates on the CheckBox controls.

cb is a variable local to the TransferValues subroutine. In the calling subroutine (CommandButton1_Click in my example), we send the object ctrl (a CheckBox control) to this subroutine.

The boolean statement If cb simply evaluates whether the checkbox has been checked. You could do If cb.Value = True but my personal preference is to simplify it.

UPDATED & TESTED

Here is a Before picture with an example userform containing three checkboxes and a few dummy textboxes:

enter image description here

Now here is the worksheet "C" after I have pressed the "Add" button:

enter image description here

And finally, I can continue to change the textbox values and press the add button over and over again, like so:

enter image description here

0
votes

Big thanks to David Zemens!

I had to make a slight modification to his code because I couldn't paste across multiple sheets based on checkbox selection.

See modified code below - now I can select any of my checkboxes and the save button pastes into the emptyrow in every sheet.

Private Sub    
Dim cb As Control    
Dim ws As Worksheet    
Dim emptyRow As Long

For Each cb In UserForm3.Controls

    If TypeName(cb) = "CheckBox" Then
        'Pass this CheckBox to the subroutine below:
        If cb Then
        Set ws = Sheets(Left(cb.Name, 2))
        emptyRow = (WorksheetFunction.CountA(ws.Range("A7:A5000")) + 6) + 1
           With ws
               .Cells(emptyRow, 1).Value = TextBox1.Value
               .Cells(emptyRow, 2).Value = TextBox2.Value
               .Cells(emptyRow, 3).Value = TextBox3.Value
               .Cells(emptyRow, 4).Value = TextBox6.Value
               .Cells(emptyRow, 5).Value = TextBox4.Value
               .Cells(emptyRow, 6).Value = TextBox5.Value
           End With
        End If        
    End If
Next cb

Unload UserForm3    
UserForm2.Show    
End Sub

enter image description here