List Combox in VBA EXCEL (Macro or any other help)

[abdullahsikandar]

Hi Guys,
I need your help in making a macro or any trick where i can have list of checkboxes and when i select any checkbox from there so the value or the label or that checkbox automatically added in the cell of excel.
There can be two ways we can do that:
1) we can make a macro where when we click on that cell in excel the "listbox of checkbox" will be open and we can select a bunch of checkboxes and there values will be automtically added into that cell.( is this possible?)?
2) We can make on user forum and when we click on the cell that "User forum" will be open and once we select our checkboxes their values will be added automatically into that cell?
Is it possible? If yes, so, Kindly advice me i am new in in Vb and trying to make this project ... Please help me.
Thank in advance.

 

[abdullahsikandar]

Hi Bertie,

I need your help again. You helped me before i need a currency changer in Access or Excel where User can give the rates manually.

I need two list boxes where user can select which Currency is he holding and second which currency he wants to change.

Holding Currency CHanged Currency

USD EUR
EUR
RS

For example user select USD as their currency and wants to change into EUR so user can select that and then in the bottom we have four text boxes

First one said "Holding Currency Amount" in this textbox user can write rate of USD Amount.
Second one Said" Changed Currency Amount" which will tell them whats the rate of EUR Amount.
Third one say "Total amount of Conversion" where user can write all the amount which he wants to change.
Forth one say " Total Changed Amount" this one will tell the total amount in EUR .

I hope you understand that if there is anything missing let me know or if you have any better suggestions Kindly let me know how to make it better but i need it manual or it will take the rate from internet with the date.

 

[bertie]

First of all I would imagine you would need some type of conversion rates table:

Excel 2007
	A	B	C	D
1	Currency	USD	EUR	RS
2	USD	-	1.6	1.7
3	EUR	1.6	-	1.8
4	RS	1.7	1.8	-
Sheet1

NB column headers need to be in the same order as row headers.
If using formula Index/Match would solve this.

VBA solution
I would use two combo boxes rather than list boxes:
cboConvertFrom
cboConvertTo

These would be populated in the user form initialize event.

Private Sub UserForm_Initialize()
   Dim numRows As Long
   
   With Sheets("Sheet1")
      numRows = .Cells(.Rows.Count, 1).End(xlUp).Row
      
      Me.cboConvertFrom.List = .Cells(2, 1).Resize(numRows).Value
      Me.cboConvertTo.List = .Cells(2, 1).Resize(numRows).Value
   End With
End Sub

I would imagine three text boxes.
txtConversionRate
txtConversionAmount
txtConversionTotal

The conversionRate is declared as a global variable.
It is calculated when the cboConvertTo combo box changes. NB-I have not coded for the user entering the same monetary unit. (i.e., if same then, msgbox, set focus, exit sub)

I have used variables to make the code easier to read.

Public conversionRate As Double


Private Sub cboConvertTo_Change()
   Dim rw As Long
   Dim col As Long
   
   'get the conversion rate
   rw = Me.cboConvertFrom.ListIndex + 2
   col = Me.cboConvertTo.ListIndex + 2
   
   conversionRate = Sheets("Sheet1").Cells(rw, col).Value
   Me.txtConversionRate.Value = conversionRate


End Sub

The user enters the amount.
When the user tabs out of the amount text box this fires the AfterUpdate event.

Private Sub txtConversionAmount_AfterUpdate()
   Dim conversionAmount As Double
   
   conversionAmount = CDbl(Me.txtConversionAmount.Value)
   Me.txtConversionTotal.Value = conversionAmount * conversionRate
End Sub

Hope this gives you some ideas,
Bertie

 

[abdullahsikandar]

First of all I would imagine you would need some type of conversion rates table:

Excel 2007

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #B8CCE4"]Currency[/TD]
[TD="bgcolor: #B8CCE4, align: center"]USD[/TD]
[TD="bgcolor: #B8CCE4, align: center"]EUR[/TD]
[TD="bgcolor: #B8CCE4, align: center"]RS[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #DBE5F1"]USD[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1.6[/TD]
[TD="align: center"]1.7[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #DBE5F1"]EUR[/TD]
[TD="align: center"]1.6[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1.8[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #DBE5F1"]RS[/TD]
[TD="align: center"]1.7[/TD]
[TD="align: center"]1.8[/TD]
[TD="align: center"]-[/TD]

</tbody>

Sheet1​

NB column headers need to be in the same order as row headers.
If using formula Index/Match would solve this.

VBA solution
I would use two combo boxes rather than list boxes:
cboConvertFrom
cboConvertTo

These would be populated in the user form initialize event.

Private Sub UserForm_Initialize()
   Dim numRows As Long
   
   With Sheets("Sheet1")
      numRows = .Cells(.Rows.Count, 1).End(xlUp).Row
      
      Me.cboConvertFrom.List = .Cells(2, 1).Resize(numRows).Value
      Me.cboConvertTo.List = .Cells(2, 1).Resize(numRows).Value
   End With
End Sub

I would imagine three text boxes.
txtConversionRate
txtConversionAmount
txtConversionTotal

The conversionRate is declared as a global variable.
It is calculated when the cboConvertTo combo box changes. NB-I have not coded for the user entering the same monetary unit. (i.e., if same then, msgbox, set focus, exit sub)

I have used variables to make the code easier to read.

Public conversionRate As Double


Private Sub cboConvertTo_Change()
   Dim rw As Long
   Dim col As Long
   
   'get the conversion rate
   rw = Me.cboConvertFrom.ListIndex + 2
   col = Me.cboConvertTo.ListIndex + 2
   
   conversionRate = Sheets("Sheet1").Cells(rw, col).Value
   Me.txtConversionRate.Value = conversionRate


End Sub

The user enters the amount.
When the user tabs out of the amount text box this fires the AfterUpdate event.

Private Sub txtConversionAmount_AfterUpdate()
   Dim conversionAmount As Double
   
   conversionAmount = CDbl(Me.txtConversionAmount.Value)
   Me.txtConversionTotal.Value = conversionAmount * conversionRate
End Sub

Hope this gives you some ideas,
Bertie

I made the form but couldn`t able to get the values in the combo box they are still empty. Anyhow i am trying to get it work. I made one form if you can make that work. Thats just the form.

When you click "Home Country" it will take the country code in first textbox1 which we clicked on listbox.
When you click "Destination Country" it will take the country code in second textbox2 which we clicked on listbox.
Textbox3 is the one where you can define the total amount how much you want to convert.
TextBox4 is the one where it will give the total.

All the rates are already defined below the other Sheet2.

Like this:

Reference to X-rates.com for the rates.


or else you have any other idea or the idea you told me that will work by clicking button? Like i click the button it will calculate the whole values and tell me the result.

Let me know the possible solution for this.

Thanks Bertie you helped me alot.

 

[abdullahsikandar]

Hi Bertie

Quick question the example you told me about the trigger event can you tell me how can i select a specific column and specific row like E30 and D30

Whenever user click on E30 it will automatically open up the listbox?

There are many ways to restrict what triggers a Change Event, e.g.,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
   'enter column number which triggers user form
   If Target.Column <> 1 Or _
      Target.Column <> 2 Then Exit Sub
   
   Load UserForm1
   UserForm1.Show
End Sub

For the above example you could put:
If Target.Column > 2 Then Exit Sub