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?

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?

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

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?

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

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

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

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

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