VBA Beginner: Help with Checkboxes on created User Form

[MarianneR]

Hi. I'm basically teaching myself some VBA skills when I need them. So please, bear with me.

I've created a user form for an Excel spreadsheet for fairly inexperienced users. I have combo boxes and text boxes that work perfectly well. However, I'm trying to add a check box and I guess I'm not quite sure how they work. Is the checkbox more an individual thing? Does it work in such a way that each item I want needs its own checkbox?

My user form might look something like this:

Patient Name: (text box)
Diagnosis: (combo box - list of 10)
CoMorbidities: (checkbox - list of 9 things, more than one can be chosen)

Basically, I was wondering if it would be possible to have a checkbox like this on a user form. Can I create something where you can select more than one option and then have it drop into my spreadsheet? I keep looking for this, but I'm not even aware if it's possible.

Another question I have is that if my a user closes the form without clicking on the "enter data" or "close form" command buttons, a "compile error" occurs. Here's the code that I have (from an example I found):
Private Sub UserForm_Click()
(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If

The part in parenthesis turns red after the error.

Your patience and any help is appreciated. I hope I've provided enough information.

Thank you in advance.
Marianne

[K Dales]

First part:
There are two options: checkboxes or a listbox with the MultiSelect property
set to fmMultiSelectExtended (this lets you select more than one listed
option, as you can in many Windows dialogs - holding ctrl lets you choose
more than one; holding shift lets you choose a range of consecutive choices).
The listbox is easier to implement bu not necessarily as user-friendly. The
checkboxes all have to be added and coded separately so it is a lot of
coding. To read them: For the list box:
' This fills a range beginning at A1 with the selected list items
For i = 1 to Listbox1.ListCount
If Listbox1.Selected(i) Then Range("A1").Offset(i-1,0)=Listbox1.List(i)
Next i

For checkboxes: (note the value is either True or False depending on whether
checked - so IF statement can just use the value)
If Checkbox1.Value Then Range("A1")="Box1 checked"
If Checkbox2.Value Then Range("A2")="Box2 checked"
etc...

These are just basic examples but hopefully show how to use the controls.
--
- K Dales


"MarianneR" wrote:

>
> Hi. I'm basically teaching myself some VBA skills when I need them. So
> please, bear with me.
>
> I've created a user form for an Excel spreadsheet for fairly
> inexperienced users. I have combo boxes and text boxes that work
> perfectly well. However, I'm trying to add a check box and I guess
> I'm not quite sure how they work. Is the checkbox more an individual
> thing? Does it work in such a way that each item I want needs its own
> checkbox?
>
> My user form _might_ look something like this:
>
> Patient Name: (text box)
> Diagnosis: (combo box - list of 10)
> CoMorbidities: (checkbox - list of 9 things, more than one can be
> chosen)
>
> Basically, I was wondering if it would be possible to have a checkbox
> like this on a user form. Can I create something where you can select
> more than one option and then have it drop into my spreadsheet? I keep
> looking for this, but I'm not even aware if it's possible.
>
> Another question I have is that if my a user closes the form without
> clicking on the "enter data" or "close form" command buttons, a
> "compile error" occurs. Here's the code that I have (from an example I
> found):
> Private Sub UserForm_Click()
> (Cancel As Integer, _
> CloseMode As Integer)
> If CloseMode = vbFormControlMenu Then
> Cancel = True
> MsgBox "Please use the button!"
> End If
>
> The part in parenthesis turns red after the error.
>
> Your patience and any help is appreciated. I hope I've provided enough
> information.
>
> Thank you in advance.
> Marianne
>
>
> --
> MarianneR
> ------------------------------------------------------------------------
> MarianneR's Profile: http://www.excelforum.com/member.php...fo&userid=6253
> View this thread: http://www.excelforum.com/showthread...hreadid=475191
>
>

[K Dales]

Forgot the second part:
The code you have applies to a standard Visual Basic form, but not a
Microsoft Forms UserForm as implemented in VBA. I know, confusing, but there
are different kinds of forms in different environments. VBA forms don't have
control menus or a CloseMode as a parameter for the _Click procedure.
I am not aware of a way to do exactly what you want; perhaps someone else
is. But you may be able to achieve the effect this way:

Private OKToQuit As Boolean ' determines if button pressed to exit

Private Sub Cancel_Click()
OKToQuit = True
Me.Hide
End Sub

Private Sub Apply_Click()
MsgBox "Button pressed; Apply choices"
OKToQuit = True
Me.Hide
End Sub

