Adding Form Controls on a Worksheet

Adding Form Controls on a Worksheet

Do you spend your days, and maybe your nights, talking about Excel with your friends? It’s amazing how quickly the time passes, while you chat about all the awesome features and tricks that you know. You know, exciting things like adding form controls on a worksheet.

This week, I was happy to spend some time on Skype, talking about Excel with my friend Chandoo. While he sweltered in India’s heat, I shivered in our Canadian snow, so it averaged out the perfect temperature for a friendly, and informative, discussion. And you’ll be able to hear what we said — Chandoo has posted a recording in the next episode of his Excel podcasts.

On the Podcast

I had a tough spot in Chandoo’s podcast lineup, because Mike Alexander was a recent guest, and he’s a hard act to follow! There was no need to worry though, because Chandoo is an excellent interviewer, and kept the conversation flowing.

We talked about running an Excel business, and how and why we got started. Chandoo learned that I began my business when dinosaurs were still roaming the earth, and a few other fun facts.

Talking About Worksheet Controls

Our main topic was worksheet controls, such as buttons, combo boxes and check boxes, and some of their benefits and drawbacks. A good chunk of our conversation was about drop down lists – data validation drop downs, Forms combo boxes and ActiveX combo boxes.

To learn more about them, read my article on 3 types of Excel drop down lists.

dropdowntypes01

Be Sure to Tune In

So this week, instead of listening to some boring music while you run on the treadmill, or ride the commuter train, you can listen to our Excel discussion. Go to the list of Chandoo’s podcasts, and click the links to play them.

My interview is numbered CP005, and you can listen by clicking the link. Chandoo will do some editing before he uploads it, and maybe even delete that spot where I incorrectly assured him that you could type in a Form Control combo box. Oops!

Despite that slip up, it was lots of fun, and I hope you enjoy it, and learn a few new things — like I did!

Video: 3 Types of Drop Down Lists

In the meantime, you can watch this short video, that I made a couple of years ago, to see how the 3 types of drop down lists work.

______________

3 thoughts on “Adding Form Controls on a Worksheet”

  1. I have a? Excel I’m using a input Sheet and a master sheet for all data I won’t to use 1 input sheet for all the data The input sheet would have to be cleared on every transaction. Is it possible to do this

  2. Hi Debra…
    Your tip “Data Validation and Combo Box with Named ranges” found at http://www.contextures.com/xlDataVal11.html, works very nicely and I even was able to twitch it to only get DV and ComboBox with two non-countinous cells; my DVs are dependent dynamic ranges reading from Excel “Tables” located in other worksheets.
    When replicating your code in my own worksheet (in Excel 2013), I have the problem that the 1st cell’s validation/combo box works, but the seconde cell’s combo box’s drop-down is not populated; the DVs all work; I have reviewed the code being used, and still and still cannot get the code to work correctly.
    My Code is:
    [Begin CODE]
    ‘Data Validation Drop Downs With Combo Box Using Named Ranges
    ‘from http://www.contextures.com
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
    ‘*****Define the cells where the DV w/CBx will be applied
    If Not Intersect(Target, Target, Range(“H3,H5”)) Is Nothing Then
    ‘*******************************
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects(“FVIngresoCombo”)
    On Error Resume Next
    With cboTemp
    ‘clear and hide the combo box
    .ListFillRange = “”
    .LinkedCell = “”
    .Visible = False
    End With
    On Error GoTo errHandler
    If Target.Validation.Type = 3 Then
    ‘if the cell contains a data validation list
    Cancel = True
    Application.EnableEvents = False
    ‘get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) – 1)
    With cboTemp
    ‘show the combobox with the list
    .Visible = True
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width + 15
    .Height = Target.Height + 10
    .ListFillRange = str
    .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    ‘open the drop down list automatically – this works for the 1st cell (h3) but not for the second cell
    Me.FVIngresoCombo.DropDown
    End If
    End If
    errHandler:
    Application.EnableEvents = True
    Exit Sub
    End Sub
    ‘=========================================
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.EnableEvents = False
    Application.ScreenUpdating = True
    If Application.CutCopyMode Then
    ‘allow copying and pasting on the worksheet
    GoTo errHandler
    End If
    Set cboTemp = ws.OLEObjects(“FVIngresoCombo”)
    On Error Resume Next
    With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = “”
    .LinkedCell = “”
    .Visible = False
    .Value = “”
    End With
    errHandler:
    Application.EnableEvents = True
    Exit Sub
    End Sub
    ‘====================================
    ‘Optional code to move to next cell if Tab or Enter are pressed
    ‘from code by Ted Lanham
    ‘—NOTE: if KeyDown causes problems, change to KeyUp
    Private Sub FVIngresoCombo_KeyDown(ByVal _
    KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    ‘Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
    Case 9 ‘Tab
    ActiveCell.Offset(0, 1).Activate
    Case 13 ‘Enter
    ActiveCell.Offset(1, 0).Activate
    Case Else
    ‘do nothing
    End Select
    End Sub
    [End CODE]
    Sorry for the lengthy comment; I thank you in advance for your interest and support.
    Kind regards, Daniel Murray (DMurray3)

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.