Combo box

  • Thread starter Thread starter angy345
  • Start date Start date
Yep.

I'm not quite sure what you're doing, but select your range (say c5:c504). Then
with that range selected and C5 the active cell, write the formula using C5 as
the base or point at the validation list (different sheet??)

(excel will adjust the formula through the cells in the selection, just like
when you copy down a column.)

You can find a lot of info about data|validation at Debra Dalgleish's site:

http://www.contextures.com/xlDataVal01.html
 
so i have an excel sheet that gets it's records from an external
database and in the first column of the excel sheet all the cells needs
to have a combo box so that the user can select or add a new entry.. I
succeeded to do it by using a macro...see codes below...then from VBE
run the program and it will make all the cells have a combo box...not
sure if it makes any sense to you ! thanks for you help :)


Range("$A:$A").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$A:$A"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
 
First, what do you want to show up in the dropdown list? Do you have that list
of available values elsewhere--a different worksheet (within the same workbook)?

If yes, then before you make it a macro, see if that works. (I'm not sure if
you'd want to use all the values that are imported from your external database.)

And I don't think I'd apply data|Validation to all of Column A, too.

I put my list of valid entries in column A of Sheet2. I gave that list a nice
name (myList). Then I could use that in my Data|Validation:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myList As Range

With Worksheets("sheet2")
Set myList = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
myList.Name = "myList"
End With

With Worksheets("sheet1")
With .Range("a1", .Cells(.Rows.Count, "A").End(xlUp).Offset(100, 0))
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, _
Operator:=xlBetween, Formula1:="=myList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Not on list (Warning only)"
.ShowInput = True
.ShowError = True
End With
End With
End With
End Sub

Since you're adding values, I figured a warning might be in order if the value
wasn't part of the list.

And I took all the data in column A (of sheet1) and then went down 100 more
rows--just to give the user some place to add more info.
 
Back
Top