Can we do away with drop down in combo box ?

Sunil Chandramohan

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
 

Attachments

  • 1.jpg
    1.jpg
    70.6 KB · Views: 6
  • 2.jpg
    2.jpg
    86.4 KB · Views: 4
  • 3.jpg
    3.jpg
    79.5 KB · Views: 4
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,523
Office Version
  1. 2019
Platform
  1. Windows
Hi,
welcome to forum

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

VBA Code:
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
 
Solution

Sunil Chandramohan

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi,
welcome to forum

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

VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,523
Office Version
  1. 2019
Platform
  1. Windows
Glad suggestion helped

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

VBA Code:
Set accRg = Nothing

Dave
 

Sunil Chandramohan

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi,
welcome to forum

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

VBA Code:
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

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Glad suggestion helped

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

VBA Code:
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

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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..
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,519
Members
417,149
Latest member
drbro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top