Validation List

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybody , I need to define an validation list to somes cells in my
worksheet using source code, is it possible ?
 
Do it in Excel with the macro recorder on. That will give you the code.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Angeles,

Try turning on the Macro Recorder and then adding some validation to some
cells. The recorded code will give you a very good start.

hth,

Doug
 
Angeles,

In Excel's Tools menu choose "Macros" then choose "Record New Macro".
Choose to save it to ThisWorkbook. The Record Macro toolbar will appear on
the screen. Dthe steps to enter your validation and then press the Stop
button on the Macro Recorder toolbar. Then go into the Visual Basic Editor.
Your recorded macro will be in "Module1" or similar in the workbook you did
the validation in.

hth,

Doug
 
I got it , thank you.

One more question , is it posible to let the user adds more values to this
list , for example , if the list values are : Class 1, Class 2, Class 3. Is
it posible that the user writes one new value and add that value to my
Validation List (if the value is new)?
 
Angeles,

It is possible. You have to capture the old list, delete the validation and
then create new validation it with the new list. Here's an example to get
you started. Obviously, it doesn't address where the new user validation
item comes from:

Sub test()
Dim strFormula1 As String

With Worksheets(1).Range("A1").Validation
strFormula1 = .Formula1 & ",Class 224"
.Delete
.Add Type:=xlValidateList, Formula1:=strFormula1
End With
End Sub

hth,

Doug
 
My formula is ... =OFFSET(INDIRECT(SUBSTITUTE($L6,"
","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($L6," ","")&"Col")),1)

If I write ...

With Worksheets(1).Range("A1").Validation
strFormula1 = .Formula1 & =OFFSET(INDIRECT(SUBSTITUTE($L6,"
","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($L6," ","")&"Col")),1)

.Delete
.Add Type:=xlValidateList, Formula1:=strFormula1
End With

I am getting an error message , which is the correct way for writing the
formmula ?

Thank you
Angeles
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

get Data Validation to stop, list supplied, duplicate data entries 1
How to Update Selections for (Numerous) Validation Lists 1
Data Validation List (conditional) 2
Userform Combo box to mimick Worksheet Cell validation list range. 2
Validation List 7
VBA to sort list automatically in alphabetical order 6
Runtime-error 1004 (excel 2007) when selecting validation list optiontriggering worksheet_change pro 1
Conditional Validation In Excel 1

Back
Top