Object Variable or with variable not set

voutsy · Mar 2nd 2006

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

Code

'Opens form and unprotects the worksheet


Sub OpenForm()
        VolOrgs.Show
     
End Sub

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

Code

Private Sub CommandButton1_Click()
Unload Me
End Sub


Private Sub UserForm_Click()


End Sub

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

Anonymous · Mar 2nd 2006

Re: Object Variable or with variable not set

The times I've had a similar problem is when I've deleted something on a form or changed its name, thereby leaving a bit of orphaned code in the form module. Are you sure you didn't do something like that? Check each sub in the form module and ensure its parent control is still on the form with the same name.

It would help if, in that situation, the debug showed which sub was orphaned, but instead it show at the form.show line as in your case.

**Andy Pope** · Mar 3rd 2006

Re: Object Variable or with variable not set

Hi,

Add Option Explicit to the top of your userform code module and then run your code. If there is anything that has not be declared or references to controls that no longer exists the compiler will tell you.

voutsy · Mar 3rd 2006

Re: Object Variable or with variable not set

Thanks guys,

Derk you were right about me leaving some "orphaned" code in there after my edit.

And Andy thanks I hadnt thought of putting Option Explicit in there, that saved me ages trawling through the code.

Thanks to you both!

Object Variable or with variable not set

Participate now!

Share