User Form VBA - Using the Userform to fill in cells

[hmltnangel]

Hi folks,

As always, I don't use excel for many moons and then struggle to write any meaningful VBA afterwards.

I have designed myself a userformwhich is exactly what I need. Now I am struggling with the VBA to make it work.

I need two things.

1. When column “O” is selected onany given row, then the userform pops up and asks to be completed
   NOTE - The userform has a bunch of optionbuttons on it which the user will select any number of these. (Lets call themrejection reasons)
   
   
2. What I need is for instance, ifuser selects option button 1 then the corresponding column “P” in the same rowis input with a “yes” when the ok button is clicked.
   NOTE – the ok/cancel commandbuttons are called (ok) “All RejectsAdded” and (cancel) “No Reject Reasons”.
   
   3. If “no Reject Reasons” is selectedthen for instance the corresponding columns “P” to “AA” are populated with “NO”
   

Thanks for any help.

 

[Fluff]

Cross posted https://www.ozgrid.com/forum/forum/...form-check-boxes-to-populate-a-range-of-cells

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[hmltnangel]

Ok thanks, no problem. I rarely cross post, so didn't even think about it today.

That being said, I always check back on any and make sure any threads are updated, it shouldn't have caused an issue but I'll remember for future.

 

[hmltnangel]

I have been trying to make it work but maybe barking up the wrong tree. Also, instead of option buttons, I used check boxes in order to allow users to select multiple reasons

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

 

[hmltnangel]

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

 

[Ingolf]

Hi,

Here: Worksheet_Change1 it should not be Worksheet_Change???
Have you more than one Worksheet_Change event??

 

[hmltnangel]

Hi,

I have a Worksheet_change event in the main spreadsheet itself. However I was trying to get this to work when I click the button on a userform. The worksheet change events are very similar, but do slightly different things.

 

[hmltnangel]

As it seems to have trouble with the Worksheet_Change, I had alook and spotted this bit of code online which I tailored to suit what I wanted. However - its not quite right. When I use it, it just starts overtyping everything from A2 onwards. What do I change so it starts from the first empty cell in column P? and fills in the row with TRUE for each checkbox that is ticked, and FALSE for each checkbox that is not ticked.

Private Sub CommandButton1_Click()
Dim erow As Long
Dim lastrow As Long
lastrow = Cells.Find(What:="", _
After:=Range("A6"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
erow = lastrow + 1
Dim intCheckBox As Integer
For intCheckBox = 1 To 26
If Controls("Checkbox" & intCheckBox) Then
Cells(erow, intCheckBox) = "True"
End If
Next intCheckBox

End Sub