Adding ListBox selected item to another cell

ozimosko

New Member
Joined
Mar 25, 2022
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
First of all English is not my native language. Sorry for my misspellings.

I have an excel macro that I need to edit. I will briefly summarize the operations in Excel and then I will ask my question.
Operation summary: Customer, with buttons in Excel; enters a Sender account, then enters the Receiver account. After these operations, he presses the Add New Payment button. The Sender Account and the Recipient Account have to choose the same currencies. After the Buyer selects the account currency, the recipients of the selected currency appear in the list. After entering the necessary information, press the Add button and the information will be written to the cells in another sheet.

In the macro I currently have, I could not sort Alphabetically by Recipient Name, as the Recipient information is obtained with the ComboBox. For this reason, I converted ComboBox to ListBox and was able to sort it alphabetically.
But I can't select or use the data I pulled to ListBox. I need to select the Recipient Account I want in the ListBox and make a payment entry for it. (I need to print the data I selected here into cells in another sheet)
In short: I want to select and use the data drawn from another sheet into the Listbox.


I am using the following code to pull the Receiver Account Information:
VBA Code:
Dim i As Range
With Sheets("Alici Hesap Bilgileri")
    Set i = .Range("A1:I" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

Me.ListBox1.List = i.Value
Me.ListBox1.ColumnCount = 9
But after pulling this data, I can't select and use it :(


In the ComboBox, the data is drawn with the codes below. But it is not possible to sort alphabetically by Account Name in ComboBox.
I hope I'm wrong and the data in the ComboBox can be sorted alphabetically by Account Name(ReceiverName) :)

VBA Code:
Private Sub CbReceiver_Change()
    With CbReceiver
        .ColumnWidths = "20in"
        
        If CbReceiver.ListCount > 0 Then
            If Len(Me.CbReceiverCurrency.Value) = 0 Then
                tempIndex = .ListIndex + 2
            Else
                tempIndex = ReceiverIndexes(.ListIndex)
            End If
        
            ReceiverName = ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(tempIndex, 2)
            ReceiverCurrency = ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(tempIndex, 3)
            ReceiverIban = ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(tempIndex, 4)
            ReceiverTaxOffice = ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(tempIndex, 5)
            ReceiverTaxId = ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(tempIndex, 6)
            ReceiverCity = ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(tempIndex, 7)
            ReceiverAddress = ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(tempIndex, 8)
        End If
    End With
End Sub

Private Function FillReceiver(currencyTxt As String) As Boolean
    Set ReceiverIndexes = New ArrayList

    lrows = ThisWorkbook.Sheets("Alici Hesap Bilgileri").UsedRange.Rows.Count
    If lrows >= 2 Then
        With Me.CbReceiver
            .Clear
            If Len(currencyTxt) = 0 Then
                For i = 2 To lrows
                    .AddItem (ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 9) & " <-> " & ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 2) & "  <->  " & ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 4) & "  <->  " & ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 3) & " Hesap")
                Next i
                .ListIndex = 0
            Else
                For i = 2 To lrows
                    If ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 3) = currencyTxt Then
                        .AddItem (ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 9) & " <-> " & ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 2) & "  <->  " & ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 4) & "  <->  " & ThisWorkbook.Sheets("Alici Hesap Bilgileri").Cells(i, 3) & " Hesap")
                        ReceiverIndexes.Add i
                    End If
                Next i
                If .ListCount > 0 Then
                    .ListIndex = 0
                Else
                    .ListIndex = -1
                End If
            End If
        End With
    End If
    FillReceiver = True
End Function
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top