Announcement

Collapse
No announcement yet.

Using a userform/check boxes to populate a range of cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using a userform/check boxes to populate a range of cells



    New here, so be gentle

    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”


    Note, cross posted on mrexcel and VBA express. Any replies to those will be updated here.

    https://www.mrexcel.com/forum/excel-...ml#post5104890

    http://www.vbaexpress.com/forum/show...Range-of-Cells

    thanks
    Last edited by hmltnangel; July 14th, 2018, 08:04.

  • #2
    Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.
    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).


    No further help to be offered, please, until the OP has complied with this request.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      I have been trying to get it to work, but probably barking up the wrong tree here....

      ​​​​​
      Code:
      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

      Comment


      • #4


        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.

        Code:
        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

        Comment

        Working...
        X