Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Beginner: Help with Checkboxes on created User Form
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to clear all checkboxes on a form? | Excel Worksheet Functions | |||
Copy checkboxes created with Excel97 | Excel Discussion (Misc queries) | |||
I created a form on excel. want to edit the form without printing | Excel Discussion (Misc queries) | |||
Beginner - Excel - Auto look up values from worksheet 3 from User | Excel Programming | |||
Beginner question: where to put code when form loads | Excel Programming |