Excel/Combobox VBA code

Advertisement


Question
Dear Sir,

I am trying to insert several combo boxes which will be placed in column B and column C in the same Excel Sheet 1 (Excel 2010) for a total of 20 question for the local office(col B) and Headquarters(col C).
Comboboxes for column B; Format Control Info: Range ($L$2:$L$5) and cell link $L$1. Values are: NO/ YES/ NOT APPLICABLE/OTHER.
Comboxes for column C; Format Control Info: Range ($M$2:$M$5) and cell link $M$1. Values are: NO/YES/NOT APPLICABLE/ INFO SAME AS LOCAL OFFICE.
Example:  COL A          COL B          COL C
1- Registered date of Form 32 3/3/2011 Combox loc office  cmbox HQ

The user will read the question and answer according to their selection.
For Column B:
a)If they select option "NO", then USERFORM1 will appear to enter information.
b)If they select option "YES", this value to show.
c)If they select option "NOT APPLICABLE", this value to show
d)If they select option "OTHER", then USERFORM2 will appear to enter information
For Column C:
a)If they select option "NO", then USERFORM1(same userform for column B) will appear to enter information.
b)If they select option "YES", this value to show.
c)If they select option "NOT APPLICABLE, this value to show.
d)If they select option "INFO SAME AS LOCAL OFFICE", this value to show.

The VBA code I have been using is as follows:

Private Sub ComboBox1_Change()
OnCboChange (ComboBox1)
End Sub
Private Sub OnCboChange(ByRef cboBox As ComboBox)
If cboBox.Value = "NO" Then
Show.UserForm1
If cboBox.Value = "OTHER" Then
Show.UserForm2
End If
End Sub
Private Sub ComboBox2_Change()
OnCboChange (ComboBox2)
End Sub
Private Sub OnCboChange(ByRef cboBox As ComboBox)
If cboBox.Value = "NO" Then
Show.UserForm1
End If
End Sub

The above VBA code does not work. Please, could you kindly provide me with the correct VBA code for this task, and how to associate this code to the comboboxes( Will the VBA code provided by you work, once I am on the Excel sheet and try the dropdown boxes).

Thank you very much for your attention and help.

Answer
It's beyond the scope of this free service for me to design entire complex vba projects for you from scratch. When you ask a question, don't describe an entire project. Instead, choose one specific point you want to learn, and design a clear, simple, concrete example to illustrate this one point. In your question, be very very clear and precise about explaining the example.

Here's an example. Say you wanted a combo box, and if user selects NO you want UserForm1 to appear, and if user selects OTHER you want UserForm2 to appear.

I wouldn't use combo boxes, I'd use in-cell dropdowns created with Data, Data Validation, Settings, Allow: List, Source: =$L$2:$L$5. In L2:L5 are the options for this dropdown: NO, YES, NOT APPLICABLE, OTHER. Create this in-cell dropdown in cell B2, then copy it down the column as many times as you need (for this example, copy it from B2:B10).

Then create your user forms 1 and 2. Then in the Worksheet object in the Visual Basic Editor, enter this code:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rngDropDowns As Range, cellDropDown As Range
'code runs whenever there's a change on the worksheet, so it will
'execute if user changes one of the in-cell dropdowns
   Set rngDropDowns = Range("B2:B10")
'code will continue only if the changed cell(s) include
'one of the in-cell dropdowns in B2:B10
   Set cellDropDown = Intersect(rngDropDowns, Target)
   If Not (cellDropDown Is Nothing) Then
       Set cellDropDown = cellDropDown.Range("a1")
'if the changed in-cell dropdown is NO show UserForm1
       If cellDropDown = "NO" Then
         UserForm1.Show
       ElseIf cellDropDown = "OTHER" Then
'if the changed in-cell dropdown is OTHER show UserForm2
         UserForm2.Show
       End If
   End If
End Sub  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Stuart Resnick

Expertise

I can answer questions relating to MS Excel formulas, or to programming with vba (Visual Basic for Applications) in the Excel environment. Please follow the following guidelines: your question should focus on one specific issue you want to learn. It's beyond the scope of this free service for me to create entire projects or complex vba solutions for you from scratch. You should be able to do most of the work yourself, and come here when you need help with a specific point you're stuck on. ALWAYS include a simple, concrete example illustrating what you want to learn. Explain this example in detail in the text of your question (what data is in which cells of which sheets, etc). Be very precise about the results you want, using this sample to make the logic clear. Always keep these examples SIMPLE. Never e.g. use 18 worksheets in your example if using 2 or 3 will do. Never use ranges like AI567:BB865 if using a range like A1:B3 will do. Thanks.

Experience

As a consultant, I've designed Excel tools since the 90s, working for the Federal Reserve Bank, AT&T, and (currently) Gap Inc.

Education/Credentials
My only "education" comes from 2 decades of doing spreadsheet/programming work, with major SF Bay Area corporations such as AT&T, Federal Reserve Bank, and Gap Inc.

©2016 About.com. All rights reserved.