Excel: read address/location of a button
Posted on 2003-03-30
THE FUNCTIONAL THING I am after is that
I want to make an protected Excel sheet of 10 colums by 2000 rows such
that each cell is associated with a pulldown list ( Menu DATA - Item Validation - TAB Settings - VALIDATION CRITERIA ALLOW list), but the range of choices must be dependent on the networkloginname or password.
So the person with the main password must be able to select any of the 20 items presented in the pulldownlist,
while others get a much shorter list of choices (e.g .only 5).
So this is the real question: how to present different lists to different persons.
Since I did not figure out how to do this without involding VBA, I tried to do this by:
A protecting the cell such that only authorised persons can change the value by the pulldown list
B adding a button in the cell, which produces a (show) form in which certain, limited, choices can be made
such that my making a selection in this form values in the cells are modified
(C) Initially I try to solve the issue by adding an extra column with the limited choices for the less authorised
persons and used a formula to copy or not copy the selected values from the list into the original cell;
however this the choices required rather long text (20 char or more), either my spreadsheet because visually to wide or
the choices from the list became unreadable short).
So I went for B. So the issue is, how to relate the button which is pressed to the cellposition it must modify.
Also, since there will be a lot of buttons on the sheet, it must be a generic procedure.
Hope this doesn't sounds to vague...