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