Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
I have a list containing 18 different categories. I want to create a user
form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
Try this....
http://support.microsoft.com/kb/829070 If this post helps click Yes --------------- Jacob Skaria "Ivor Davies" wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
Debra Dalgleish shares some tips:
http://contextures.com/xlUserForm01.html I created a small userform named Userform1 with 4 checkboxes and they were named nicely! Checkbox1 went into column A Checkbox2 went into column B Checkbox3 went into column C Checkbox4 went into column D I also added 2 commandbuttons (cancel and ok). This is the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") '<-- my report worksheet MaxCheckBoxes = 4 'I was lazy! With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("Checkbox" & iCtr).Value 'get ready for next one without closing the userform??? 'Me.Controls("Checkbox" & iCtr).Value = False Next iCtr End With 'or close the form Unload Me End Sub Private Sub CommandButton2_Click() Unload Me 'cancel key End Sub Private Sub UserForm_Initialize() Me.Caption = "Make your choices" With Me.CommandButton1 .Caption = "Ok" .Default = True .Enabled = True End With With Me.CommandButton2 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CheckBox1.Caption = "Option 1 goes here" Me.CheckBox2.Caption = "Option 2 goes here" Me.CheckBox3.Caption = "Option 3 goes here" Me.CheckBox4.Caption = "Option 4 goes here" End Sub ========= And this code goes in a General module. Option Explicit Sub ShowTheForm() UserForm1.Show End Sub Ivor Davies wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
hi
18 different categories. oboy. 1. combo boxes do not support multisect. 2. check boxes. you would have to code if then else/select case statements for all of the possible mathmatical combinations for 18 different check boxes. whoa. i didn't do the math but .....personal opinion. i wouldn't go there if i were you. 3. list boxes do have multilsect properties so i think that would be the way to go. user form tutorial. see this site. http://contextures.com/xlUserForm01.html unfortunially it does not cover llist boxes but does give general info. i did search my liberary but it seem i don't have much on excel forms and list boxes so my liberary seem to be lacking. sorry. do a google. good luck. regards FSt1 "Ivor Davies" wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
Excellent - thanks Dave. I'll give it a go.
"Dave Peterson" wrote: Debra Dalgleish shares some tips: http://contextures.com/xlUserForm01.html I created a small userform named Userform1 with 4 checkboxes and they were named nicely! Checkbox1 went into column A Checkbox2 went into column B Checkbox3 went into column C Checkbox4 went into column D I also added 2 commandbuttons (cancel and ok). This is the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") '<-- my report worksheet MaxCheckBoxes = 4 'I was lazy! With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("Checkbox" & iCtr).Value 'get ready for next one without closing the userform??? 'Me.Controls("Checkbox" & iCtr).Value = False Next iCtr End With 'or close the form Unload Me End Sub Private Sub CommandButton2_Click() Unload Me 'cancel key End Sub Private Sub UserForm_Initialize() Me.Caption = "Make your choices" With Me.CommandButton1 .Caption = "Ok" .Default = True .Enabled = True End With With Me.CommandButton2 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CheckBox1.Caption = "Option 1 goes here" Me.CheckBox2.Caption = "Option 2 goes here" Me.CheckBox3.Caption = "Option 3 goes here" Me.CheckBox4.Caption = "Option 4 goes here" End Sub ========= And this code goes in a General module. Option Explicit Sub ShowTheForm() UserForm1.Show End Sub Ivor Davies wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
Hi Dave, I've used your code below and added additional check boxes to the
user form (I've now pared it down to 14). Everything seems to be OK until I get to the following piece of code: With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Where I get the following error: Run-time error '-2147024809 (80070057)': Could not find the specified object I assume this error is occuring because Excel can't find the object labelled "CheckBox" ? I have changed the name of the form to "CapitalRisks" but substituting this name for "CheckBox" results in the same error. Here is the full code: Private Sub CheckBox1_Click() End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") MaxCheckBoxes = 14 With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Me.Controls("CheckBox" & iCtr).Value = False Next iCtr End With Unload Me End Sub Private Sub CreditRisk_Click() End Sub Private Sub UserForm_Initialize() Me.Caption = "Select Risk Capital Type" With Me.CommandButton1 ..Caption = "Cancel" ..Cancel = True ..TakeFocusOnClick = False ..Enabled = True End With Me.CreditRisk.Caption = "Credit Risk" Me.MismatchRisk.Caption = "Mismatch Risk" Me.InterestRate.Caption = "Interest Rate Risk" Me.BasisRisk.Caption = "Basis Risk" Me.TradingRisk.Caption = "Trading Risk" Me.OperatingRisk.Caption = "Operating Risk" Me.FARisk.Caption = "Fixed Asset Risk" Me.DefAcqRisk.Caption = "Deferred Acquisition Costs" Me.GoodwillRisk.Caption = "Goodwill Risk" Me.SoftwareRisk.Caption = "Software Risk" Me.EquityCap.Caption = "Equity Capital" Me.OtherCap.Caption = "Other Capital" Me.AllRisks.Caption = "Select All Risks" Me.TotalEcoCap.Caption = "Total Economic Capital" End Sub "Dave Peterson" wrote: Debra Dalgleish shares some tips: http://contextures.com/xlUserForm01.html I created a small userform named Userform1 with 4 checkboxes and they were named nicely! Checkbox1 went into column A Checkbox2 went into column B Checkbox3 went into column C Checkbox4 went into column D I also added 2 commandbuttons (cancel and ok). This is the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") '<-- my report worksheet MaxCheckBoxes = 4 'I was lazy! With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("Checkbox" & iCtr).Value 'get ready for next one without closing the userform??? 'Me.Controls("Checkbox" & iCtr).Value = False Next iCtr End With 'or close the form Unload Me End Sub Private Sub CommandButton2_Click() Unload Me 'cancel key End Sub Private Sub UserForm_Initialize() Me.Caption = "Make your choices" With Me.CommandButton1 .Caption = "Ok" .Default = True .Enabled = True End With With Me.CommandButton2 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CheckBox1.Caption = "Option 1 goes here" Me.CheckBox2.Caption = "Option 2 goes here" Me.CheckBox3.Caption = "Option 3 goes here" Me.CheckBox4.Caption = "Option 4 goes here" End Sub ========= And this code goes in a General module. Option Explicit Sub ShowTheForm() UserForm1.Show End Sub Ivor Davies wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
First, if you're going to unload the form after assigning the values in the
worksheet, you don't need to clear the checkbox. Second, I'm guess that you think you have 14 checkboxes and you think you named them checkbox1, ..., checkbox14. But one of those things isn't true. Either the names aren't correct or you don't have 14 checkboxes. Ivor Davies wrote: Hi Dave, I've used your code below and added additional check boxes to the user form (I've now pared it down to 14). Everything seems to be OK until I get to the following piece of code: With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Where I get the following error: Run-time error '-2147024809 (80070057)': Could not find the specified object I assume this error is occuring because Excel can't find the object labelled "CheckBox" ? I have changed the name of the form to "CapitalRisks" but substituting this name for "CheckBox" results in the same error. Here is the full code: Private Sub CheckBox1_Click() End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") MaxCheckBoxes = 14 With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Me.Controls("CheckBox" & iCtr).Value = False Next iCtr End With Unload Me End Sub Private Sub CreditRisk_Click() End Sub Private Sub UserForm_Initialize() Me.Caption = "Select Risk Capital Type" With Me.CommandButton1 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CreditRisk.Caption = "Credit Risk" Me.MismatchRisk.Caption = "Mismatch Risk" Me.InterestRate.Caption = "Interest Rate Risk" Me.BasisRisk.Caption = "Basis Risk" Me.TradingRisk.Caption = "Trading Risk" Me.OperatingRisk.Caption = "Operating Risk" Me.FARisk.Caption = "Fixed Asset Risk" Me.DefAcqRisk.Caption = "Deferred Acquisition Costs" Me.GoodwillRisk.Caption = "Goodwill Risk" Me.SoftwareRisk.Caption = "Software Risk" Me.EquityCap.Caption = "Equity Capital" Me.OtherCap.Caption = "Other Capital" Me.AllRisks.Caption = "Select All Risks" Me.TotalEcoCap.Caption = "Total Economic Capital" End Sub "Dave Peterson" wrote: Debra Dalgleish shares some tips: http://contextures.com/xlUserForm01.html I created a small userform named Userform1 with 4 checkboxes and they were named nicely! Checkbox1 went into column A Checkbox2 went into column B Checkbox3 went into column C Checkbox4 went into column D I also added 2 commandbuttons (cancel and ok). This is the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") '<-- my report worksheet MaxCheckBoxes = 4 'I was lazy! With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("Checkbox" & iCtr).Value 'get ready for next one without closing the userform??? 'Me.Controls("Checkbox" & iCtr).Value = False Next iCtr End With 'or close the form Unload Me End Sub Private Sub CommandButton2_Click() Unload Me 'cancel key End Sub Private Sub UserForm_Initialize() Me.Caption = "Make your choices" With Me.CommandButton1 .Caption = "Ok" .Default = True .Enabled = True End With With Me.CommandButton2 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CheckBox1.Caption = "Option 1 goes here" Me.CheckBox2.Caption = "Option 2 goes here" Me.CheckBox3.Caption = "Option 3 goes here" Me.CheckBox4.Caption = "Option 4 goes here" End Sub ========= And this code goes in a General module. Option Explicit Sub ShowTheForm() UserForm1.Show End Sub Ivor Davies wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
Thanks Dave, I've double checked and found a naming error with checkbox
number 7 (it didn't like "FARisk" as a name). I've fixed the name now but I still get the same error. Double checking the number of check boxes confirms I definitely have 14 of them. So I'm still a little stuck as to why this error is occuring. Any ideas? "Dave Peterson" wrote: First, if you're going to unload the form after assigning the values in the worksheet, you don't need to clear the checkbox. Second, I'm guess that you think you have 14 checkboxes and you think you named them checkbox1, ..., checkbox14. But one of those things isn't true. Either the names aren't correct or you don't have 14 checkboxes. Ivor Davies wrote: Hi Dave, I've used your code below and added additional check boxes to the user form (I've now pared it down to 14). Everything seems to be OK until I get to the following piece of code: With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Where I get the following error: Run-time error '-2147024809 (80070057)': Could not find the specified object I assume this error is occuring because Excel can't find the object labelled "CheckBox" ? I have changed the name of the form to "CapitalRisks" but substituting this name for "CheckBox" results in the same error. Here is the full code: Private Sub CheckBox1_Click() End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") MaxCheckBoxes = 14 With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Me.Controls("CheckBox" & iCtr).Value = False Next iCtr End With Unload Me End Sub Private Sub CreditRisk_Click() End Sub Private Sub UserForm_Initialize() Me.Caption = "Select Risk Capital Type" With Me.CommandButton1 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CreditRisk.Caption = "Credit Risk" Me.MismatchRisk.Caption = "Mismatch Risk" Me.InterestRate.Caption = "Interest Rate Risk" Me.BasisRisk.Caption = "Basis Risk" Me.TradingRisk.Caption = "Trading Risk" Me.OperatingRisk.Caption = "Operating Risk" Me.FARisk.Caption = "Fixed Asset Risk" Me.DefAcqRisk.Caption = "Deferred Acquisition Costs" Me.GoodwillRisk.Caption = "Goodwill Risk" Me.SoftwareRisk.Caption = "Software Risk" Me.EquityCap.Caption = "Equity Capital" Me.OtherCap.Caption = "Other Capital" Me.AllRisks.Caption = "Select All Risks" Me.TotalEcoCap.Caption = "Total Economic Capital" End Sub "Dave Peterson" wrote: Debra Dalgleish shares some tips: http://contextures.com/xlUserForm01.html I created a small userform named Userform1 with 4 checkboxes and they were named nicely! Checkbox1 went into column A Checkbox2 went into column B Checkbox3 went into column C Checkbox4 went into column D I also added 2 commandbuttons (cancel and ok). This is the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") '<-- my report worksheet MaxCheckBoxes = 4 'I was lazy! With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("Checkbox" & iCtr).Value 'get ready for next one without closing the userform??? 'Me.Controls("Checkbox" & iCtr).Value = False Next iCtr End With 'or close the form Unload Me End Sub Private Sub CommandButton2_Click() Unload Me 'cancel key End Sub Private Sub UserForm_Initialize() Me.Caption = "Make your choices" With Me.CommandButton1 .Caption = "Ok" .Default = True .Enabled = True End With With Me.CommandButton2 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CheckBox1.Caption = "Option 1 goes here" Me.CheckBox2.Caption = "Option 2 goes here" Me.CheckBox3.Caption = "Option 3 goes here" Me.CheckBox4.Caption = "Option 4 goes here" End Sub ========= And this code goes in a General module. Option Explicit Sub ShowTheForm() UserForm1.Show End Sub Ivor Davies wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Selection Combo Box
I could duplicate it by having a missing/misnamed checkbox.
Maybe you could add a temporary routine to your userform: Option Explicit Private Sub UserForm_Click() Dim ctrl As Control Dim iCtr As Long iCtr = 0 For Each ctrl In Me.Controls If TypeOf ctrl Is MSForms.CheckBox Then iCtr = iCtr + 1 Debug.Print iCtr & ". ***" & ctrl.Name & "***" End If Next ctrl End Sub Then load the userform, click on it, close the userform and look at the immediate window. I'm still betting that you have something wrong with those names/quantities. (The asterisks are so that you can see if there's an extra space in the name.) Ivor Davies wrote: Thanks Dave, I've double checked and found a naming error with checkbox number 7 (it didn't like "FARisk" as a name). I've fixed the name now but I still get the same error. Double checking the number of check boxes confirms I definitely have 14 of them. So I'm still a little stuck as to why this error is occuring. Any ideas? "Dave Peterson" wrote: First, if you're going to unload the form after assigning the values in the worksheet, you don't need to clear the checkbox. Second, I'm guess that you think you have 14 checkboxes and you think you named them checkbox1, ..., checkbox14. But one of those things isn't true. Either the names aren't correct or you don't have 14 checkboxes. Ivor Davies wrote: Hi Dave, I've used your code below and added additional check boxes to the user form (I've now pared it down to 14). Everything seems to be OK until I get to the following piece of code: With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Where I get the following error: Run-time error '-2147024809 (80070057)': Could not find the specified object I assume this error is occuring because Excel can't find the object labelled "CheckBox" ? I have changed the name of the form to "CapitalRisks" but substituting this name for "CheckBox" results in the same error. Here is the full code: Private Sub CheckBox1_Click() End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub CommandButton2_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") MaxCheckBoxes = 14 With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("CheckBox" & iCtr).Value Me.Controls("CheckBox" & iCtr).Value = False Next iCtr End With Unload Me End Sub Private Sub CreditRisk_Click() End Sub Private Sub UserForm_Initialize() Me.Caption = "Select Risk Capital Type" With Me.CommandButton1 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CreditRisk.Caption = "Credit Risk" Me.MismatchRisk.Caption = "Mismatch Risk" Me.InterestRate.Caption = "Interest Rate Risk" Me.BasisRisk.Caption = "Basis Risk" Me.TradingRisk.Caption = "Trading Risk" Me.OperatingRisk.Caption = "Operating Risk" Me.FARisk.Caption = "Fixed Asset Risk" Me.DefAcqRisk.Caption = "Deferred Acquisition Costs" Me.GoodwillRisk.Caption = "Goodwill Risk" Me.SoftwareRisk.Caption = "Software Risk" Me.EquityCap.Caption = "Equity Capital" Me.OtherCap.Caption = "Other Capital" Me.AllRisks.Caption = "Select All Risks" Me.TotalEcoCap.Caption = "Total Economic Capital" End Sub "Dave Peterson" wrote: Debra Dalgleish shares some tips: http://contextures.com/xlUserForm01.html I created a small userform named Userform1 with 4 checkboxes and they were named nicely! Checkbox1 went into column A Checkbox2 went into column B Checkbox3 went into column C Checkbox4 went into column D I also added 2 commandbuttons (cancel and ok). This is the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim NextRow As Long Dim wks As Worksheet Dim iCtr As Long Dim MaxCheckBoxes As Long Set wks = ThisWorkbook.Worksheets("Sheet1") '<-- my report worksheet MaxCheckBoxes = 4 'I was lazy! With wks NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iCtr = 1 To MaxCheckBoxes .Cells(NextRow, "A").Offset(0, iCtr - 1).Value _ = Me.Controls("Checkbox" & iCtr).Value 'get ready for next one without closing the userform??? 'Me.Controls("Checkbox" & iCtr).Value = False Next iCtr End With 'or close the form Unload Me End Sub Private Sub CommandButton2_Click() Unload Me 'cancel key End Sub Private Sub UserForm_Initialize() Me.Caption = "Make your choices" With Me.CommandButton1 .Caption = "Ok" .Default = True .Enabled = True End With With Me.CommandButton2 .Caption = "Cancel" .Cancel = True .TakeFocusOnClick = False .Enabled = True End With Me.CheckBox1.Caption = "Option 1 goes here" Me.CheckBox2.Caption = "Option 2 goes here" Me.CheckBox3.Caption = "Option 3 goes here" Me.CheckBox4.Caption = "Option 4 goes here" End Sub ========= And this code goes in a General module. Option Explicit Sub ShowTheForm() UserForm1.Show End Sub Ivor Davies wrote: I have a list containing 18 different categories. I want to create a user form that allows the user to select multiple combinations of these categories - either through a check box or list box (prefer check boxes). Once the selection is complete the choices made will be recorded as data in a separate list in the worksheet. I am using Excel 2003, I have not created user forms before and have only basic experience with VBA coding. Is there a step-by-step guide somewhere that will show me how to create a user form that includes check box options? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple selection on a combo box | Excel Worksheet Functions | |||
Row Selection using Drop down/Combo boxes | Excel Discussion (Misc queries) | |||
Combo box disappears after selection made | Excel Discussion (Misc queries) | |||
multiple selection in combo box | Excel Discussion (Misc queries) | |||
Combo Box selection to place $0 into another cell | Excel Discussion (Misc queries) |