Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Compile Error Ambiguous Name Detected

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello, Im using a script that allows me to autocomplete a data validation list using a combo box and the code shown below. However I am getting a compile error ambigous name detected and the following line is highlighted
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)"

I know its because this is used twice but I am not sure what to do in order to fix this. I would appreciate some help on this.

Thank you.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("combobox1")
  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
    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 + 5
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
Private Sub combobox1_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

 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("combobox2")
  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
Private Sub combobox2_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
 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("combobox3")
  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
Private Sub combobox3_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



Private Sub CommandButton1_Click()
Range("C10").Select
    Selection.ClearContents
    Range("E14").Select
    Selection.ClearContents
    Range("G14").Select
    Selection.ClearContents
    Range("C12").Select
End Sub




View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics







Hi there,

I have quite a few things going on. I need to know how to incorp Code:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  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
    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 + 5
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

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

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

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
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
'====================================


With the following code

Code:

 If Target.Cells.Count = 1 And Not (Application.Intersect(Target, Range("A1:D100")) Is Nothing) Then
        If 1 < Application.CountIf(Range("a1:D100"), Target.Value) Then
            Application.EnableEvents = False
                MsgBox Target.Value & " Already Has a Shift on " & ActiveSheet.Name & "!", vbCritical, "WARNING!"
                
                Target.ClearContents
                
            Application.EnableEvents = True
        End If
    End If


Both Codes need to work together.


Hi,

Okay, I did away with the merged cells, and now I have a combo box in every cell that I want it in.

My problem is, I would like to format each combo box seperately. Every cell that has a combo box in it has data vakidation activated. I copy and pasted code to get it to work. It works great, but I would still like to change the combo box size and tab and enter functions for each individual one. I created more than one combo box but I kept getting an error on them. Any help or knowledge would be greatly appreciated.

I am posting just the code that I copy and pasted.

Code:

'=========================================
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("Locations")
  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 + 195
      .Height = Target.Height + 4
      .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 Locations_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 4).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, -1).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================


Thank you in advance.


I'm having a problem with the screen flickering whenever a different cell is selected due to a ComboBox. I searched the forums and wasn't able to find any code that helped my problem. The fact that I wouldn't know where to insert the proper code to fix it even if I could find it doesn't help. That's why I assume the "optional code" listed at the end that I found online does not work properly because it probably isn't in the right spot. Any help would be appreciated. Here's the code:

Code:

'=========================================
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("Staff4Combo")
  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 + 22
      .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
'====================================





Hello there,

I have a workbook with appointments that I make daily. It contains addresses and dates mainly. It contains few sheets and the more important a

Sheet 1: "Master Search" and of course sheet 2 (contains data and diary)


In sheet one in Cell E2 I have got a combo box where user can type an address. The code that I have is shown below:

Code:

Option Explicit

Private Sub TempCombo_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
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .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
  
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

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

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub



What I would like is when user start typing and address eg. 12 Elt, to show some more possibilities such as, 12 Eltham, 12 Elton, 12 Eltous etc. and show them at the drop down whithout having to press it. Preferably I would like 12 max showed at the same time without scrolling. Can anyone please help?



Many Thanks


I would like to combine the two Worksheet_SelectionChange statements into 1 to avoid the "Compile error: Ambiguous name detected" error that I have caused.

VB:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
     'The 1st method is coded to launch when a curtain cell is  selected
    If Target.Address = "$G$8" Or Target.Address = "$G$14" Or Target.Address = "$G$17" Then 
        CalendarFrm.Show 
    End If 
     'another method is to have it show if a cell that is formatted as a date is selected...see below
    If Target.NumberFormat = "m/d/yy;@" Then 
        CalendarFrm.Show 
    End If 
     'another method is using Select Case...see below
    Select Case Target.NumberFormat 
    Case Is = "m/d/yy", "m/d/yyyy", "m/d/yy", "mm/dd/yy", "yyyy-mmm-dd" 
        CalendarFrm.Show 
         'Case Else
         '    MsgBox "Not a valid date format!"
    End Select 
End SubOption Explicit 
Private Sub Calendar1_Click() 
End Sub 
Private Sub TempCombo_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 
        ActiveCell.Offset(0, 1).Activate 
    Case 13 
        ActiveCell.Offset(1, 0).Activate 
    Case Else 
         'do nothing
    End Select 
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 
        Application.EnableEvents = False 
        str = Target.Validation.Formula1 
        str = Right(str, Len(str) - 1) 
        With cboTemp 
            .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.EnableEvents = True 
    Application.ScreenUpdating = True 
    Exit Sub 
