PDA

View Full Version : Using a Userform with Multilple Checkboxes to complete a Range of Cells



hmltnangel
07-13-2018, 07:51 AM
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

hmltnangel
07-13-2018, 08:20 AM
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

hmltnangel
07-13-2018, 04:06 PM
Cross posted

http://www.vbaexpress.com/forum/showthread.php?63177-Using-a-Userform-with-Multilple-Checkboxes-to-complete-a-Range-of-Cells

https://www.mrexcel.com/forum/excel-questions/1062964-user-form-vba-using-userform-fill-cells.html#post5104890

Hightree
07-14-2018, 01:50 PM
Wich error, post your xlsm here

hmltnangel
07-15-2018, 03:11 PM
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.

Hightree
07-15-2018, 10:36 PM
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.

Hightree
07-15-2018, 10:39 PM
At First I See Two times End Sub, thats one to much.

hmltnangel
07-16-2018, 02:29 AM
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

Hightree
07-16-2018, 03:07 AM
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

Hightree
07-16-2018, 03:09 AM
Worksheet_Change, not Worksheet_Change1