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