Looping through select userform controls to print values in sheet

Question

NEW UPDATE-Using a Collection to Map Destinations of Userform Controls

Using a collection to reference userform controls and the location to input

I created a collection, as shown below to reference the position to print the userform controls, to the sheet. I tested this on a sheet "Sheet1" with the controls being "subTaskID" and "TextBoxsubtask".

I get a

"ByRef argument type mismatch"

on the ctlMap variable. I tried to follow PeterT's explanation below.

'' COLLECTION START DELETE FROM HERE IF WRONG
Sub Userform_Intialize()

Set ctlMap = New Collection
With ctlMap
    .Add item = "Sheet1!B1", key:="SubTaskID"
    .Add item = "Sheet!B2", key:="TextBoxsubtask"
End With

End Sub 

'Macro to populate sheet

Private Sub CommandButton1_Click()

    Dim ctl As Variant
    For Each ctl In Me.Controls
        If ControlExists(ctlMap, ctl.Name) Then
            Range(ctlMap(ctl.Name)) = ctl.Value
        End If
    Next ctl

End Sub

Original QUESTION

I have a userform to populate an Excel file regarding project tasks.

I created code linking each control to the correct column and cell number. I would like to be more efficient in my code to speed things up. I do not want labels, and certain textbox values to be inputted and also if a control is blank to skip to the next.

I can't order the loop through the controls correctly as it does not print in the desired cells.

How do I rearrange this order? Also how to I prevent it from printing false for an empty value?

I created an array with the required control names, however I get an error

same object required when running.

lastrowST = subtaskws.range("A" & Rows.Count).End(xlUp).row

Dim Ctrl As Control, col As Long, range As range, userformorder As Variant

With AddTask
userformorder = Array(.SubTaskID, .TextBoxsubtask, .ComboBoxDeliverableFormat, .TextBoxcheckedcomplete, .TextBoxformat, .TextBoxacceptancecriteria, .BudgetWorkloadTextBox, .AWLTextBox, .ComboBoxOwner, .TextBoxTDSNumber, .TextBoxMilestone, .TextBoxTargetDeliveryDate, .ComboBoxW, .ComboBoxI, .ComboBoxe, .TextBoxP, .TextBoxLevel, .TextBoxInputQuality, .TextBoxNewInput, .TextBoxDelay, .TextBoxInternalVV, .TextBoxReviewer, .TextBoxDelivered, .ComboBoxNumIterations, .ComboBoxAcceptance, .ComboBoxProgress, .ComboBoxStatus, .ComboBoxFlowChart, .TextBoxActivitySheet, .TextBoxEvidenceofDelivery, .TextBoxComments)

For Each range In subtaskws.range("A" & lastrowST + 1 & ":AE" & lastrowST + 1 & "")
    For Each Ctrl.Value In userformorder
        If Ctrl.Value <> "" Then
            range.Value = Ctrl.Value
        Else
        End If
    Next Ctrl
Next range
End With

Looks like your loops are the wrong way round. What is `col` supposed to do? — SJR, Jan 07 '20 at 18:15
col is just the column counter, so moves column. it is redundant now — Lawrence Forster, Jan 07 '20 at 18:20
Where are the values supposed to go? As it stands you are likely to be overwriting. — SJR, Jan 07 '20 at 18:34
The Values are inserted into the range along the new row, (lastrowST + 1). — Lawrence Forster, Jan 07 '20 at 18:37
No, i want them to be put in the correct columns, so assumed if looping through horizontal range it will go to the right? I have set up the array so that it is in same order as the columns needed. — Lawrence Forster, Jan 07 '20 at 18:42
OK, have posted an answer, would have been quicker if you'd mentioned that in your original question. — SJR, Jan 07 '20 at 18:51
Please see original post, i havr updated the question with my current progress — Lawrence Forster, Jan 09 '20 at 09:47

PeterT · Accepted Answer · 2020-01-08T15:57:47.107

One method I often use creates a map between a control and a cell. I use a Collection for this and setup the map in the Initialize event:

Private Sub UserForm_Initialize()
    Set ctlMap = New Collection
    With ctlMap
        .Add Item:="Sheet1!B1", key:="TextBox1"
        .Add Item:="Sheet1!B2", key:="TextBox2"
        .Add Item:="Sheet1!B3", key:="CheckBox1"
    End With
End Sub

My userform looks like this

and the data on a worksheet looks like this

So now to populate the controls, it's just a loop through ALL the controls on the userform. Because we're using the controls map, only the controls that are mapped are populated. The Select statement is necessary because not all of the controls have the same property to access the data. For example, a TextBox uses ctl.Text but a CheckBox uses ctl.Value.

EDIT: my memory was faulty and I referred to some older code of mine for the example. This answer provides a good explanation of why you want to use .Value to access the values for the controls. This means you could probably remove the Select statement and replace all of it with a simple ctl.Value = Range(ctlMap(ctl.Name)) to take care of all assignments. Just in case the developer would like to perform specific logic based on the control, I'll leave the Select statement intact.

Private Sub PopulateControls()
    Dim ctl As Variant
    For Each ctl In Me.Controls
        If ControlExists(ctlMap, ctl.Name) Then
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = Range(ctlMap(ctl.Name))

                Case "CheckBox"
                    ctl.Value = Range(ctlMap(ctl.Name))

            End Select
        End If
    Next ctl
End Sub

Or optionally

Private Sub PopulateControls()
    Dim ctl As Variant
    For Each ctl In Me.Controls
        If ControlExists(ctlMap, ctl.Name) Then
            ctl.Value = Range(ctlMap(ctl.Name))
        End If
    Next ctl
End Sub

