Open your developer panel up by going to the “Developer” tab and clicking on “Visual Basic.”Īdd the following code to the developer’s pane for sheet 1: Private Function GetListCell(rowNum As Integer) As String It needs to work with 1 item and 1,000 items.

We need a way to access the list items contained in column “A” and we need it to be able to work without knowing in advance how many items are in the list. A value should be assigned to the Function so that it can be returned.Functions work like variables in that they should have a type declared.Functions are designated with a “Functions” keyword, Subroutines use “Sub”.functions return some sort of value, subroutines do not.The largest differences you are concerned with: Functions and subroutines have a lot of similarities and some differences. If you recall, we worked with subroutines in earlier tutorials. Make sure to change the combo box name to “MergedList” and play around with its settings if you want. The worst you can do is mess up the element and have to delete it and remake it. Play with font, color, and experiment with the other options a bit. Make whatever style changes you want on the button. You can click on your button, making sure you have “Design Mode” turned on, and making “ListMerge” the name of the button. On the “Developer” tab, you can move your mouse over to properties and click it. You go through a similar process for a combo box, only you select “Combo Box” from the Active X controls.Ģ. Select the “Command Button” ActiveX Control and click anywhere within your Excel sheet to place the button.

Go to the “Developer” tab and click the “Insert” drop-down.

We must save the document as a macro-enabled Excel fileĬreating buttons should be familiar to you by now if you have taken the earlier tutorials, but here it is again.The items list may change in size and the application needs to consider a variable length list.Move all items in the list from column “A” into a combo box for easier selection.We don’t know, it is a variable length list. Additionally, we want to be able to allow for new items to be added to the list, or maybe the list shrinks. We want to be able to do that with a push of a single button, the “Merge List” button in blue shown above. The items in column “A” need to be merged together into the combo box we have on the right. You can view all available tutorials on the Excel VBA Tutorials page. It is recommended that if you are new to VBA, you begin with Excel Visual Basic #1: Getting Started. This is the third part of the Excel Visual Basic tutorial series.

Visual basic for excel for loop