-
vba combobox help
Hi,
I have an Excel File with 4 Column (Name, Address, Zip Code, City)
and an other Excel file where I've created a userform with a combobox which has its source from the Excel File with the Addresses etc...
Now I need to fill some cells in my current excel file with the user form depending of my combobox.
I know how to do it with just 1 column (the name column for example) but I don't know to do it for the complete row..
I hope it's clear (sorry for my bad english)
here an example:
EXCEL File 1
NAME ADDRESS ZIP CITY
Test TestAddress 9999 Mos Eisly
Excel File 2
NAME ADDRESS ZIP CITY
I need to fill these cells, using a combobox on my userform
here is my code:
[VBA]
Option Explicit
Public CustomerName
Private Sub CommandButton1_Click()
'Make Invoice Active
Sheets(3).Activate
Sheets("Agreement").Select
Dim zeil As Integer, spalt As Integer
zeil = ActiveSheet.UsedRange.Rows.Count 'last acktive row
spalt = ActiveSheet.UsedRange.Columns.Count 'last acktive column
'Export Data to worksheet
Cells(22, 1).HorizontalAlignment = xlLeft
Cells(22, 1).Font.Name = "Tahoma"
Cells(22, 1).Font.Size = 12
Cells(22, 1).Font.Bold = True
If Trim(CustomerComboBox.Value) = "" Then
CustomerComboBox.SetFocus
MsgBox "Please select a customer"
Exit Sub
End If
Cells(22, 1).Value = CustomerComboBox.Value 'Customer Name
'Exit
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim LastRowIni As Integer
'Set Scrollbar to Top
'ActiveSheet.ScrollTop = 0
'Make Invoice Active
Sheets(1).Activate
Sheets(3).Activate
'Customer ComboBox
Dim sDateiKundenListe As String, wbKundenListe As Workbook
If Not IsArray(CustomerName) Then
sDateiKundenListe = "D:\Customer\KundenListe.xlsx"
Application.ScreenUpdating = False
Application.StatusBar = "Loading Customer Address"
Set wbKundenListe = Application.Workbooks.Open(Filename:=sDateiKundenListe, ReadOnly:=True)
With wbKundenListe.Worksheets(1)
CustomerName = .Range(.Cells(2, 1), .Cells.SpecialCells(xlCellTypeLastCell))
End With
wbKundenListe.Close savechanges:=False
Application.ScreenUpdating = True
Application.StatusBar = False
End If
Agreement_UserForm1.CustomerComboBox.List = CustomerName
End Sub
[/VBA]
-
So what do you want to do exactly? To populate the ComboBoxes in your Form, or to save the values of your ComboBoxes to your new Excel? Please reformulate your problem a little to make it more clear..
-
no I know how to populate the ComboBox on my form, I've already did it.
My Problem is that the value of my combobox is fixed from 1 column in a separate Excel Sheet/file.
What I need is when I make my selection in the comboBox, that by clicking the button the entire row will be transmitted to my worksheet.
Again I have on Sheet or file 1 3 Columns A, B and C with 2 rows:
A1 B1 C1
A2 B2 C2
On File 2 I have a userform with a ComboBox which has actually File 1 as source but only Column A.
I want that when I select ex. A2 in the combobox.. That A2 B2 and C2 are returned on my worksheet
-
Then declare the value of your ComboBox as variable. Loop through the values of column "Name" in your Worksheet1 until you find the value of your ComboBox, and copy the entire row.
-
as you mentioned, you have four columns, so to copy all you can do smth like:
[VBA]
Dim wb as Workbook
Dim ag, xy as Worksheet 'xy is your target worksheet
Set ag = wb.Sheets("Agreement")
For Col = 1 to 4
xy.Cells(Row, Col) = ag.Cells(Row, Col)
Next
[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules