Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Controls by Name
Like when you click the ok button on the userform?
Option Explicit Private Sub CommandButton1_Click() Dim myName As String Dim ctrl As Control Range("A1").Select For Each ctrl In Me.Controls If TypeOf ctrl Is MSForms.SpinButton Then myName = Mid(ctrl.Name, 5) 'not like =mid() in the worksheet ActiveCell.Value = Me.Controls("lab" & myName).Caption ActiveCell.Offset(0, 1) = Me.Controls("txt" & myName).Text ActiveCell.Offset(1, 0).Select If ActiveCell.Row = 10 Then ActiveCell.Offset(-10, 1).Select End If End If Next End Sub I wouldn't use a variable named Name. It may be ok with VBA, but it would confuse me. BOBODD wrote: I'm not sure of appropriate syntax on these forums, so I've noted comments or what I'm trying to do in {} brackets. How would I do this: Private Sub FillForm() Dim Name As String Range("A1").Select For Each SpinButton in UserForm Name = Mid(Spinbutton.Name, 5, {However long Spinbutton.Name is}) ActiveCell = Label.Value {Where Label.Name = "lab" & Name} ActiveCell.Offset(0, 1) = Textbox.Value {Where Textbox.Name = "txt" & Name} ActiveCell.Offset(1, 0).Select If ActiveCell.Row = 10 Then ActiveCell.Offset(-10, 1).Select End If Next End Sub I've been banging my head over this one for several days now. I can call each control individually, but I have a lot of controls and I end up with a Sub that's several pages in length. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Excel controls vs vba controls | Excel Programming | |||
ActiveX Controls vs Form Controls | Excel Discussion (Misc queries) | |||
Event procedures for controls added with Controls.Add | Excel Programming |