Private Sub ControlsToWorksheet()
    Dim ctl As Variant
    For Each ctl In Me.Controls
        If ControlExists(ctlMap, ctl.Name) Then
            Range(ctlMap(ctl.Name)) = ctl.Value
        End If
    Next ctl
End Sub

The entire code for the userform is below

Option Explicit

Private ctlMap As Collection

Private Sub PopulateControls()
    Dim ctl As Variant
    For Each ctl In Me.Controls
        If ControlExists(ctlMap, ctl.Name) Then
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = Range(ctlMap(ctl.Name))

                Case "CheckBox"
                    ctl.Value = Range(ctlMap(ctl.Name))

            End Select
        End If
    Next ctl
End Sub

Private Sub ControlsToWorksheet()
    Dim ctl As Variant
    For Each ctl In Me.Controls
        If ControlExists(ctlMap, ctl.Name) Then
            Select Case TypeName(ctl)
                Case "TextBox"
                    Range(ctlMap(ctl.Name)) = ctl.Text

                Case "CheckBox"
                    Range(ctlMap(ctl.Name)) = ctl.Value

            End Select
        End If
    Next ctl
End Sub

Private Function ControlExists(ByRef thisCollection As Collection, _
                               ByVal key As String) As Boolean
    On Error GoTo SKIP
    ctlMap.Item key
    ControlExists = True
SKIP:
End Function

Private Sub CloseButton_Click()
    Me.Hide
End Sub

Private Sub PopulateButton_Click()
    PopulateControls
End Sub

Private Sub WriteButton_Click()
    ControlsToWorksheet
End Sub

Private Sub UserForm_Initialize()
    Set ctlMap = New Collection
    With ctlMap
        .Add Item:="Sheet1!B1", key:="TextBox1"
        .Add Item:="Sheet1!B2", key:="TextBox2"
        .Add Item:="Sheet1!B3", key:="CheckBox1"
    End With
End Sub

@SJR thanks for the reminder. I've updated the code in the answer with an explanation. — PeterT, Jan 07 '20 at 20:21
Thanks PeterT, I am looking to use some of your suggestion to see if it speeds the process up! Can you loop put userform names in to an array and then lop through the array to save on specifying each case? — Lawrence Forster, Jan 08 '20 at 14:16
Use the optional version of `PopulateControls` and you won't need to specify each case. Just initialize the map collection when the form is created as in the example. — PeterT, Jan 08 '20 at 14:25
Sorry im confused, i was following the controls to worksheet function as i want the values from the userform into the worksheet — Lawrence Forster, Jan 08 '20 at 14:40
I updated the code above to show the optional `ControlsToWorksheet` sub without the `Case` statement. I'm less certain about your last comment though. Insert some `Debug.Print` statements to figure out what the `ctl.Name` and `ctlMap(ctl.Name)` values are. If they aren't what you expect, you should re-check how you initialized your map. — PeterT, Jan 08 '20 at 16:00
Thanks for your help Peter, really appreciate it! Just to clarify, if i was wanting to write the values inputted into the controls to the worksheet, would the set up of the collection look the same as your collection? Or would i tbe different as from what i understand from your exmaple is you are populating the controls from the worksheet, the reverse of what i am trying to do @PeterT — Lawrence Forster, Jan 09 '20 at 09:32
The code in my example works for both populating the controls from worksheet cells, and for writing the values of the form controls to the worksheet. You could use either one of those functions or both, depending on your design. If you don't want to populate the controls from the worksheet, then you can skip that method and just use the `ControlsToWorksheet` method. — PeterT, Jan 09 '20 at 14:13
Also, the additional code you posted in the question should probably be moved to a new question if you can't get it to work. You have at least one error in the `.Add item = "Sheet!B2", ...` line -- it should be `"Sheet1!B2"` (you're missing the `"1"`). — PeterT, Jan 09 '20 at 14:18
Your additional code also has a couple other problems. The statement to add items to the collection should use `Item:="...` and not `Item = "...`. This is a syntax error and the code will not run. Secondly, you MUST declare the `UserForm_Initialize` sub as `Private` or it will not execute. The `ctlMap` is not created unless the `Initialize` method is run. Other than that, after creating a simple userform using your (corrected) code and adding the `ControlExists` function, it works fine for me. — PeterT, Jan 09 '20 at 14:30

score 0 · Answer 2 · answered Jan 07 '20 at 18:46

0

I think this will do what you want. There is some good stuff in Peter's answer which would be worth perusing.

lastrowST = subtaskws.range("A" & Rows.Count).End(xlUp).Row

Dim Ctrl As Variant, range1 As range, userformorder As Variant, col As Long

userformorder = Array("SubTaskID", "TextBoxsubtask", "ComboBoxDeliverableFormat", "TextBoxcheckedcomplete") 'etc

For Each Ctrl In userformorder
    If AddTask.Controls(Ctrl).Value <> "" Then
        subtaskws.range("A" & lastrowST + 1).Offset(, col).Value = AddTask.Controls(Ctrl).Value
    End If
    col = col + 1
Next Ctrl

End With

answered Jan 07 '20 at 18:46

SJR

22,986
6
18
26

Thanks works amazing! peter's stuff looks good but i dont quite understand all of it!! – Lawrence Forster Jan 07 '20 at 19:06
Question, how would get the code to skip certain columns, such as column 7 – Lawrence Forster Jan 07 '20 at 19:06
could i change the range to go up to column 7 and then start again at column 9 – Lawrence Forster Jan 07 '20 at 19:08
You could check the value of `col` and if it's 7 add 2 instead of 1. – SJR Jan 07 '20 at 19:11
1

You might want to start getting in the habit of accepting answers at SO if they help. – SJR Jan 07 '20 at 19:55

Looping through select userform controls to print values in sheet

2 Answers2