Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a validation list macro
Hi
I cannot find this in the groups anywhere. I would like to update a validation list, which is in cells "FR2" to "FR26" which contains a list of names. I would like a user to be able to click on a button which pops up with a message saying "Enter New Buyer Name", they enter the name, click ok and then the validation list has been updated in alphbetical order with that persons name. Could someone help me with the coding for this pls. Thanks in advance. roger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a validation list macro
Dim ans
ans = InputBox("New value") With ActiveCell ans = .Validation.Formula1 & "," & ans .Validation.Delete .Validation.Add Type:=xlValidateList, Formula1:=ans End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rammieib" wrote in message oups.com... Hi I cannot find this in the groups anywhere. I would like to update a validation list, which is in cells "FR2" to "FR26" which contains a list of names. I would like a user to be able to click on a button which pops up with a message saying "Enter New Buyer Name", they enter the name, click ok and then the validation list has been updated in alphbetical order with that persons name. Could someone help me with the coding for this pls. Thanks in advance. roger |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a validation list macro
Bob
Thanks for your reply. I have been unable to make it work. After entering the name into the inputbox, I cannot get that name to appear in the cell when I push the validation list down one. I have the following: Sub EnterNewBuyer() Dim ans ans = InputBox("New value") Range("FR2").Select Selection.Insert Shift:=xlDown With ActiveCell ans = .Validation.Formula1 & "," & ans .Validation.Delete .Validation.Add Type:=xlValidateList, Formula1:=ans Range("FR2:FR34").Select Selection.Sort Key1:=Range("FR2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B5").Select End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a validation list macro
Is it a text value you are entering, or a number? Also, is your original
list in the DV, or referencing a range somewhere? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rammieib" wrote in message oups.com... Bob Thanks for your reply. I have been unable to make it work. After entering the name into the inputbox, I cannot get that name to appear in the cell when I push the validation list down one. I have the following: Sub EnterNewBuyer() Dim ans ans = InputBox("New value") Range("FR2").Select Selection.Insert Shift:=xlDown With ActiveCell ans = .Validation.Formula1 & "," & ans .Validation.Delete .Validation.Add Type:=xlValidateList, Formula1:=ans Range("FR2:FR34").Select Selection.Sort Key1:=Range("FR2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B5").Select End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a validation list macro
Bob
Entering a text value, it would be the name of someone new joining the company for instance. I want that name putting into the range F2:F34 which is the list of current names. This is my DV list. Range("f2") = InputBox("Enter name") That line does not put whatever name entered into cell F2 when you click ok. Why? This was the code I next tried after yours. Thanks Bob Phillips wrote: Is it a text value you are entering, or a number? Also, is your original list in the DV, or referencing a range somewhere? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rammieib" wrote in message oups.com... Bob Thanks for your reply. I have been unable to make it work. After entering the name into the inputbox, I cannot get that name to appear in the cell when I push the validation list down one. I have the following: Sub EnterNewBuyer() Dim ans ans = InputBox("New value") Range("FR2").Select Selection.Insert Shift:=xlDown With ActiveCell ans = .Validation.Formula1 & "," & ans .Validation.Delete .Validation.Add Type:=xlValidateList, Formula1:=ans Range("FR2:FR34").Select Selection.Sort Key1:=Range("FR2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B5").Select End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a validation list macro
Try this
Cells(Cells(Rows.Count, "F").End(xlUp).Row + 1, "F").Value = InputBox("Enter Name") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rammieib" wrote in message ups.com... Bob Entering a text value, it would be the name of someone new joining the company for instance. I want that name putting into the range F2:F34 which is the list of current names. This is my DV list. Range("f2") = InputBox("Enter name") That line does not put whatever name entered into cell F2 when you click ok. Why? This was the code I next tried after yours. Thanks Bob Phillips wrote: Is it a text value you are entering, or a number? Also, is your original list in the DV, or referencing a range somewhere? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rammieib" wrote in message oups.com... Bob Thanks for your reply. I have been unable to make it work. After entering the name into the inputbox, I cannot get that name to appear in the cell when I push the validation list down one. I have the following: Sub EnterNewBuyer() Dim ans ans = InputBox("New value") Range("FR2").Select Selection.Insert Shift:=xlDown With ActiveCell ans = .Validation.Formula1 & "," & ans .Validation.Delete .Validation.Add Type:=xlValidateList, Formula1:=ans Range("FR2:FR34").Select Selection.Sort Key1:=Range("FR2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B5").Select End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a validation list macro
You could also do this with a dynamic list instead of re-setting your validation routine(s) every time you add a name to the list. Like so, Say you have R1-R26 as your range, define the following as a named range myRange =OFFSET(R1, 0, 0, COUNTA(R1:R$65536), 1) then set your Cell's validation = List and set to myRange. What myRange is now - a list of everything non-blank in Column R. If you add values to the end of the list, anything that references this range will automatically show up in the validation list. I have a number of dynamic ranges in several applications that do this. (Easiest to have a hidden worksheet that holds these values.) Hope this helps, Chad "rammieib" wrote: Hi I cannot find this in the groups anywhere. I would like to update a validation list, which is in cells "FR2" to "FR26" which contains a list of names. I would like a user to be able to click on a button which pops up with a message saying "Enter New Buyer Name", they enter the name, click ok and then the validation list has been updated in alphbetical order with that persons name. Could someone help me with the coding for this pls. Thanks in advance. roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation List Macro | Excel Discussion (Misc queries) | |||
Create auto updating data validation list from all worksheet names | Excel Worksheet Functions | |||
Updating a Validation List | Excel Programming | |||
Creating a Validation List Through Macro | Excel Programming | |||
Macro for updating memberssubscription list annually | Excel Programming |