XL: How do I put UserForm Control Values into Worksheet? 2

[MeGustaXL]

Hi Peeps
I'm kinda embarrassed to ask this, because it's First-Grade stuff to most of you, I'm sure, but here goes...

I’m populating a list from a UserForm with 20+ controls on it; ComboBoxes, TextBoxes, ScrollBars, etc.
Once the user has finished entering/checking/picking/scrolling and so on, they click the OK button on the form and code similar to this appends the control values in the next blank row at the bottom of the list:

Private Sub btnOK_Click()
With Sheets("Lists&quot
.Range("A65536&quot.End(xlUp).Offset(1, 0).Select
With ActiveCell
.Offset(0, 1).Value = "First"
.Offset(0, 2).Value = "Second"
.Offset(0, 3).Value = "Third"

……. SO ON AND SO FORTH ……

.Offset(0, 20).Value = "Twentieth"
End With
End With
End Sub

Bearing in mind Bowers74’s advice about Selecting and Activating being bad practice, can anyone suggest a more efficient & elegant way of doing this?
I thought of looping all the control values into an array of some kind, then slapping the array contents onto the next empty row, but I don’t have a clue how to go about it!

Thanks in advance,

Chris

 

[xlbo]

Not much you can do with that I reckon - Problem is that you can loop thru the controls on the form but it may well not be in the order that you want them to appear on the worksheet. You're already using the WITH construct which means you are not selecting or activating anything except the initial cell - and that could be amended to

With Sheets("Lists&quot.Range("A65536&quot.End(xlUp).Offset(1, 0)
.Offset(0, 1).Value = "First"
.Offset(0, 2).Value = "Second"
.Offset(0, 3).Value = "Third"

……. SO ON AND SO FORTH ……

.Offset(0, 20).Value = "Twentieth"
End With
End With
End Sub


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Here you go Chris,

Private Sub UserForm_Click()
Dim i As Integer, r As Long
Dim ctrl As Control
i = 0
r = [A65536].End(xlUp).Row + 1
For Each ctrl In Me.Controls
    i = i + 1
    Cells(r, i) = ctrl.Value
Next ctrl
End Sub

That should get you started! ;-)

P.S. It was actually Skip's FAQ dealing with not selecting or activating to speed up your code.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Good point with the control order Geoff, which gave me this idea:

Chris,

give each of the controls a numeric tag (.Tag Property) for the order in which they should be listed and change my code from above to:

Private Sub UserForm_Click()
Dim i As Integer, r As Long
Dim ctrl As Control
r = [A65536].End(xlUp).Row + 1
For Each ctrl In Me.Controls
    i = Val(ctrl.Tag)
    Cells(r, i) = ctrl.Value
Next ctrl
End Sub

That should do the trick!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

Thanks Mike, that's close to cracking it!
Slight problem though; the form has 5 buttons on it, one of which is the OK button with the code attached. When that code runs, it puts "FALSE" in the cells that correspond to the control order of the buttons!

If poss, I'd like to construct the code like this:

Private Sub btnOK_Click()
Dim i As Integer, r As Long
Dim ctrl As Control
With Sheets("Lists&quot
i = 0
r = [A65536].End(xlUp).Row + 1
For Each ctrl In Me.Controls
If ctrl.Type <> &quot;Button&quot; Then
i = i + 1
Cells(r, i) = ctrl.Value
Next ctrl
End With
End Sub

But I don't know the syntax for detecting the control type (if such a test exists) - can it be done this way?

P.S. Apologies to Skip - I've just re-read and printed his FAQ!

Chris

 

[Bowers74]

If TypeName(ctrl) <> Button

But I would use my second bit of code and not give any Controls a tag if you don't want their values to be listed and then change the code to this:

Private Sub UserForm_Click()
Dim i As Integer, r As Long
Dim ctrl As Control
r = [A65536].End(xlUp).Row + 1
For Each ctrl In Me.Controls
    If IsNumeric(ctrl.Tag) then
        i = Val(ctrl.Tag)
        Cells(r, i) = ctrl.Value
    end if
Next ctrl
End Sub

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

Woo-Hooo! Thanks Mike!

Version 2 is the biz - another sparkler for you!

Chris

 

[Bowers74]

Thanks Chris,

Just a reminder faq707-4085 ;-)

Glad I could help!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

ooooh - like it a lot Mike - star from me as well

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244