Can we do away with drop down in combo box ?

[Sunil Chandramohan]

Hi, guys, I have developed a excel user form based data entry for accounting the canteen accounts in my company. The user from has three option buttons. I have a requirement that when the INCOME button is checked the ACCOUNT drop down field should be set as A_Advance, when the DEBIT button is checked the field should be set as B_Loaned and when PURCHASE button is checked it should give drop down list of various account heads as shown. the code works fine until I have to select the PURCHASE button first and then change my selection to INCOME or DEBIT, the drop down list pertaining to PURCHASE still remains, where in it should only narrow to Account heads as I mentioned afore. I realise that the range accRg. gets saved when I check the PURCHASE option. Is there any way to clear that range when I have to select another option button or better still can I remove the drop down property of the combobox if the row count of accRg is only 1 row. I need the the dynamic range defined by the offset worksheet function, incase other account fields get added later on. Some help or thoughts will be definitely appreciated..Thanks in advance

The code for the option buttons I wrote is

Private Sub optDebit_Click()
    Dim accRg As Range
    Set accRg = Range("OFFSET(Data!$N$1,MATCH(""Debit"",OFFSET(Data!$M$2,0,0,COUNTA(Data!$M:$M)-1,1),0),0,COUNTIF(OFFSET(Data!$M$2,0,0,COUNTA(Data!$M:$M)-1,1),""Debit""),1)")
    If accRg.Rows.Count = 1 Then
    Me.cboAccount.Value = "B_Loaned"
    Else
    cboAccount.ListRows = accRg.Rows.Count
    Me.cboAccount.List = (accRg)
    End If
End Sub

Private Sub optIncome_Click()
    Dim accRg As Range
    Set accRg = Range("OFFSET(Data!$N$1,MATCH(""Income"",OFFSET(Data!$M$2,0,0,COUNTA(Data!$M:$M)-1,1),0),0,COUNTIF(OFFSET(Data!$M$2,0,0,COUNTA(Data!$M:$M)-1,1),""Income""),1)")
    If accRg.Rows.Count = 1 Then
    Me.cboAccount.Value = "A_Advance"
    Else
    cboAccount.ListRows = accRg.Rows.Count
    Me.cboAccount.List = (accRg)
    End If
End Sub

Private Sub optPurchase_Click()
    Dim accRg As Range
    Set accRg = Range("OFFSET(Data!$N$1,MATCH(""Purchase"",OFFSET(Data!$M$2,0,0,COUNTA(Data!$M:$M)-1,1),0),0,COUNTIF(OFFSET(Data!$M$2,0,0,COUNTA(Data!$M:$M)-1,1),""Purchase""),1)")
    If accRg.Rows.Count = 1 Then
    Me.cboAccount.Value = "P_Purchase"
    Else
    Me.cboAccount.Value = ""
    cboAccount.ListRows = accRg.Rows.Count
    Me.cboAccount.List = (accRg)
    End If
End Sub

 

[dmt32]

Hi,
welcome to forum

You can use the ShowDropButtonWhen property of the control to hide the dropdown button in your optionbutton codes

Private Sub optDebit_Click()

    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "B_Loaned"
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
        End With
End Sub

Sub optIncome_Click()

    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "A_Advance"
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
        End With

End Sub

Private Sub optPurchase_Click()


    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "P_Purchase"
            .ShowDropButtonWhen = fmShowDropButtonWhenAlways
        End With

End Sub

Dave

 

[Sunil Chandramohan]

Hi,
welcome to forum

You can use the ShowDropButtonWhen property of the control to hide the the button in your optionbutton codes

Private Sub optDebit_Click()

    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "B_Loaned"
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
        End With
End Sub

Sub optIncome_Click()

    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "A_Advance"
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
        End With

End Sub

Private Sub optPurchase_Click()


    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "P_Purchase"
            .ShowDropButtonWhen = fmShowDropButtonWhenAlways
        End With

End Sub

Dave

Thanks a ton Dave. I really appreciate your effort. By the way..out of an academic interest, is there a way we can clear the range accRg. because there might be addition of accounts as time progresses....Thanks is advance

 

[dmt32]

Glad suggestion helped

To clear your Range object variable from memory you would do this

Set accRg = Nothing

Dave

 

[Sunil Chandramohan]

Hi,
welcome to forum

You can use the ShowDropButtonWhen property of the control to hide the the button in your optionbutton codes

Private Sub optDebit_Click()

    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "B_Loaned"
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
        End With
End Sub

Sub optIncome_Click()

    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "A_Advance"
            .ShowDropButtonWhen = fmShowDropButtonWhenNever
        End With

End Sub

Private Sub optPurchase_Click()


    If accRg.Rows.Count = 1 Then
        With Me.cboAccount
            .Value = "P_Purchase"
            .ShowDropButtonWhen = fmShowDropButtonWhenAlways
        End With

End Sub

Dave

Thanks a ton Dave. I really appreciate your effort. By the way..out of an academic interest, is there a way we can clear the range accRg. because there might be addition of accounts as time progresses....Thanks is advance

 

[Sunil Chandramohan]

Glad suggestion helped

To clear your Range object variable from memory you would do this

Set accRg = Nothing

Dave

Hi Dave the drop down doing away worked but what happened was the if I select INCOME or DEBIT first and have to change to PURCHASE on a second thought, the dropdown arrow does not come..Other wise it works fine

 

[Sunil Chandramohan]

Hi Dave the drop down doing away worked but what happened was the if I select INCOME or DEBIT first and have to change to PURCHASE on a second thought, the dropdown arrow does not come..Other wise it works fine

This did not help. Somehow after selecting the PURCHASE option , the accRg still retain its range of values which comes into the list of values for other two options...

Sunil

 

[Sunil Chandramohan]

Thanks a ton Dave. I really appreciate your effort. By the way..out of an academic interest, is there a way we can clear the range accRg. because there might be addition of accounts as time progresses....Thanks is advance

Hi dave...writing the same show - no show code in the second half of the IF condition helped for the PURCHASE option button. This is exactly what I require..Thanks a lot.

Would be obliged if you could look into why the Set accRg = Nothing: did not work though..