Form Automation: Populating a ComboBox during a dynamic userform build

[Atroxell]

Hi,

I have 2 workbooks. One is the macro workbook, the other a source list. the macro workbook will be used for storing header data from the source and then later, formatting the source workbook correctly.

During the procedure that opens the source workbook, the macro searches for a matching vendor and event name in the first two rows of the "Headers" worksheet. If it's found, it continues processing-that works.

If the values are not found, it generates a userform with a column of labels containing the Standard field names along with a corresponding combobox that will allow the user to match the relationship between the standardized field names and the new field names found in the source workbook. Once they have been defined, the macro will post the new relationships to the "Headers" sheet in a new column and save it.

When I generate the userform to perform what I call "field equivalency", everything runs fine until it comes time to populate the comboboxes for the user to select the value. None of them populate. The problem is that because I am generating the form using code I cannot stop the code and step through it. I have added a number of debug statements in an effort to detail where it is stopping, as well as a line to specify where I get the error : "error 13: Type mismatch". I have been looking at this for 3 days now and I cannot find it.

I tried moving a few lines of code around to see if maybe the different workbook focus would matter, but it does not have any effect. I have also tried both methods shown below to populate the list, but neither works.

Can anyone tell me why the combobox won't populate?

Sub fieldEquivalencyForm()
      Dim varCmboBox As Range: Dim h As Range
      Dim chkVal2 As String
      Dim lRow As Long
      Dim lastCell As Variant
      Dim listRowCnt As Long
      
      On Error GoTo setupFail
      customFunc.setActive                              [COLOR=#ff0000] ' special function to ensure that the source workbook is, in fact, the source workbook.       ' This works perfectly, so I know that wsSource is set correctly [/COLOR]
      [COLOR=#ff0000]' Last column address in the first row of (active) source file.[/COLOR]
      lastCell = Cells(1, Columns.Count).End(xlToLeft).Address
      
     [COLOR=#ff0000] ' Create userform[/COLOR]
      Set objFrm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
      customProc.nameForm ("Field Equivalency Definitions")   [COLOR=#ff0000]'Sets the form caption[/COLOR]
  
    [COLOR=#ff0000]  ' Define the top label position and contents.[/COLOR]
      Set ctlLabel = objFrm.Designer.Controls.Add("Forms.Label.1", "Label1", True)
      ctlLabel.Caption = "Match new vendor/event field names to your standard field names. " & vbCr & "Click 'Save & Close' to accept them as they are, or replace with your preferred names and click 'Save & Close'."
      ctlLabel.Height = 66: ctlLabel.Width = 400: ctlLabel.Left = 6: ctlLabel.Top = 18: ctlLabel.Font.Size = 11: ctlLabel.TextAlign = fmTextAlignCenter: ctlLabel.Font.Name = "Callibri"
      ThisWorkbook.Sheets("Headers").Activate
      Debug.Print "Label1 recognized and built."
      
      With objFrm
            [COLOR=#ff0000]' Build out the text boxes for however many rows are in the dynamic range 'stdFieldNames'[/COLOR]
            topPos = 90
            For Each c In ThisWorkbook.Sheets("Headers").Range("stdFieldNames")
                  Debug.Print "Current value of c  = " & c.Value
                  If ctlLabel.Name <> "Label1" Then topPos = topPos + 18 Else: topPos = 90
                        Debug.Print "Current value of topPos  = " & topPos              [COLOR=#ff0000]' Just a marker to see where it is stopping.[/COLOR]
                        
                        [COLOR=#ff0000]' Static standard field names[/COLOR]
                        Set ctlLabel = objFrm.Designer.Controls.Add("Forms.Label.1", "Label" & (c.Row - 1), True)
                        Debug.Print "Current value of (c.Row - 1) = " & (c.Row - 1)             [COLOR=#ff0000] ' Just a marker to see where it is stopping."[/COLOR]
                        
                        specData = "Label" & (c.Row - 1):
                        With ctlLabel
                              .Height = 15.6: .Width = 138: .Left = 36: .Caption = c.Value: .Top = topPos
                        End With
                        Debug.Print specData & " created successfuly"               [COLOR=#ff0000]' Just a marker to see where it is stopping."[/COLOR]
                        
                        Set newComboBox = objFrm.Designer.Controls.Add("Forms.ComboBox.1", "ComboBox" & (c.Row - 1), True)
                        Debug.Print "ComboBox" & (c.Row - 1) & " created successfuly. "               [COLOR=#ff0000]' Just a marker to see where it is stopping."[/COLOR]
                        With newComboBox
                              .ListRows = 8: .Height = 15.6: .Width = 138: .Left = 192: .Top = topPos: .Clear
                        End With
                        Debug.Print "ComboBox size set successfully."               ' Just a marker to see where it is stopping."
                        
                        [COLOR=#ff0000]'####################This stops here.#####################[/COLOR]
                        [COLOR=#ff0000]' error 13 : Type Mismatch[/COLOR]
                        newComboBox.List = Sheets(wsSource).Range("A1:" & lastCell).Value
[COLOR=#ff0000]                        'For Each h In wsSource.Range("A1:" & lastCell)
                        '      newComboBox.AddItem h.Value
                        'Next h[/COLOR]
                        Debug.Print "ComboBox " & (c.Row - 1) & " list recognized and built."
                        Set newComboBox = Nothing
            Next c
.
.
.
End Sub

Here's a sample of what my source data looks like:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	date	offer	First Name	Last Name	Company	Mailing Street	dept_div	Mailing City	Mailing State/Province	Mailing Zip/Postal Code	Mailing Country	Business Phone	Email	Employee Size	Industry	industry-other	job function	job level	job function-other	Website	Company Revenue	Title	Lead Source Detail	Lead Source	SFDC Campaign	Custom Event Type	Lead Score	Lead Status	Campaign Member Status	Lead Notes
2	10/30/2017	Something Interesting	Bob	Stevens	ABC	735 E Main St		HENDERSONVLLE	CA	94025	US	1234567890	bob.gaffron@abc.com	100 to 249	Telecommunications: Telecommunications (General)		Marketing: General Management	Senior Manager			$25 - 49.99 Million	Marketing: General Management - Senior Manager	Wazzaup	Content Syndication	2017-Q4: Wazzaup - Content Syndication	Media Download	2	Open	Media Download	This lead has downloaded a copy of Something Interesting from Wazzaup
3	10/30/2017	Something Interesting	Taylor	Schultz	Local Insurance	225 S East St		Reno	NV	86952	US	1234567890	taylor.simonetto@locins.com	1,000 to 2,499	Insurance: Insurance (General)		Marketing: General Management	Manager			$100 - 499.9 Million	Marketing: General Management - Manager	Wazzaup	Content Syndication	2017-Q4: Wazzaup - Content Syndication	Media Download	2	Open	Media Download	This lead has downloaded a copy of Something Interesting from Wazzaup
4	10/30/2017	Something Interesting	Kristal	Kane	Harris Meat	1001 HAXALL POINT		Omaha	NE	54231	US	1234567890	kristal.hurst@harrismeat.com	250 to 499	Finance: Finance (General)		Marketing: Brand/Product Marketing	Manager			$10 - 24.99 Million	Marketing: Brand/Product Marketing - Manager	Wazzaup	Content Syndication	2017-Q4: Wazzaup - Content Syndication	Media Download	2	Open	Media Download	This lead has downloaded a copy of Something Interesting from Wazzaup
5	10/30/2017	Something Interesting	Allen	Tort	Reign Sovereign	9200 Northpark Dr		Wilmington	DE	1562	US	1234567890	allen.krultz@reignsovereign.com	500 to 999	Insurance: Insurance (General)		Marketing: General Management	VP			$500 - 999.9 Million	Marketing: General Management - VP	Wazzaup	Content Syndication	2017-Q4: Wazzaup - Content Syndication	Media Download	2	Open	Media Download	This lead has downloaded a copy of Something Interesting from Wazzaup
6	10/30/2017	Something Interesting	Tonga	Jung	Quick Loans	1 Plaza Dr		lone Tree	AK	99801	US	1234567890	tonga.hall@quickloans.com	100 to 249	Finance: Finance (General)		Marketing: General Management	Manager			$25 - 49.99 Million	Marketing: General Management - Manager	Wazzaup	Content Syndication	2017-Q4: Wazzaup - Content Syndication	Media Download	2	Open	Media Download	This lead has downloaded a copy of Something Interesting from Wazzaup
7	10/30/2017	Something Interesting	Stephen	London	Simple Bank	34 S 7th Ave		Tulsa	OK	74101	US	1234567890	stephen.haskett@simplebank.com	100 to 249	Finance: Banking		Marketing: General Management	VP			$100 - 499.9 Million	Marketing: General Management - VP	Wazzaup	Content Syndication	2017-Q4: Wazzaup - Content Syndication	Media Download	2	Open	Media Download	This lead has downloaded a copy of Something Interesting from Wazzaup

<tbody>
</tbody>

Sheet1​

And here are my standard headers that I want to compare the headers above to, which are the range named "stdFieldNames" in the "Headers" sheet of my macro workbook:

	A
1	Standard fields
2
3	First Name
4	Last Name
5	Company
6	Title
7	Email
8	Business Phone
9	Mailing Street
10	Mailing City
11	Mailing State/Province
12	Mailing Zip/Postal Code
13	Mailing Country
14	Industry
15	Employee Size
16	Company Revenue
17	Website
18	When
19	Status
20	Member Status
21	Lead Source
22	Lead Source Detail
23	Campaign Name
24	Interests
25	Lead Score
26	Lead Notes

<tbody>
</tbody>

Sheet1​

 

[Norie]

What happens if you remove this?

     On Error GoTo setupFail

 

[Atroxell]

What happens if you remove this?

     On Error GoTo setupFail

Still get the same error... and then the macro workbook shuts down and I am left with the source book, Probably due to the calling procedure also having a On Error GoTo statement.

 

[Atroxell]

I edited the primary procedure fail statement--it was closing the macro workbook by default. Not what I wanted it to do. But I was able to retain the macro workbook by remming the close statement.

 

[Norie]

Have you checked what the value of lastcell is?

I know you can't do that in the 'normal' way via debugging but you could output the value like this.

Debug.Print lastcell

PS The reason I'm asking about lastcell is that as far as I can see it's the most likely culprit for the error you describe.

 

[Atroxell]

Just for information's sake, here's what customFunc.setActive() looks like:

Function setActive()
      For Each WB In Application.Workbooks
            If WB.Name <> ThisWorkbook.Name Then
                  Set wbSource = WB
                  wbSource.Activate
                  Set wsSource = ActiveSheet
                  setActive = True
            End If
      Next WB
End Function

 

[Atroxell]

Just added it to the debugger. Result = "lastCell = $AD$1"

 

[NoSparks]

Don't you need to transpose that range of values in order to populate the list ?

 

[Norie]

Atroxell

I've tried your code and I'm getting errors but not the one you describe.

 

[Atroxell]

Sorry for the delay, I got pulled into a meeting.

I tried the transpose(). Looks like:

newComboBox.List = Application.Transpose(Sheets(wsSource).Range("A1:" & lastCell).Value)

I made the other changes you suggested and am now getting "424-object required."