PDA

View Full Version : Solved: Data Validation help



austenr
01-13-2010, 03:31 PM
Sorry haven't programmed much lately. Got this workbook from a friend and am trying to help him out.

Look at Form3 tab. Two questions:

How do you remove the drop down's from line 16?

Also on line 16 column I, How do you move the drop down list from line 16 down to line 17 and copy down?

Boy, been away too long.

lucas
01-13-2010, 04:38 PM
Go to data-filter-autofilter. When you clidk it your checkboxes should go away.

I don't understand question 2

austenr
01-13-2010, 04:46 PM
Never mind the second question. However, if I name a range on another sheet like sheet 2, how do you use that named range in a data validation cell on sheet1? Also, I need to be able to make the named range dynamic. Hope that clears it up. Thanks Steve

lucas
01-13-2010, 04:52 PM
see this thread, post #5 for an example.

Check out the define on the named range and the data validation on cell C3

http://www.vbaexpress.com/forum/showthread.php?t=30067

using the named range, your list does not have to be on the same sheet as the data validation cell or cells.

austenr
01-13-2010, 04:54 PM
Thanks, I saw that earlier. How can you make it a dynamic sorted named range?

lucas
01-13-2010, 05:11 PM
Sheet code for the sheet with the list or named range:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long

n = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
Me.Range("A1:A" & n).Name = "MyList"
Application.CutCopyMode = False
Range("MyList").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select

End Sub

try adding a 4 or 8 to sheet two of the attachment.

austenr
01-13-2010, 05:24 PM
Thank you