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.
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.
______________
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
@Ken, there is a data entry form example on my website that should help you get started:
http://www.contextures.com/xlForm02.html
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)