PDA

View Full Version : Data Validation - Combo box using Named Ranges ? INDIRECT



kinkiBCN
04-09-2007, 07:57 AM
I am adapting these macros :

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
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationSample")
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 = 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 = False
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.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End Sub

My goal is to have a VALIDATION List but modifying the font and using autocomplete.

This macro works perfectly well when I am using Named Ranges as VALIDATION List, but when I want to use an INDIRECT function as VALIDATION List the macro does not works at all.

Is there anyone able to help me to about how can I modify the macro to use INDIRECT Function as VALIDATION List ?

Thanks

lucas
04-09-2007, 08:09 AM
Are you using INDIRECT because you want to have a dynamic range?

kinkiBCN
04-09-2007, 02:37 PM
Hi Lucas, I am using INDIRECT because depending on certains variable values I use differents Names Range.

lucas
04-09-2007, 06:19 PM
I found this in the help files for troubleshooting validation. Maybe it's not working because there is an error in your formula:


Are formulas error free? Make sure formulas in validated cells don't result in errors, such as #REF! or #DIV/0!. Microsoft Excel ignores the data validation until you correct the error.
Are cells referenced in formulas correct? If a referenced cell changes so that a formula in a validated cell calculates an invalid result, the validation message for the cell won't appear.

kinkiBCN
04-10-2007, 03:48 PM
Thanks Lucas, in fact, with this macro, the Combo Box overlap the validation cell in the way that and you can use the Combo Box with a double click or the validation with one click.

The validation works perfectly well, it is the Como Box that does not take the validation list values.

lucas
04-10-2007, 04:24 PM
Hi kinkiBCN,
I guess I'm just missing the point. I don't understand why you would have a combobox and data validation in a cell with overlaping data...

I don't follow some of what your doing like this in the before double click event is dimmed and set but never used...

Dim wsList As Worksheet
Set wsList = Sheets("ValidationSample")

could you possibly post what your working on with a littl explaination?