{"id":77,"date":"2018-10-30T15:41:49","date_gmt":"2018-10-30T15:41:49","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=77"},"modified":"2018-10-30T15:49:30","modified_gmt":"2018-10-30T15:49:30","slug":"vba-combobox","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2018\/10\/30\/vba-combobox\/","title":{"rendered":"VBA Combobox"},"content":{"rendered":"

This is a text and video example of how to implement a VBA Combobox:<\/strong><\/p>\n

On your Excel worksheet show your “Control Toolbar”. In Excel 2003 and prior, right click the standard toolbar (at the top of your screen) and choose “Control Toolbar”. In Excel 2007 and above click the round Microsoft Office Button at the top left or your screen. Then choose “Excel Options” at the bottom of the list. The choose “Developer” and select to show the developer toolbar.<\/p>\n

Add combo box to your worksheet, then choose to show the properties. Key in “A1” for the linked cell. Call one of your worksheets “data”, and for “ListFillRange” enter “data!A2:A5” (that’s the naming structure [worksheet name]![cell range]). Make sure you enter some values into the sheet called “data” and cells “A2-A5” or else you won’t show anything in your new combobox.
\nThis is what your worksheet should look like:
\nCell A2 = “red”
\nCell A3 = “blue”
\nCell A4 = “black”
\nCell A5 = “yellow”
\nNext – add some code to the “Change” event of the combobox. You will analyze the value of the changed item in the combobox.<\/p>\n

It’s going to look like this, and you’ll have to apply the principles to your situation:
\n******************************************************************* <\/p>\n

Private Sub cboColors_Change()\r\n   Dim strColor As String\r\n\r\n   strColor = Me.cboColors\r\n   strColor = Trim(strColor)\r\n\r\n   Select Case strColor\r\n      Case \"blue\"\r\n         MsgBox \"blue\"\r\n      Case \"black\"\r\n         MsgBox \"black\"\r\n      Case Else\r\n         MsgBox Me.cboColors\r\n      End Select\r\n   End Sub\t\r\n<\/pre>\n
*********************************************************************
\nNow watch how it’s done…<\/p>\n