errHandler: 
    Resume exitHandler 
End Sub 
 
[B][/B] 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Hi,

How to adjust this code when it is used in a locked worksheet? The data validation for the other cells work even when the sheet is protected, but the combos are unaccessible.


Thanks
'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
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
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 + 5
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
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

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

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
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


I have some code that takes a validation list and enters it into a combo box which allows the user to use a form of autocomplete and then outputs the results into the desired cell, it used to work, but for a reason i cannot find the code has stopped working.. when i try copy pasting into a new workbook, it fails again!

what have i messed up?

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
'Dim wsList As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
'Set wsList = Sheets("ValidationLists")


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 = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

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

End Sub





Hi All,
I found the below code on http://www.contextures.com/xlDataVal10.html and it seems to work great EXCEPT when I put a named range where the data is on another sheet. When the named range is on the same sheet it works.
What I am trying to do is expand the length of the driop down list.
Does anyone have a clue how I can make it work with the named range on a seperate sheet? If you do, can you explain to me why so I can learn.
Thank you very much.
Warren

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
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
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 + 5
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
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

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

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
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


I have the following code frm http://www.contextures.com/xlDataVal11.html

The problem with the below code is while entering data in combobox, any data(outside defined list) is allowed where i need to allow only the data from defined list. An error/warning should popup while entering data from out of the dropdown list.

Where do i change the code for this functionality?

Thanks.

Code:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")

Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  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
    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 + 5
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  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("TempCombo")
  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
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
'====================================





PHP Code:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  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
    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 + 5
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  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

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

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
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 13 'Enter
            ActiveCell.Offset(0, 1).Activate
        Case Else
            'do nothing
    End Select
End Sub
'====================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$Z$3:$AA$3" Then
Call GoToMatch
End If
End Sub
==================================== 





Compile error:
Ambiguous name detected: Worksheet_SelectionChange

this error occurs and higlights the line Private Sub Worksheet_SelectionChange(ByVal Target As Range) which belongs to the last Private Sub that is:-
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$Z$3:$AA$3" Then
Call GoToMatch
End If
End Sub

any help will be appreciated...


Basically i'm building a database in access with all the data handling done across a network in excel.(both 2007) Current problem is validation.

The plan is to have code that takes a validated cell i.e. one that has a list associated with it from the excel built in validation tool and populate a temp combo box with that list, this means that every time a cell is selected a validated list can be chosen from, as well as this as the text is typed in the list is autocompleted!..

i had this working for one sheet, but when i try to create a new instance of it in a new worksheet it fails..

can anyone tell me why?

thanks

Jonathan

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
'Dim wsList As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
'Set wsList = Sheets("ValidationLists")


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 = str
      .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


update..

turns out all that needs to be done was renaming a combo box "TempCombo".. not a clue how i missed this.. wasted the better part of a day.. anyway, feel free to use the code if it's helpful fo you, originally from http://www.contextures.com/


I am looking for assisitance in combining the functionality of two seperate combo box code sets. The first combo box code set allows the combo box to automaticially appear when a user Clicks on a cell that contains a data validation list. The second set of code will have a combo box appear when a user Double-clicks on a cell that contains a data validation list. And the code allows for Named Ranges on a seperate worksheet.

I would like to have the functionality of having the combo box to appear when a user Clicks on a cell and not having to Double-click. Also, I would like to have the option to use Named Ranges on a seperate worksheet ("Validation Lists").

I would appreciate any assistance that can be given. Thank you! The code for both sets is listed below.

Data Validation -- Combo box -- Click
VB:

 '=========================================
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 
 '====================================


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Data Validation -- Combo box using Named Ranges

VB:

 '==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ 
    Cancel As Boolean) 
    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Dim wsList As Worksheet 
    Set ws = ActiveSheet 
    Set wsList = Sheets("ValidationLists") 
     
    Cancel = True 
    Set cboTemp = ws.OLEObjects("TempCombo") 
    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
        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 + 5 
            .Height = Target.Height + 5 
            .ListFillRange = str 
            .LinkedCell = Target.Address 
        End With 
        cboTemp.Activate 
    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("TempCombo") 
    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
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 
 '====================================


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




I used the following tutorial in making a combobox: http://www.contextures.com/xlDataVal10.html

I already made the Data Validation, so I just made the combobox step by step, exacly the same (same namesm TempCombo etc.). But it wont work. I have the combobox, when I'm out of design mode you won't be able to see it. Just as it should be. When I double click a cell, the box appears. Till this far everything is going just fine.

But now when I start typing, or press the pull down button. Non of the Data is coming foward. And when I just type something inside of the combobox en press enter, the text just disappears.

In some sort of way it looks like the ComboBox isn't connecting with the cell of data validation.

I hope someone has the solution for me.

Thanks

Edit: I dont know if this matter, but the Data Validation I use, is data coming from another sheet, while the tutorial uses data from the same sheet. Does this make any difference?

Edit again: After some tests, I came to the conclusion that it is infact a sheet problem. I defined a name to 3 cells in the same sheet and changed the data validation source, when I did this, the combobox worked fine. But this isn't what I want, I need it to come from another sheet, hope someone can help me out here.

Code:

Private Sub TempCombo_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
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .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
  
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

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

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub





i have a bunch of code that takes a validation list, turns into an entry for a combo box and then outputs this to the relevant cell, problem is its stopped working any chance you can help me out.

the combo box (active x) still populates but just will not output the data, i have another sheet where i do the same thing but on this one the code opererates correctly!


Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
'Dim wsList As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
'Set wsList = Sheets("ValidationLists")


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 = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

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

End Sub





I'm using Excel 2010 and have created several long data validation lists with named ranges on separate sheets from where the drop down lists are. I have tried to allow autocomplete functionality by following the directions found at http://www.contextures.com/xlDataVal11.html.

Unfortunately my vba experience is about zero! I'm guessing that I need to modify some names/ranges/sheets etc, etc but I have no idea which bits are the critical bits. I named my combo box as ComboBox01. The sheet one particular set of data is on is AKLD_DATA. The range the the data validation references is AUCKLAND.

Can anyone please tell me which bits in the below I need to change?

My exact copy and paste was:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")

Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
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
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 + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
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("TempCombo")
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
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
'====================================



PHP Code:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  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
    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 + 5
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  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

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

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
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
==================================== 




Hi, this code that i found in http://www.contextures.com/xlDataVal10.html this page.... is exactlly what i've been looking for quiet some time.
but the instructions are not for ms excel "2007". can someone give proper step-by-step instructions on how to do this and also alter the code if necessary for it to work.

or a macro to make this macro work would be very much appreciated aswell.

one of the few things that i don't understand here is how to name a "combo box".

i know how to enter code but it just not work with excel 2007
Thank You.


Hello

I have a bunch of useful xl files for an ongoing event i help to manage. It basically enables us to count contributions, deduct expenses, and so on.

However, I would like to link this to a database of members, and the first step, once the membership list is completed, is to be able to list which members are going to be present at any given event.

For this, I would like to be able to begin typing the first few letters of a members name, and the rest autocomplete, or provide a drop down with possibilities.

After a bit of searching I found the Data validation functions, and ActiveX combobox possibility, and this seems to be just what i want.

However, copying the code I have found so that when clicking in a data validation cell the combobox appears, and autocompletes is all going well apart from one thing : the data validation is not really working.

Here is the code I am using:

'==========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Members")

