PDA

View Full Version : vba combobox help



Aranell
04-29-2013, 03:33 AM
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:


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

lionne
04-29-2013, 05:33 AM
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..

Aranell
04-29-2013, 06:09 AM
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

lionne
04-29-2013, 06:33 AM
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.

lionne
04-29-2013, 06:39 AM
as you mentioned, you have four columns, so to copy all you can do smth like:


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