Assigning form fields to cells in VB

[Souljah]

I've been using the Create your own Forms Tutorial and customized it t
suit my needs, however how do I direct it to put certain fields i
certain places???

Also, it is an order form I'm creating and it would be Ideal if I coul
create multiple orders with one form entry - what is the best way to d
this??

thanks for any help/.

 

[Charles]

Souljah,

Create a userform with a command button.
Double click it an post this code.
You may need to modify it.


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Worksheets("sheet1").Activate
Dim myrange As Range
Dim a As Long
With Worksheets("Sheet1")
Set myrange = .Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))
End With
For a = 1 To myrange.Rows.Count
Next a
With UserForm1
myrange(a, 1).Value = Trim(TextBox1.Text)
myrange(a, 2).Value = Trim(TextBox2.Text)
myrange(a, 3).Value = Trim(TextBox3.Text)
End With
End Sub


HTH

Charle

 

[Souljah]

Private Sub chkUKmainland_Change()

If chkUKmainland = True Then
chkVAT.Enabled = True
Else
chkVAT.Enabled = False
chkVAT = False
End If
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Customer Details").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboTitle.Value
ActiveCell.Offset(0, 1) = txtFirstname.Value
ActiveCell.Offset(0, 2) = txtSurname.Value
ActiveCell.Offset(0, 3) = txtAddress.Value
ActiveCell.Offset(0, 4) = txtPostcode.Value
ActiveCell.Offset(0, 5) = txtCity.Value
ActiveCell.Offset(0, 6) = txtCountry.Value
ActiveCell.Value = cboType.Value
ActiveCell.Offset(0, 8) = txtCardnumber.Value
ActiveCell.Value = cboexpmonth.Value
ActiveCell.Value = cboexpyear.Value
ActiveCell.Offset(0, 11) = txtCardholder.Value
ActiveCell.Offset(0, 12) = txtIssue.Value

If chkUKmainland = True Then
ActiveCell.Offset(0, 13).Value = "Yes"
Else
ActiveCell.Offset(0, 13).Value = "No"
End If
If chkVAT = True Then
ActiveCell.Offset(0, 13).Value = "Yes"
Else
If chkUKmainland = False Then
ActiveCell.Offset(0, 14).Value = "No"
Else
ActiveCell.Offset(0, 14).Value = "No"
End If
End If
Range("A1").Select
End Sub
Private Sub txtFirstname_Change()

End Sub


Private Sub UserForm_Initialize()
With cboTitle
.AddItem "Mr"
.AddItem "Miss"
.AddItem "Mrs"
.AddItem "Master"
.AddItem "Dr"
End With
cboTitle.Value = ""
txtFirstname.Value = ""
txtSurname.Value = ""
txtAddress.Value = ""
txtPostcode.Value = ""
txtCity.Value = ""
txtCountry.Value = ""
With cboType
.AddItem "Visa/Delta/Electron"
.AddItem "MasterCard/EuroCard"
.AddItem "American Express"
.AddItem "Switch/Solo"
End With
cboType.Value = ""
With cboexpmonth
.AddItem "01"
.AddItem "02"
.AddItem "04"
.AddItem "06"
.AddItem "07"
.AddItem "08"
.AddItem "09"
.AddItem "10"
.AddItem "11"
.AddItem "12"
End With
cboexpmonth.Value = ""
cboexpyear.Value = ""
With cboexpyear
.AddItem "2004"
.AddItem "2005"
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
End With
cboexpmonth.Value = ""

chkUKmainland = False
chkVAT = False
cboTitle.SetFocus
End Sub

Here is how the form looks, how would I get what you suggested i
there?
Sorry, I AM a noob, what exactly does you code do

 

[onedaywhen]

The ComboBox has a List property which returns an array. Excel's Range
object has a Value property which can be set to equal a (variant)
array of the same size. Therefore, to exploit this you could do
something like this:

Private Sub cmdOK_Click()
ComboToRange cboTitle, Sheet1.Range("A2")
ComboToRange cboType, Sheet1.Range("B2")
' etc etc
End Sub

Private Function ComboToRange( _
ByVal Combo As MSForms.ComboBox, _
ByVal Target As Excel.Range _
) As Boolean

Dim a As Variant
a = Combo.List
Target.Cells(1, 1). _
Resize(UBound(a, 1) - LBound(a, 1) + 1) _
.Value = a
ComboToRange = True
End Function

BTW do we have noobs in the UK?

--