Excel 2007 : Combobox works with On sheet data validation list but not Off Sheet list

**adventureli** · 11-11-2010, 08:00 AM

Attached is an example that I pulled from Contextures website (Grovier). There a re 3 sheets. The first sheet if the original file and all works as it should, In which the combobox attaches itself to the active cell containing data validation using one click. The lists for the data validation are contained on the same sheet.

Sheet Two: is the same thing modified such that the data validation lists (moved to sheet List) are using the named Ranges of the lists offsheet. Most of the functionality of the combobox no longer works. Namely the font change , number of items the list dispplays on click, autocomplete dont work any more.

any thoughts?

**JBeaucaire** · 11-11-2010, 08:27 AM

When I removed your BeforeDoubleClick macro and put the code that was in it into your SelectionChange macro, it seemed to work...also had to correct the "Lists" reference.

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("Lists")

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

**adventureli** · 11-11-2010, 09:25 AM

JB,

Thanks for pointing out the need to declare to the wcLists variable, I missed that.

The Macro you removed is an important component that would facilitate natural keyboard data entry without having to use the mouse to "Click" on the next cell. In that, it functions in the Original Sheet to move the combo-box upon the use of a Tab or Enter to the next cell.

Can you see what is preventing that Macro from performing as it should once the data validation lists have been moved to another sheet. (At least that is what appears to have been the culprit in breaking the Macro)

**JBeaucaire** · 11-11-2010, 11:24 AM

Then I guess you've lost me. I took out that macro and put in the one shown above and now sheet2 is behaving the same way. If I click by mouse the ComboBox appears and if I finish by pressing ALT or ENTER, the box appears in the next cell.

**adventureli** · 11-11-2010, 12:37 PM

Hmmmm. Would you kindly send me your file. I've just retried it and neither Alt, Tab or Enter moves me into the next cell. Not sure what the difference is.

Thanks in advance for your help.

**JBeaucaire** · 11-11-2010, 01:35 PM

Here you go...

**adventureli** · 11-11-2010, 08:30 PM

Thanks again JB. I see that with the macro at the bottom it works for that sheet. Excitedly, I adapted it to my own application and it fails. No idea why. It's all seems pretty straightforward.

**JBeaucaire** · 11-11-2010, 09:51 PM

Yeah, this stuff can be frustrating, though I like the technique you've implemented. Very interesting stuff.

Excel 2007 : Combobox works with On sheet data validation list but not Off Sheet list

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Combobox works with On sheet data validation list but not Off Sheet list

Re: Combobox works with On sheet data validation list but not Off Sheet list

Re: Combobox works with On sheet data validation list but not Off Sheet list

Re: Combobox works with On sheet data validation list but not Off Sheet list

Re: Combobox works with On sheet data validation list but not Off Sheet list

Re: Combobox works with On sheet data validation list but not Off Sheet list

Re: Combobox works with On sheet data validation list but not Off Sheet list

Re: Combobox works with On sheet data validation list but not Off Sheet list

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions