Data Validation Combo Box

[wholesaleguy]

I am trying to create a combo box over a data validation cell so the I can change the font and size of the input options. I found good information related to this at: http://www.contextures.com/xlDataVal14.html. I am having a problem creating and naming the combo box. The properties box menu shown in the instructions is much more detailed than mine and I wonder if this is a version issue or if I am not creating the combo box correctly. I am using 2007.

Any ideas?

 

[VoG]

You need to insert an ActiveX combobox.

 

[wholesaleguy]

I'll give that a try. Thanks.

 

[wholesaleguy]

That worked like a charm... Is there an option or more code that I can add that would move to the right one cell after I choose the option in the list that i want?
This behavior would eliminate having to hit enter or tab after each selection.

Thanks again for your help.

 

[VoG]

In the link that you posted there is code to do that if you press Tab.

 

[wholesaleguy]

That coding works fine, but I am trying to eliminate one keystroke by moving to the next cell to the right when you left click with the mouse to choose the option in the list. Any ideas?

 

[VoG]

Maybe try this but I don't know how it will interact with the other code. In Design Mode right click the combobox, select View Code and paste in

Private Sub ComboBox1_Change()
ActiveCell.Offset(, 1).Select
End Sub

 

[wholesaleguy]

Here is my code for this worksheet: now that I have inserted combo boxes I have lost the ability to run the first section of code correctly. Any ideas on how to make this work, or is not being able to do this just a trade off for using combo boxes?
PS: The target cells are different data validations that narrow down choices and need to clear out if you make a change somewhere further up the chain.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Target.Address = "$B$3" Then
Range("C3:E3").ClearContents
End If
If Target.Address = "$C$3" Then
Range("D3:E3").ClearContents
End If
If Target.Address = "$D$3" Then
Range("E3").ClearContents
End If
If Target.Address = "$B$10" Then
Range("C10:E10").ClearContents
End If
If Target.Address = "$D$10" Then
Range("E10").ClearContents
End If
If Target.Address = "$C$10" Then
Range("E3").ClearContents
End If
sub_exit:
Application.EnableEvents = True
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
On Error GoTo errHandler

If Target.Count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
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 + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
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
'====================================