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]

So what code do you have now and where do you get the 424 error?

 

[Atroxell]

ok, The 424 error was due to a mistype on my behalf. It's gone now.

I now have it working back to the "13-Type Mismatch" error after building Combobox2 (which is actually the first comboBox to be built--names are least of my worries at this point.) But the comboBox is not populating.

My debug window says:

lastCell = $AD$1Label1 recognized and built.
Current value of c  = First Name
Current value of topPos  = 90
Current value of (c.Row - 1) = 2
Label2 created successfuly
ComboBox2 created successfuly. 
ComboBox size set successfully.

The problem seems to remain in the transpose statement, which reads:

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

 

[Atroxell]

Back into this project. Still not finding the issue. Anything new on your end, Norie?