Private Sub UserForm_Terminate()
' Check if button was pressed:
If Not OKToQuit Then ' If not, prompt user:
DoIt = MsgBox("Do you want to use or ignore your selections?" _
& vbCrLf & "(Press OK to use selections, Cancel to ignore)",
vbOKCancel, "USE SELECTIONS?")
If DoIt = vbOK Then ' User said go ahead:
MsgBox "Closed form: Apply settings"
End If
End If
End Sub

Replace the message boxes with the code you want to run
--
- K Dales


"MarianneR" wrote:

>
> Hi. I'm basically teaching myself some VBA skills when I need them. So
> please, bear with me.
>
> I've created a user form for an Excel spreadsheet for fairly
> inexperienced users. I have combo boxes and text boxes that work
> perfectly well. However, I'm trying to add a check box and I guess
> I'm not quite sure how they work. Is the checkbox more an individual
> thing? Does it work in such a way that each item I want needs its own
> checkbox?
>
> My user form _might_ look something like this:
>
> Patient Name: (text box)
> Diagnosis: (combo box - list of 10)
> CoMorbidities: (checkbox - list of 9 things, more than one can be
> chosen)
>
> Basically, I was wondering if it would be possible to have a checkbox
> like this on a user form. Can I create something where you can select
> more than one option and then have it drop into my spreadsheet? I keep
> looking for this, but I'm not even aware if it's possible.
>
> Another question I have is that if my a user closes the form without
> clicking on the "enter data" or "close form" command buttons, a
> "compile error" occurs. Here's the code that I have (from an example I
> found):
> Private Sub UserForm_Click()
> (Cancel As Integer, _
> CloseMode As Integer)
> If CloseMode = vbFormControlMenu Then
> Cancel = True
> MsgBox "Please use the button!"
> End If
>
> The part in parenthesis turns red after the error.
>
> Your patience and any help is appreciated. I hope I've provided enough
> information.
>
> Thank you in advance.
> Marianne
>
>
> --
> MarianneR
> ------------------------------------------------------------------------
> MarianneR's Profile: http://www.excelforum.com/member.php...fo&userid=6253
> View this thread: http://www.excelforum.com/showthread...hreadid=475191
>
>

[K Dales]

And another thing I thought I should mention: If you try the listbox, there
is another property, ListStyle, that can be set to fmListStyleOption. This
puts checkboxes beside the items in the listbox so it looks a little bit more
like checkboxes, but still not as easy for users since they need to drop down
the list & scroll, etc.
--
- K Dales


"K Dales" wrote:

> First part:
> There are two options: checkboxes or a listbox with the MultiSelect property
> set to fmMultiSelectExtended (this lets you select more than one listed
> option, as you can in many Windows dialogs - holding ctrl lets you choose
> more than one; holding shift lets you choose a range of consecutive choices).
> The listbox is easier to implement bu not necessarily as user-friendly. The
> checkboxes all have to be added and coded separately so it is a lot of
> coding. To read them: For the list box:
> ' This fills a range beginning at A1 with the selected list items
> For i = 1 to Listbox1.ListCount
> If Listbox1.Selected(i) Then Range("A1").Offset(i-1,0)=Listbox1.List(i)
> Next i
>
> For checkboxes: (note the value is either True or False depending on whether
> checked - so IF statement can just use the value)
> If Checkbox1.Value Then Range("A1")="Box1 checked"
> If Checkbox2.Value Then Range("A2")="Box2 checked"
> etc...
>
> These are just basic examples but hopefully show how to use the controls.
> --
> - K Dales
>
>
> "MarianneR" wrote:
>
> >
> > Hi. I'm basically teaching myself some VBA skills when I need them. So
> > please, bear with me.
> >
> > I've created a user form for an Excel spreadsheet for fairly
> > inexperienced users. I have combo boxes and text boxes that work
> > perfectly well. However, I'm trying to add a check box and I guess
> > I'm not quite sure how they work. Is the checkbox more an individual
> > thing? Does it work in such a way that each item I want needs its own
> > checkbox?
> >
> > My user form _might_ look something like this:
> >
> > Patient Name: (text box)
> > Diagnosis: (combo box - list of 10)
> > CoMorbidities: (checkbox - list of 9 things, more than one can be
> > chosen)
> >
> > Basically, I was wondering if it would be possible to have a checkbox
> > like this on a user form. Can I create something where you can select
> > more than one option and then have it drop into my spreadsheet? I keep
> > looking for this, but I'm not even aware if it's possible.
> >
> > Another question I have is that if my a user closes the form without
> > clicking on the "enter data" or "close form" command buttons, a
> > "compile error" occurs. Here's the code that I have (from an example I
> > found):
> > Private Sub UserForm_Click()
> > (Cancel As Integer, _
> > CloseMode As Integer)
> > If CloseMode = vbFormControlMenu Then
> > Cancel = True
> > MsgBox "Please use the button!"
> > End If
> >
> > The part in parenthesis turns red after the error.
> >
> > Your patience and any help is appreciated. I hope I've provided enough
> > information.
> >
> > Thank you in advance.
> > Marianne
> >
> >
> > --
> > MarianneR
> > ------------------------------------------------------------------------
> > MarianneR's Profile: http://www.excelforum.com/member.php...fo&userid=6253
> > View this thread: http://www.excelforum.com/showthread...hreadid=475191
> >
> >

