Consulting

Results 1 to 10 of 10

Thread: Using a Userform with Multilple Checkboxes to complete a Range of Cells

  1. #1

    Using a Userform with Multilple Checkboxes to complete a Range of Cells

    Hi folks, as per usual, I don't use excel for ages, then I need to do something and have completely forgotten everything

    I have designed the userform already and it has multiple checkboxes on it. When I click the OK button I want it to do the following

    I need 2/3 things.


    Firstly - When column “O” is selected onany given row, then the userform pops up and asks to be completed

    - The userform has a bunch of checkboxbuttons on it which the user will select any number of these. (Lets call themrejection reasons)

    What I need is - for instance, ifuser selects checkbox button 1 then the corresponding cell in column “P” in the same rowis input with a “yes” when the ok button is clicked. If ts not ticked then "NO" should be added. There are approx. 25 checkboxes, and the column needing completed will move along for each checkbox. (ie button 2 - column q, button 3 - column r and so o

    – the ok/cancel commandbuttons are called (ok) “All RejectsAdded” and (cancel) “No Reject Reasons”. If “no Reject Reasons” is selectedthen for instance the corresponding columns “P” to “AA” are populated with “NO”

    thanks

  2. #2
    I was trying this - but its just giving an error.

    Private Sub CheckBox1_Click()
        Sub Worksheet_Change(ByVal Target As Excel.Range)
            With Target
                If CheckBox1.Value = False Then Exit Sub
                If Not Intersect(Range("O5:O50000"), .Cells) Is Nothing Then
                    Application.EnableEvents = False
                    If IsEmpty(.Value) Then
                        .Offset(0, 1).ClearContents
                    Else
                        With .Offset(0, 1)
                            .Value = True
                        End With
                    End If
                Application.EnableEvents = True
                End If
            End If
            End With
        End Sub
    End Sub

  3. #3

  4. #4
    VBAX Regular
    Joined
    Jan 2018
    Location
    The Netherlands
    Posts
    45
    Location
    Wich error, post your xlsm here

  5. #5
    WIth that code there, it gives an "compile error - expected End Sub" error message.

    I'll get a blank version of the sheet when I get a few minutes in the morning.

  6. #6
    VBAX Regular
    Joined
    Jan 2018
    Location
    The Netherlands
    Posts
    45
    Location
    Quote Originally Posted by hmltnangel View Post
    WIth that code there, it gives an "compile error - expected End Sub" error message.

    I'll get a blank version of the sheet when I get a few minutes in the morning.

  7. #7
    VBAX Regular
    Joined
    Jan 2018
    Location
    The Netherlands
    Posts
    45
    Location
    At First I See Two times End Sub, thats one to much.

  8. #8
    I have been trying to get it to work, and think I'm closer to success, but still not getting there. Anyone got any suggestions? This is where I got to so far, trying to get it to work for one cell first.

    However, I get a Compile error. "Argument not optional" and it highlights the call "Worksheet_change1" line.

    Sub ShowUserForm()
     UserForm1.Show
     End Sub
      
     Sub Worksheet_Change1(ByVal Target As Excel.Range)
     If RejectReasons.CheckBox1.Value = True Then
             With Target
                 If CheckBox1.Value = False Then Exit Sub
                     If Not Intersect(Range("P5:P50000"), .Cells) Is Nothing Then
                         Application.EnableEvents = False
                         If IsEmpty(.Value) Then
                             .Offset(0, 1).ClearContents
                         Else
                             With .Offset(0, 1)
                                 .Value = True
                             End With
                         End If
                         Application.EnableEvents = True
                     End If
                 End With
     End If
     End Sub
    
     Private Sub CommandButton1_Click()
     Call Worksheet_Change1
     End Sub

  9. #9
    VBAX Regular
    Joined
    Jan 2018
    Location
    The Netherlands
    Posts
    45
    Location
    Quote Originally Posted by hmltnangel View Post
    I have been trying to get it to work, and think I'm closer to success, but still not getting there. Anyone got any suggestions? This is where I got to so far, trying to get it to work for one cell first.

    However, I get a Compile error. "Argument not optional" and it highlights the call "Worksheet_change1" line.

    Sub ShowUserForm()
     UserForm1.Show
     End Sub
      
     Sub Worksheet_Change1(ByVal Target As Excel.Range)
     If RejectReasons.CheckBox1.Value = True Then
             With Target
                 If CheckBox1.Value = False Then Exit Sub
                     If Not Intersect(Range("P5:P50000"), .Cells) Is Nothing Then
                         Application.EnableEvents = False
                         If IsEmpty(.Value) Then
                             .Offset(0, 1).ClearContents
                         Else
                             With .Offset(0, 1)
                                 .Value = True
                             End With
                         End If
                         Application.EnableEvents = True
                     End If
                 End With
     End If
     End Sub
    
     Private Sub CommandButton1_Click()
     Call Worksheet_Change1
     End Sub

  10. #10
    VBAX Regular
    Joined
    Jan 2018
    Location
    The Netherlands
    Posts
    45
    Location
    Worksheet_Change, not Worksheet_Change1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •