New to Userforms - need help on the output

Dear all,

I'm creating my first ever userform, and whilst I've created the form itself I need to tell it where to output.

I have named all the Textboxes, and I was planning to modify the following code to put the data into a worksheet:

---

Add code to the cmdAdd button

Select the cmdAdd button
On the Menu bar, choose View | Code.
This creates a procedure, where you can add your code.
Where the cursor is flashing, enter the following code:
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

End Sub


(With thanks, I've lifted this from www.contextures.com/xlUserform01.html)
---

Now I've realised that if I do it this way when I come to the "'copy the data to the database" part I have 68 items to put in, and some of the box names are quite long! Is there either a way to output all the box names so I can cut and paste them into the code, or can I get them to output both the name in the label next to the text box AND the text box itself, one above the other so I know what is what?

Any help on this would be gratefully received. As I'm sure you can tell, my VBA knowledge is virtually zero!

Many thanks,

Chris

Try something like:

'copy the data to the database
i=1
For Each c In me.Controls
if TypeName(c) = "TextBox" then
ws.Cells(iRow, i).Value = c.Value
i=i+1
Next

This will loop through all the textboxes on the form and place their values in cells along the row. They;ll be in the order they were created - not sure how to rearrange that order but if it comes to me I'll let you know.


HTH
Col

Col,

Thanks for that - it works perfectly! However, I've just realised there's a second part...

I also need to clear the values when I click Add Promotion. Is there an elegant way of doing it - like you have given me in the first part?

The code the people have used in the example I copied from is:

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

Many thanks,

Chris

Genius!

Many thanks,

Chris

Hi,
I am using Colofnature's code modified to set the controls' source on a userform:

Private Sub FillForm()
i = 1
For Each c In Me.Controls
If TypeName(c) = "TextBox" Or TypeName(c) = "ComboBox" Then
c.ControlSource = ActiveSheet.Cells(ActiveCell.Row, i).Address
i = i + 1
End If
Next
End Sub

Works like a charm

As you have well said the sequence of the controls is somehow set as per their sequence of creation.
I'd appreciate to find out how this sequence can be altered afterwards.

Any clue?

Gabor

Not an elegant solution - and certainly not VBA, but this is how I got around it.

On the first of my inputs I put the consecutive number 1 - 67 and added it to my list. This then gave me the locations of each of the entries. I then did a lookup on this into a new sheet that had headers, formatting etc etc.

Chris

Sorry guys, I was out.
I was hoping the tab order is making a difference but it's not.
Anyway I have made a generic UF file up and most probably that will be released with the communicated condition of the user has to watch out for the sequence of creation since that is the governing factor.
For some reason I like Colof's code since this is simple. I have extended it using the same logic and now it is filling the Labels in case the user wants that, but only once, when the UF is initiated.
I do not really grasp how the other solutions you suggested would work, but I guess they are in the direction of making the code more complicated isn't it?
Thanks.
Gábor

Gabor,

There's no code. I just output the data to one sheet and then use another sheet to collate the data on another so it is in order.

I'm totally clueless about VBA, so any solution I come up with will be much more clunky!

Chris