Combobox repeating options

voutsy · Mar 3rd 2006

Hi again,

I have got (with your help) my userform working as intended, except for one minor problem:

Once the form is filled in the user should click..

"OK" and the data is written to the spreadsheet.

After this the form remains completed so in order to input the next set of data the second button needs to be pressed - "clear form"

This clears the form and allows the user to input the next data set, however the combobox options are multiplied. For example one of the comboboxes has 3 options, however on inputting the second set of data 6 options will appear, each option appearing twice. If the user then clears the form for the 3rd dataset then the combobox will have 9 options, again each option being repeated 3 times,.... and so on.

What do I need to do in order to get the combobox options to reset to the default of just 3 choices.

I attach my code below, and feel that the problem could lie in this section of code:

Code

Private Sub CmdClear_Click()


Call UserForm_Initialize


End Sub

for info the rest of my code:

Code

Option Explicit


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"


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


'records whether the person is receiving services from teh department


If OptYes = True Then
    
    ActiveCell.Offset(0, 13) = "Yes"


ElseIf OptNo = True Then


    ActiveCell.Offset(0, 13) = "No"
    
End If


'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 TextBox2_Change()


End Sub


Private Sub UserForm_Initialize()


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"
    .AddItem "HFHS to be provided"
    
End With
cboOutcome1.Value = ""




With cboOutcome2
    .AddItem "Referral to other agency/organisation"
    .AddItem "Provision of advice and information"
    .AddItem "Safety assessment"
    .AddItem "HFHS to be provided"
        
End With
cboOutcome2.Value = ""




With cboOutcome3
    .AddItem "Referral to other agency/organisation"
    .AddItem "Provision of advice and information"
    .AddItem "Safety assessment"
    .AddItem "HFHS to be provided"
    
End With
cboOutcome3.Value = ""


OptLow = True
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


End Sub

Display More

Any help with this is greatly appreciated...

Thanks
Damian

max_lux · Mar 3rd 2006

Re: Combobox repeating options

Three ethnicities of caucasion? Looks skewed to me...

British
Irish
Other

Why not?
Good
Bad
Ugly

max_lux · Mar 3rd 2006

Re: Combobox repeating options

and what happened to?

White European
White North American
White South American
White Central American
White South American
White African
White Pakistani
White Indian
White Asian

Black European
Black North American
Black South American
Black Central American
Black South American
Black African
Black Pakistani
Black Indian
Black Asian

Asian European
Asian North American
Asian South American
Asian Central American
Asian South American
Asian African
Asian Pakistani
Asian Indian

Pakistani European
Pakistani North American
Pakistani South American
Pakistani Central American
Pakistani South American
Pakistani African
Pakistani Indian
Pakistani Asian

Indian European
Indian North American
Indian South American
Indian Central American
Indian South American
Indian African
Indian Pakistani
Indian Indian
Indian Asian

Native European
Native North American
Native South American
Native Central American
Native South American
Native African
Native Pakistani
Native Indian
Native Asian

Not Stated

voutsy · Mar 4th 2006

Re: Combobox repeating options

Sorry for the double post

voutsy · Mar 4th 2006

Re: Combobox repeating options

Hi Max

I am glad that you read my code so thoroughly!!
However i wasn't asking for your interpretation of the english department of healths ethnic categories.

If you would like to read into this then read here

I was actually asking for help solving my vba problem.

Have you anthing to add other than spam?

max_lux · Mar 4th 2006

Re: Combobox repeating options

Sorry m8, I just hate seeing people being "classified"

As far as I am concerned, there are only two classifications for people:

Living
Deceased

now, to code
sorry, I can't see a reason your code triples the values. You seem to be clearing it properly on init. I will continue to see if I can figure it out...

max_lux · Mar 4th 2006

Re: Combobox repeating options

just something to try - you might, in the initalize

Code

.value = 0

instead of

Code

.value = ""

max_lux · Mar 4th 2006

Re: Combobox repeating options

ok i think this might be it - try this - because you are essentially - reloading it rather than initializing it, it will still contain the previous values. unloading and initializing it should reset the values

Code

Private Sub CmdClear_Click() 


    Unload Userform
    Call UserForm_Initialize 
     
End Sub

**Andy Pope** · Mar 4th 2006

Re: Combobox repeating options

Hi,

The problem is that you are adding items to the comboboxes without first clearing them. See this example[vba] With cboClientCategory

.Clear ' remove items

.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
[/vba]
You might be better off breaking the code into 2 parts[vba]Sub ResetControls()

txtRef_No.Value = ""
cboClientCategory.Value = ""
cboReferralType.Value = ""
cboReferralSource.Value = ""
cboReferralReason.Value = ""
cboReferralReason2.Value = ""
cboReferralReason3.Value = ""
cboOutcome1.Value = ""
cboOutcome2.Value = ""
cboOutcome3.Value = ""

OptLow = True
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

End Sub

Sub LoadControls()

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

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

With cboReferralSource
.AddItem "Southport DGH (HFHS)"
.AddItem "Aintree Hospital"
.AddItem "Other Hospital"
.AddItem "Not Known"
End With

With cboReferralReason
.AddItem "Shopping"
.AddItem "Laundry"
.AddItem "Housework"
End With

With cboReferralReason2
.AddItem "Shopping"
.AddItem "Laundry"
.AddItem "Housework"
End With

With cboReferralReason3
.AddItem "Shopping"
.AddItem "Laundry"
.AddItem "Housework"
End With

With cboOutcome1
.AddItem "Referral to other agency/organisation"
.AddItem "Provision of advice and information"
.AddItem "Safety assessment"
.AddItem "HFHS to be provided"
End With

With cboOutcome2
.AddItem "Referral to other agency/organisation"
.AddItem "Provision of advice and information"
.AddItem "Safety assessment"
.AddItem "HFHS to be provided"
End With

With cboOutcome3
.AddItem "Referral to other agency/organisation"
.AddItem "Provision of advice and information"
.AddItem "Safety assessment"
.AddItem "HFHS to be provided"
End With

End Sub
Private Sub UserForm_Initialize()

LoadControls
ResetControls

End Sub
[/vba]And when you need to clear a set of answer just call the ResetControls routine.

Combobox repeating options

Participate now!

Share