[MarianneR]

Thank you so much for your help. I so appreciate it!!

On the second part of my post, I was referring to a User Form that I had created. I used two command boxes - one to dump the data into the spreadsheet and the other to close the form. If I click the mouse outside of any of the User Form cells or command buttons, the error occurs. I'm sure there's some way to work around this, but I haven't figured it out.

[K Dales]

Don't know if you saw my other post yet - hopefully it can help with the error.

And good luck continuing to learn VBA (we never finish learning it!) - seems
like you are off to a good start if you figured out as much as you did. I am
a teacher at heart (only occasionally in my work history) but I think I key
into posts from people who say they are new to VBA. I appreciate those who
are so eager to learn.
--
- K Dales


"MarianneR" wrote:

>
> Thank you so much for your help. I so appreciate it!!
>
> On the second part of my post, I was referring to a User Form that I
> had created. I used two command boxes - one to dump the data into the
> spreadsheet and the other to close the form. If I click the mouse
> outside of any of the User Form cells or command buttons, the error
> occurs. I'm sure there's some way to work around this, but I haven't
> figured it out.
>
>
> --
> MarianneR
> ------------------------------------------------------------------------
> MarianneR's Profile: http://www.excelforum.com/member.php...fo&userid=6253
> View this thread: http://www.excelforum.com/showthread...hreadid=475191
>
>

[MarianneR]

Ok, I'm really sorry that I'm so slow. And, I cannot tell you how much I appreciate all the help.

I decided to go with the checkboxes. I have 9 of them. I've successfully named them. Unfortunately, this is as far as I've gotten.

One of my checkboxes is "Asthma." So, I've named it "ckbxAsthma" and have added this code:

Private Sub ckbxAsthma_Click()
If ckbxAsthma.Value Then Range("R2") = "Asthma checked"

End Sub

I don't know if the R2 is right ~ or if any of it is right for that matter. YIKES.

So, this is what I want to do: I want to be able to select the checkbox "Asthma" and have that information jump into column R on another spreadsheet. The text and list boxes work fine. But, I don't quite understand how to define my checkbox and then have it dump into my data sheet.

Again, I'm really sorry that I'm not grasping this.

[mark walberg]

You don't seem to be slow at all. You are pretty much there as far as code
is concerned. Only a couple of points. If you just refer to Range("R2"),
then the value will be changed on the value of "R2" on the active sheet,
whatever that is at the moment. One soultion is to name the cells on the
data sheet to "Asthma" (or whatever), and then refer to the cell by name.
Secondly I presume you want to reset if the check box is deselected. So you
will need to have the elase statement also.

Example:

Private Sub ckbxAsthma_Click()
If ckbxAsthma.Value Then
Range("Asthma") = "Asthma checked"
Else
Range("Asthma") = ""
End If
End Sub

Also you may need to add initializing code.

"MarianneR" <[email protected]> wrote
in message news:[email protected]...
>
> Ok, I'm really sorry that I'm so slow. And, I cannot tell you how much
> I appreciate all the help.
>
> I decided to go with the checkboxes. I have 9 of them. I've
> successfully named them. Unfortunately, this is as far as I've
> gotten.
>
> One of my checkboxes is "Asthma." So, I've named it "ckbxAsthma" and
> have added this code:
>
> Private Sub ckbxAsthma_Click()
> If ckbxAsthma.Value Then Range("R2") = "Asthma checked"
>
> End Sub
>
> I don't know if the R2 is right ~ or if any of it is right for that
> matter. YIKES.
>
> So, this is what I want to do: I want to be able to select the
> checkbox "Asthma" and have that information jump into column R on
> another spreadsheet. The text and list boxes work fine. But, I don't
> quite understand how to define my checkbox and then have it dump into
> my data sheet.
>
> Again, I'm really sorry that I'm not grasping this.
>
>
> --
> MarianneR
> ------------------------------------------------------------------------
> MarianneR's Profile:
> http://www.excelforum.com/member.php...fo&userid=6253
> View this thread: http://www.excelforum.com/showthread...hreadid=475191
>