Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super piece of code..At least for me!!
I came accross this code and of course I have a question hope to
resolve it with your help. Here it is Public Sub CreateList() With ActiveCell .Validation.Delete .Validation.Add xlValidateList, , , "=" & "ListRange" End With End Sub Put it in standard module 1. Sheet1 create a botton to activate this macro 2. sheet2 create a range of one column Say A1:A12 with data say Jan,Feb etc Name it ListRange. 3. Now goback to Sheet1 and at ANY ACTIVE CELL click Botton1 You will notice the creation of a List (with black Triangle) Click on it you will get a list of Jan,.... Select one and you have it in the active cell. I realy do not know how it works, my question is can I place the selected text from the list somewhere else in sheet1 ? say left cell??? I hope you see the beuty of this code as I did. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super piece of code..At least for me!!
the code just uses data validation as you could do manually by selecting a
cell and doing Data=Validation in the menu and then selecting the LIST option. In you case you would put in =ListRange Data validation only works on the cell where it is applied - so you could not select from the dropdown in A1 and have the selected value appear in B9 unless B9 had the formula =A1 in which case, it would appear in both cells. -- Regards, Tom Ogilvy "Arishy" wrote in message oups.com... I came accross this code and of course I have a question hope to resolve it with your help. Here it is Public Sub CreateList() With ActiveCell .Validation.Delete .Validation.Add xlValidateList, , , "=" & "ListRange" End With End Sub Put it in standard module 1. Sheet1 create a botton to activate this macro 2. sheet2 create a range of one column Say A1:A12 with data say Jan,Feb etc Name it ListRange. 3. Now goback to Sheet1 and at ANY ACTIVE CELL click Botton1 You will notice the creation of a List (with black Triangle) Click on it you will get a list of Jan,.... Select one and you have it in the active cell. I realy do not know how it works, my question is can I place the selected text from the list somewhere else in sheet1 ? say left cell??? I hope you see the beuty of this code as I did. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super piece of code..At least for me!!
Thank you for explanation. I have two posts open regarding the solution for multiple choice selection at a certain cell. So rather than repeating here my problem which is wrong anyway I have a followup question. Suppose I do a "refill" of the list for each cell I am interested in. Can I use this routine? Example will help A1 has "85" B1 is the active cell. Since I can look at A1 which is activecell.offset(0,1) I can direct VBA to "refill" ListRange" with a different set of data. Once I select one choice I want VBA to replace A1 with the result of selection. You mentioned if A1 has +B1 then I am ok BUT A1 had originally "85" I really do not care once I used the value in A1 I do not need it anymore. In other word I can insert formula in A1 once I use the value "85" If that is no no in VBA or even in any language Please put me right...again *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Super piece of code..At least for me!!
I put a value in a cell
This updates the choices for data validation in another cell I select one of those values the selected value appears in the original cell. No, that isn't directly supported by data validation. You might be able to cobble something together using events. -- Regards, tom Ogilvy "samir arishy" wrote in message ... Thank you for explanation. I have two posts open regarding the solution for multiple choice selection at a certain cell. So rather than repeating here my problem which is wrong anyway I have a followup question. Suppose I do a "refill" of the list for each cell I am interested in. Can I use this routine? Example will help A1 has "85" B1 is the active cell. Since I can look at A1 which is activecell.offset(0,1) I can direct VBA to "refill" ListRange" with a different set of data. Once I select one choice I want VBA to replace A1 with the result of selection. You mentioned if A1 has +B1 then I am ok BUT A1 had originally "85" I really do not care once I used the value in A1 I do not need it anymore. In other word I can insert formula in A1 once I use the value "85" If that is no no in VBA or even in any language Please put me right...again *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interpretation of a piece of code | Excel Discussion (Misc queries) | |||
What is wrong with this vba piece of code? | Excel Discussion (Misc queries) | |||
Run piece of code for each line of data | Excel Programming | |||
code running super slow... | Excel Programming | |||
changing a piece of code | Excel Programming |