Cancel = True
Set cboTemp = ws.OLEObjects("Bookings")
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
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 + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet(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("Bookings")
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
Private Sub Bookings_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
'====================================


This works perfectly well, except that if I enter a name that doesn't exist on the database, it doesn't object or let me know, which is surely the point of the data validation??

What am I doing wrong? Can anyone point out the mistake in the code?? Many thanks for any help

Dan


Hi is there a way i could stop my sheet from flickering everytime i change my cell selection via keyboard/mouse. i checked and this problem is only with code below that i run in view code of my sheet...

Please kindly take a look and feel free to make any suggestions..
Help will be very much appreciated.

PHP Code:

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
If Target.Address(False, False) = "Z3" Then
    Call GoToMatch
    Exit Sub
End If
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 + 6
      .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 







I'm trying to use a combo box with my data validation. It works great when I'm not using the the INDIRECT function for the validation. But I can't get it to work with it.

I downloaded the code from the internet.

What it does when i don't use the INDIRECT function: I click on a cell that has a Data Validation assigned to it, it activates the combo box to be visible by just clicking on the cell one time, and it auto fills when i start typing. And when I click on the drop down, it list all my options that I can select.

What it does when i use the INDIRECT function: I click on a cell that has a Data Validation assigned to it, it activates the combo box to be visible by just clicking on the cell one time, but it does not auto fill when i start typing. And when I click on the drop down, it does not list any options.

I was wondering if i can add/change something to the code to make it work?

I am also wondering if I can make the combo box expand when i click on the cell?

This is the code:

VB:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Dim str As String 
    Dim cboTemp As OLEObject 
    Dim ws As Worksheet 
    Dim wsList As Worksheet 
    Set ws = ActiveSheet 
    Set wsList = Sheets("Order Form") 
     
    Cancel = True 
    Set cboTemp = ws.OLEObjects("FenceSelection") 
    On Error Resume Next 
    With cboTemp 
        .ListFillRange = "" 
        .LinkedCell = "" 
        .Visible = False 
    End With 
    On Error Goto errHandler 
    If Target.Validation.Type = 3 Then 
        Application.EnableEvents = False 
        str = Target.Validation.Formula1 
        str = Right(str, Len(str) - 1) 
        With cboTemp 
            .Visible = True 
            .Left = Target.Left 
            .Top = Target.Top 
            .Width = Target.Width + 3 
            .Height = Target.Height + 3 
            .ListFillRange = str 
            .LinkedCell = Target.Address 
        End With 
        cboTemp.Activate 
    End If 
     
errHandler: 
    Application.EnableEvents = True 
    Exit Sub 
End Sub 
Private Sub Worksheet(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 
     
    Set cboTemp = ws.OLEObjects("FenceSelection") 
    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 
 
 
Private Sub FenceSelection_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
    Select Case KeyCode 
    Case 9 
        ActiveCell.Offset(0, 1).Activate 
    Case 13 
        ActiveCell.Offset(1, 0).Activate 
    Case Else 
    End Select 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Hi there,

I'm not too good at VBA and I've been trying to create a form that contains a list with an auto-complete option.

I found a pretty good guide under the following link:

http://www.contextures.com/xlDataVal10.html

I've tried it and it kinda works the way I'd like it to except:

1. Excel crashes if you press TAB or Enter after choose your selection.
2. You have to Double-Click on the Validation list tab to bring up the combo box.

If possible, it would be great if Excel didn't crash after pressing Enter or Tab
and if the combo box showed up immediately on selecting the validation list cell (or single click).

The VBA code is:

Code:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  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
    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 + 5
      .Height = Target.Height + 5
      .ListFillRange = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  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

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

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
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
'====================================


Not too sure how to attach the file here but the website I mentioned contains a sample excel.

http://www.contextures.com/xlDataVal10.html

To summarise I'm hoping to create a sheet that:

1. Let's you click on the cell and then type something which causes it to autocomplete.
2. After I've made my selection I can press Enter/Tab/Click somewhere and it confirms it.
3. The rest of the sheet gets populated by results of lookup functions that are dependent on the selections.

Thanks for reading and hope you can help!
FD


Hey ya'll!

Beyond even being new to Excel, if I couldn't play games on it I never turned anything on before this year. Been using these forums a lot to learn how to make up a lot of forms I am using at work. Recently I took some code off of one of the forums here to make my validation lists have a larger font and it has worked great on several of my forms. This week I tried doing the same thing to allow the user to select Military Time in a user box and the date as well. I created a list manually that has tiem from 00:00 to 23:59 in minute increments and the Dates from 1-Jan thru 31-Dec.

I have it formatted the way I want it in the lists that I am using however when the information is selected it comes up in a different format. I believe it is scientific format to be exact. I have tried formatting the cell that I am putting the drop down list in and still have the same issues. Is there a way to format the cell via your code that you use for the combo box?

Here is the code I used..
Private Sub TempCombo_Change()
End Sub
'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
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
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
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
'====================================

Thanks in advance for any help!


Well, couple of days ago someone responded to the question i had on how to increase the font size of a data validation(dropdownbox) and he gave me the code below. This code was to eliminate double clicking on a cell to increase its font.
I created a combo box and add the code in visual basic. However, it does not seem to work. I dont know if i need to do something else or doing something wrong.
Am still working on it, so if there is any body that can help me out i would appriciate it alot.
thank you


Code:

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim str    As String 
    Dim cboTemp As OLEObject 
    Dim ws     As Worksheet 
    Set ws = ActiveSheet 

    If Target.Validation.Type = xlValidateList Then 
        Set ws = ActiveSheet 
        Cancel = True 
        Set cboTemp = ws.OLEObjects("TempCombo") 
        On Error Resume Next 
        With cboTemp 
            .ListFillRange = "" 
            .LinkedCell = "" 
            .Visible = False 
        End With 
        On Error GoTo errHandler 
        Application.EnableEvents = False 
        str = Target.Validation.Formula1 
        str = Right(str, Len(str) - 1) 

        With cboTemp 
            .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 

errHandler: 
    Application.EnableEvents = True 
    Exit Sub 

End Sub


EDIT: added code tags - Moderator


I've developed a workbook with macros in Excel 2007 (Compatibility

Mode), Windows Vista Operating System. The purpose of the Macos

is to allow auto complete on a validated data entry field.

The macros run fine on this platform. I then copied the workbook

to my desktop, Excel 2000, Windows 2000 Professional Operating

System. One of the macros is killing Excel on this platform.

The code is as follows:

Code:

Private Sub TempCombo_KeyDown(ByVal _
                              KeyCode As MSForms.ReturnInteger, _
                              ByVal Shift As Integer)
    Dim cboTemp As OLEObject
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects("TempCombo")

    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
    Case 9
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case 13
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case Else
        'do nothing
    End Select

End Sub


and:

Code:

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 errHandler1

    If Not Intersect(Target, Range("I12:I2024")) Is Nothing Then
        If Target.Cells.Offset(0, -1).Value <> "" Then
            ActiveCell.Offset(1, -7).Select
        Else: GoTo exitHandler
        End If
    End If
    If Intersect(Target, Range("F12:F2024")) Is Nothing 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 errHandler1
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = "Categories!" + Sheets

("Categories").Range(str).Address
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
    End If
exitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.Protect
    Exit Sub
errHandler1:
    Resume exitHandler

End Sub


I found this code at http://<br /> <br /> http://www.co...DataVal14.html from a lead on

this site.

These macros run fine on platform Excel 2007 (compatibility mode),

Vista but bomb out on Excel 2000, Windows 2000. The line of code

Code:

cboTemp.Visible = False


in the TempCombo_KeyDown macro appears to be the culprit.

If I comment out this line(s), the macro runs but the ComboBox

remains visible. Not an overwhelming problem but not as neat.


I get the following error message under Excel 2000:

"Excel.exe has generated errors and will be closed by Windoes.

You will need to restart the program. An error log is being

created."

The error log reports:

Application exception occirred:
App: (pid=2180)
When: 6/1/2009 @ etc.
Exception number: c0000005 (access violation)

I've been wrestling with this for three days with various coding

but cannot get Excel 2000 to buy it.

I'v also been wrestling with the Forum for several days; I'll try

to upload the file later if I can ever get this tread posted.

Any ideas on how I can modify the code to make it compatible would

be greatly appreciated.

Thanks for you help.

John


hello again,

I'm still trying to tune up my combo box, and as I test it, I find
things that need some work to make it smooth. I would like to have it
setup so that the mouse is not required for data entry. It can be used
as an option, just not a required tool.

How do I add a shortcut key to the following code: (This code is in
sheet1)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim ws As Worksheet
Set ws = ActiveSheet

Dim cboTemp As OLEObject
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Column = 2 And ActiveSheet.Name = "Timesheet Entry"
Then
Application.EnableEvents = False
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 275
.Height = Target.Height + 5
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

I've added this line:

Keyboard Shortcut: Ctrl Shift + Z

Without success. I've written a sub to call the doubleclick sub and
attached a shortcut there, to no avail.

If anyone can help, I'd really appreciate it.

Thanks,

Dave




Hi there,

Here's the issue I'm hoping someone can help with (I've pieced the solution together from some code I found he http://www.contextures.com/xlDataVal14.html
That link explains how to insert a combo box in fields that are using data validation from a list (to take advantage of combobox feature over the default drop down in excel). I changed that code to insert MS Date Picker instead of a combobox if the data validation is set to Date

Here is my code:
Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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("DPick")
On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .LinkedCell = ""
    .Visible = False
    
  End With
End If

  On Error GoTo errHandler
  If Target.Validation.Type = 4 Then
    'if the cell contains a data validation date
    Application.EnableEvents = False
    
    Target.NumberFormat = "mm/dd/yyyy" 'format the cell to a date
    
    DPick.Value = Date 'default date picker to today if field is blank
    
    With cboTemp
      'show the Date Picker
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    
  End If

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

End Sub


I have 2 problems:

#1 If the field is blank, the date picker default to today (Great!) but if try to accept that value and move to a new cell the last cell remains blank
#2 once you choose a date value with the date picker, the cell shows a Data Validation error - it seems the value is a string (left justified) - selecting the cell, F2 then Enter corrects the issue.

Can anyone help with these issues please?