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