Hi
I have made a few cosmetic changes to an excel user form that was working perfectly. The changes were only very minor and related to removal of some of the options in the drop down menus.
Now when I click the "open form" button i get a runtime error 424 object required.
Ill paste all my code below in the hope that someone can spot what I've done wrong.
When I click on Debug the "VolOrgs.Show" bit of code is highlighted in yellow.
Sheet 2
Code
' Opens form and unprotects the worksheet
Sub OpenForm()
VolOrgs.Show
End Sub
' opens instruction dialoge box
Sub OpenInstruction()
Instructs.Show
End Sub
Display More
MODULE 1
MODULE 2
Code
Sub Protect_sheet()
'
' Protect_sheet Macro
' Macro recorded 16/09/2005 by Damian Voutsinas
'
'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
Sub unprotect_sheet()
'
' unprotect_sheet Macro
' Macro recorded 16/09/2005 by Damian Voutsinas
'
'
ActiveSheet.Unprotect
End Sub
Display More
FORM INSTRUCTS
FORM VolOrgs
Code
Private Sub cboClientCategory_Change()
End Sub
Private Sub cboOutcome1_Change()
End Sub
Private Sub cboReferralType_Change()
End Sub
Private Sub CmdOk_Click()
ActiveWorkbook.Sheets("sheet2").Activate
Range("A10").Select
'to get down to the 1st empty row
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
'to put the client reference number into column A
ActiveCell.Offset(0, 0) = txtRef_No.Value
'in order to put the correct age group into column B
If OptUnder18 = True Then
ActiveCell.Offset(0, 1).Value = "Under 18"
ElseIf Opt1864 = True Then
ActiveCell.Offset(0, 1).Value = "18-64"
ElseIf Opt6574 = True Then
ActiveCell.Offset(0, 1).Value = "65-74"
ElseIf Opt75 = True Then
ActiveCell.Offset(0, 1).Value = "75+"
End If
'selected values from the comboboxes get put into column C to I on the same row
ActiveCell.Offset(0, 2) = cboClientCategory.Value
ActiveCell.Offset(0, 3) = cboReferralType.Value
ActiveCell.Offset(0, 4) = cboReferralSource.Value
ActiveCell.Offset(0, 5) = cboReferralReason.Value
ActiveCell.Offset(0, 6) = cboReferralReason2.Value
ActiveCell.Offset(0, 7) = cboReferralReason3.Value
'puts the selected ethnicity into column J
If OptWhitBrit = True Then
ActiveCell.Offset(0, 8).Value = "White British"
ElseIf OptWhitIrish = True Then
ActiveCell.Offset(0, 8).Value = "White Irish"
ElseIf OptWhitOther = True Then
ActiveCell.Offset(0, 8).Value = "White Other"
ElseIf OptMixedCaribbean = True Then
ActiveCell.Offset(0, 8).Value = "Mixed Caribbean"
ElseIf OptMixedAfrican = True Then
ActiveCell.Offset(0, 8).Value = "Mixed African"
ElseIf OptMixedAsian = True Then
ActiveCell.Offset(0, 8).Value = "Mixed Asian"
ElseIf OptMixedOther = True Then
ActiveCell.Offset(0, 8).Value = "Mixed Other"
ElseIf OptAsianIndian = True Then
ActiveCell.Offset(0, 8).Value = "Asian Indian"
ElseIf OptAsianPakistani = True Then
ActiveCell.Offset(0, 8).Value = "Asian Pakistani"
ElseIf OptAsianOther = True Then
ActiveCell.Offset(0, 8).Value = "Asian Other"
ElseIf OptBlackCaribbean = True Then
ActiveCell.Offset(0, 8).Value = "Black Caribbean"
ElseIf OptBlackAfrican = True Then
ActiveCell.Offset(0, 8).Value = "Black African"
ElseIf OptBlackOther = True Then
ActiveCell.Offset(0, 8).Value = "Black Other"
ElseIf OptChinese = True Then
ActiveCell.Offset(0, 8).Value = "Chinese"
ElseIf OptChineseOther = True Then
ActiveCell.Offset(0, 8).Value = "Other ethnicity"
ElseIf OptNotStated = True Then
ActiveCell.Offset(0, 8).Value = "Not Stated"
End If
'puts the FACS eligibility into column K of the sanme row
If OptLow = True Then
ActiveCell.Offset(0, 9).Value = "Low"
ElseIf OptModerate = True Then
ActiveCell.Offset(0, 9).Value = "Moderate"
ElseIf OptSubstantial = True Then
ActiveCell.Offset(0, 9).Value = "Substantial"
ElseIf OptCritical = True Then
ActiveCell.Offset(0, 9).Value = "Critical"
End If
'selected values from the comboboxes get put into column L to O on the same row
ActiveCell.Offset(0, 10) = cboOutcome1.Value
ActiveCell.Offset(0, 11) = cboOutcome2.Value
ActiveCell.Offset(0, 12) = cboOutcome3.Value
ActiveCell.Offset(0, 13) = CboOutcome4.Value
'records the current date in column "P"
ActiveCell.Offset(0, 14) = Date
'Readies the active cell for inputting of the next form
Range("A10").Select
ActiveCell.Offset(1, 0).Select
End Sub
Private Sub CmdClear_Click()
Call UserForm_Initialize
End Sub
'Closes the form (and protects the range where data is automatically input)
Private Sub CmdCancel_Click()
Unload Me
End Sub
Private Sub Frame3_Click()
End Sub
Private Sub UserForm_Initialize()
txtReferralReason.Value = ""
txtRef_No.Value = ""
With cboClientCategory
.AddItem "Older People (Not EMI) [Aged 65-74]"
.AddItem "Older People (Not EMI) [Aged 75+]"
.AddItem "Physical Disability/Sensory Impairment [Aged 18-64]"
.AddItem "Learning Disability [Aged 18-64]"
.AddItem "Mental Health / EMI [Aged 18-64]"
.AddItem "Mental Health / EMI [Aged 65-74]"
.AddItem "Mental Health / EMI [Aged 75+]"
.AddItem "Substance Misuse [Aged 18-64]"
.AddItem "Other Vulnerable People [Aged 18-64]"
End With
cboClientCategory.Value = ""
With cboReferralType
.AddItem "New Client"
.AddItem "1st Contact of year for an existing client"
.AddItem "Repeat (i.e. Subsequent contact within the year)"
End With
cboReferralType.Value = ""
With cboReferralSource
.AddItem "Southport DGH (HFHS)"
.AddItem "Aintree Hospital"
.AddItem "Other Hospital"
.AddItem "Not Known"
End With
cboReferralSource.Value = ""
With cboReferralReason
.AddItem "Shopping"
.AddItem "Laundry"
.AddItem "Housework"
End With
cboReferralReason.Value = ""
With cboReferralReason2
.AddItem "Shopping"
.AddItem "Laundry"
.AddItem "Housework"
End With
cboReferralReason2.Value = ""
With cboReferralReason3
.AddItem "Shopping"
.AddItem "Laundry"
.AddItem "Housework"
End With
cboReferralReason3.Value = ""
With cboOutcome1
.AddItem "Referral to other agency/organisation"
.AddItem "Provision of advice and information"
.AddItem "Safety assessment"
End With
cboOutcome1.Value = ""
With cboOutcome2
.AddItem "Referral to other agency/organisation"
.AddItem "Provision of advice and information"
.AddItem "Safety assessment"
End With
cboOutcome2.Value = ""
With cboOutcome3
.AddItem "Referral to other agency/organisation"
.AddItem "Provision of advice and information"
.AddItem "Safety assessment"
End With
cboOutcome3.Value = ""
OptUnder18 = False
Opt1864 = False
Opt6574 = False
Opt75 = False
OptWhiteBrit = False
OptWhiteIrish = False
OptWhiteOther = False
OptMixedCaribbean = False
OptMixedAfrican = False
OptMixedAsian = False
OptMixedOther = False
OptAsianIndian = False
OptAsianPakistani = False
OptAsianOther = False
OptBlackCaribbean = False
OptBlackAfrican = False
OptBlackOther = False
OptChinese = False
OptChineseOther = False
OptNotStated = False
OptSubstantial = False
OptModerate = False
OptCritical = False
End Sub
Display More
Any help with this will be much appreciated
Thanks
voutsy