Form Automation: Populating a ComboBox during a dynamic userform build

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
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?

Code:
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:
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1dateofferFirst NameLast NameCompanyMailing Streetdept_divMailing CityMailing State/ProvinceMailing Zip/Postal CodeMailing CountryBusiness PhoneEmailEmployee SizeIndustryindustry-otherjob functionjob leveljob function-otherWebsiteCompany RevenueTitleLead Source DetailLead SourceSFDC CampaignCustom Event TypeLead ScoreLead StatusCampaign Member StatusLead Notes
210/30/2017Something InterestingBobStevensABC735 E Main StHENDERSONVLLECA94025US1234567890bob.gaffron@abc.com100 to 249Telecommunications: Telecommunications (General)Marketing: General ManagementSenior Manager$25 - 49.99 MillionMarketing: General Management - Senior ManagerWazzaupContent Syndication2017-Q4: Wazzaup - Content SyndicationMedia Download2OpenMedia DownloadThis lead has downloaded a copy of Something Interesting from Wazzaup
310/30/2017Something InterestingTaylorSchultzLocal Insurance225 S East StRenoNV86952US1234567890taylor.simonetto@locins.com1,000 to 2,499Insurance: Insurance (General)Marketing: General ManagementManager$100 - 499.9 MillionMarketing: General Management - ManagerWazzaupContent Syndication2017-Q4: Wazzaup - Content SyndicationMedia Download2OpenMedia DownloadThis lead has downloaded a copy of Something Interesting from Wazzaup
410/30/2017Something InterestingKristalKaneHarris Meat1001 HAXALL POINTOmahaNE54231US1234567890kristal.hurst@harrismeat.com250 to 499Finance: Finance (General)Marketing: Brand/Product MarketingManager$10 - 24.99 MillionMarketing: Brand/Product Marketing - ManagerWazzaupContent Syndication2017-Q4: Wazzaup - Content SyndicationMedia Download2OpenMedia DownloadThis lead has downloaded a copy of Something Interesting from Wazzaup
510/30/2017Something InterestingAllenTortReign Sovereign9200 Northpark DrWilmingtonDE1562US1234567890allen.krultz@reignsovereign.com500 to 999Insurance: Insurance (General)Marketing: General ManagementVP$500 - 999.9 MillionMarketing: General Management - VPWazzaupContent Syndication2017-Q4: Wazzaup - Content SyndicationMedia Download2OpenMedia DownloadThis lead has downloaded a copy of Something Interesting from Wazzaup
610/30/2017Something InterestingTongaJungQuick Loans1 Plaza Drlone TreeAK99801US1234567890tonga.hall@quickloans.com100 to 249Finance: Finance (General)Marketing: General ManagementManager$25 - 49.99 MillionMarketing: General Management - ManagerWazzaupContent Syndication2017-Q4: Wazzaup - Content SyndicationMedia Download2OpenMedia DownloadThis lead has downloaded a copy of Something Interesting from Wazzaup
710/30/2017Something InterestingStephenLondonSimple Bank34 S 7th AveTulsaOK74101US1234567890stephen.haskett@simplebank.com100 to 249Finance: BankingMarketing: General ManagementVP$100 - 499.9 MillionMarketing: General Management - VPWazzaupContent Syndication2017-Q4: Wazzaup - Content SyndicationMedia Download2OpenMedia DownloadThis 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
1Standard fields
2
3First Name
4Last Name
5Company
6Title
7Email
8Business Phone
9Mailing Street
10Mailing City
11Mailing State/Province
12Mailing Zip/Postal Code
13Mailing Country
14Industry
15Employee Size
16Company Revenue
17Website
18When
19Status
20Member Status
21Lead Source
22Lead Source Detail
23Campaign Name
24Interests
25Lead Score
26Lead Notes

<tbody>
</tbody>
Sheet1
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
What happens if you remove this?
Code:
     On Error GoTo setupFail
 

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
What happens if you remove this?
Code:
     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.
 
Last edited:

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
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

Active Member
Joined
Apr 18, 2007
Messages
419
Just for information's sake, here's what customFunc.setActive() looks like:

Code:
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

Active Member
Joined
Apr 18, 2007
Messages
419

ADVERTISEMENT

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

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,043
Office Version
  1. 2010
Platform
  1. Windows
Don't you need to transpose that range of values in order to populate the list ?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Atroxell

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

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
Sorry for the delay, I got pulled into a meeting.

I tried the transpose(). Looks like:

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,473
Messages
5,596,356
Members
414,061
Latest member
JJSB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top