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.

  • hmltnangel
    replied
    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

    Leave a comment:


  • hmltnangel
    replied
    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

    Leave a comment:


  • AlanSidman
    replied
    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.

    Leave a comment:

Working...
X