?
Solved

Excel: read address/location of a button

Posted on 2003-03-30
17
Medium Priority
?
428 Views
Last Modified: 2012-06-22
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...



0
Comment
Question by:miauw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +2
17 Comments
 
LVL 5

Expert Comment

by:JohnMcCann
ID: 8233616
I think you may ewant to read up on Views in Excel.  I beleive you can open a different view for each user.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8233675
Here is a setup that I've used for different users before.

(1) Create a master sheet where you match different dropdown lists to different users. This sheet determines the dropdown list access.

(2) Run a VBA Workbook_Open evenet to ask the user for a password, the event would then setup a sheet with that particular customised dropdown list as a choice.

(3) I'd hide all the sheets so that if the user did not select enable macros there would be no choices avaiable. The Workbook_Open event would configure and unhide the sheet

Cheers

Dave

0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 2000 total points
ID: 8233711
Ifm you can get hold of the username already (either through a menu or whatever) you can use data Validation with a different range in a number of ways, e.g. at a simple level:

A1 = The user's Name

In a cell set it as Data Validation List box with a formula of =indirect(a1)

then create a named range (highlight the range and type a name into the area above the A1 cell and hit enter or use Insert | Name | define) for each user's name.

You can take this further using other formulas for the list range, e.g.:

=indirect(if(a1="Fred","AllData","BasicList"))

will show the list defined as BasicList for all but the user Fred.

It depends upon the number of usernames and how many different data lookup lists you have but you could also use combinations of VLOOKUP, MATCH, HLOOKUP, or OFFSET functions to get hold of the list.  OFFSET works v. well for this, e.g.:

=OFFSET(AllList,0,0,12,1)

returns the first 12 items of a list arranged down a column in a named range called AllList.  You could set teh 12 depending upon user, e.g.:

=OFFSET(AllList,0,0,if(a1="Fred",12,5),1)

to show 12 for Fred, 5 for everyone else.

I'm only popping in to EE briefly so might be around to see any queries (sorry) but hopefully it will give you some ideas.

Steve
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 81

Expert Comment

by:byundt
ID: 8234472
Assume you have a cross-reference table named tblXref which contains the user names in the first column and named ranges for each of the lists of choices in the second column. For example:

Bill     lstBill
Brad   lstBrad
Susie  lstSusie

The second column in essence contains pointers to the lists of choices.

lstBill     contains the elements 1, 2, 3, 4, 5
lstBrad contains the elements x, y, z
lstSusie contains the elements a, b, c, d, e, f, g, h, i,

Assume further that cell C4 contains the function call     =myUser()
This is a reference to a very simple VBA function pasted onto any module sheet in your workbook.

Function myUser(cell)
myUser = Application.UserName
End Function
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8235583
That's a nice method Steve for avoiding VBA.

I think the 500 pointers will keep tempting you, baby or not. :)



0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8236353
I think it might be here quicker than we think... hence why I am up at 5am :-)
0
 

Author Comment

by:miauw
ID: 8239926
Tnx everyone.

Fast and good feedback I think.

I really like the method Dragon-it provided me with, since
it is both simple and very functional (and perfect except for the fact that it assumes the info about the networkuser is already contained in the sheet).

However, to get what I want, I need to discrimate between either the network loginname (using Netware with a local votatile NT or W9x account) of the persons using this sheet.
Then the Dragon-it solution is great.

This
 Function myUser(cell)
 myUser = Application.UserName
 End Function

with a call in A1 ( = myUser()) from Byundt does not seem to do the trick.

Plz expand if you have any further info about getting the right username in a cell. When I have this, the problem is solved.

Almost there.

   Miauw




0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8246892
You can get the username from the registry I think,

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer

in the "Logon User Name" key, baby born last night so not stopping, maybe someone else can get you to the Novell or MS username using a UDF?

Steve
0
 

Author Comment

by:miauw
ID: 8247234
Mmmh. Still have trouble reading the correct networkuser, which by the way best can be the Netware login name, since the knowledgeable users can choose to either logon to the domain or to avoid it and login using a local Windows login name, which than can create (don't ask me why). Offcourse if one can "change" the Windows login name by creating an temporary account for this purpose only, the protection is not very good. So the "Logon User Name" key is not good enough. Nor is Application.UserName works BTW now.
Maybe I was to vague indeed earlier.

So the options, following the dragon-it solution, I feel are:

A) Using the Netware login name (which then can not [easily] modify unlike the windows login name)

B) Or work with a password in some way asked when accessing the workbook or maybe even the cell.

I realise that both A and B are not perfect, securitywise,
however to meet my functional specs following the Dragon-it solution, I do need either one of them. Or is there another option. Functionally my question remains the same.

Plz could you expand on this.

Cheers,
 Miauw

0
 

Author Comment

by:miauw
ID: 8247292
BTW: On Experts-exchange I found (excuse me for not quoting the author, I couldn't refind this answer so I lost the programmer's name)".:

Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function ReturnUserName() As String
' returns the NT Domain User Name
Dim rString As String * 255, sLen As Long, tString As String
   tString = ""
   On Error Resume Next
   sLen = GetUserName(rString, 255)
   sLen = InStr(1, rString, Chr(0))
   If sLen > 0 Then
       tString = Left(rString, sLen - 1)
   Else
       tString = rString
   End If
   On Error GoTo 0
   ReturnUserName = UCase(Trim(tString))
End Function

which gives the NT login name.
And
Function Test() As String
    Test = " " & Application.UserName
End Function

returns the application user.
But in my case both can be manipulated. So: not good enough.

0
 
LVL 81

Expert Comment

by:byundt
ID: 8247310
The GetSettings function in VBA can retrieve keys from the registry, but I have not figured out how to get the user name from it.
0
 

Author Comment

by:miauw
ID: 8247465
To Byundt:
I never tried to store or retrieve things in the registry using Excel-VBA, but I tried it just now and it seems

GetSetting(appname := "MyApp", section := "Startup", _
                       key := "Left", default := "25")
Will amount to HKEY_USERS\S-1....(rest of the SID)\VB AND VBA PROGRAMS SETTINGS\"MyaApp"\"Startup".

I can write to and read from this key, but how the get rid of the part before this (the Hive HKEY_USERS.....SETTINGS\) I do not know and I am sure the key I want to read is stored somewhere else. Isn't it?

Miauw

0
 

Author Comment

by:miauw
ID: 8247498
And from Technet:

There is a limitation to using the built-in registry access functions: You can access only the registry keys under HKEY_CURRENT_USER\Software\VB and VBA Program Settings. To do so, you must be logged onto the system because the HKEY_CURRENT_USER registry key is not active until you log on to the system.

Registry settings that are to be accessed from a noninteractive process (such as mtx.exe) should be stored under either the HKEY_LOCAL_MACHINE\Software\ or the HKEY_USER\DEFAULT\Software registry keys.

Accessing the Registry with the .NET Framework
Because you can use the GetSetting and SaveSetting functions to access keys only under HKEY_CURRENT_USER\Software\VB and VBA Program Settings, you may find them limiting. When that happens, you can use the Registry and RegistryKey classes in the Microsoft.Win32 namespace of the .NET Framework. For more information, see Registry and RegistryKey


___

The computers are not all provided with the .NET Framework.
So maybe this direction for the solution will not lead to anything?
So how about the password or another way.
I can't increase the points. I would if I could. Really do not have the time write know to go into VBA 4 Excel, a language I don't speak.

Cheers,
  Miauw




0
 

Author Comment

by:miauw
ID: 8255588
Well anyway, getting a end2end solution fast is probably asking too much.
Fairly speaking, I am not helped really yet, but on the otherhand the question is probably requiring a multipart solution (so several questions), and since dragon-it suggested a very nice and simple idear, I will grand the points to him.
(There are way to many questions left open here on EE anyway)

Cheers,
 Miauw
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8255693
Thanks v.much for all those points and grade.  Apologies for not being able to spend more time helping, my previous comments tell it all!  I'm sure the other experts around her can sort out the other bits, if I think of a way in a quite moment I'll post here for you.

Steve
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8260620
How about this (sorry for punctuation, etc, typing one handed, other one full of baby!)

Type SET at a command prompt.  username will be set to the NT username on Nt/w2k but not win9x.  On netware with a nw client you get login_name set to first 8 chars of username.

List of available variables in the login script can be seen here... might need to use SET command in login script to set this if you can't see it from typing set as above:

http://www.dragon-it.co.uk/links/login_script_variables.htm

Reason for doin this is you can read environment variables using VBA easily, e.g. insert this into a new module then yiu can read it using =username() from vb or a cell formula.

Function NWname() As String
  NWname = Environ$("login_name")
End Function


hth

Steve
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 8260683
BTW been a while since I wrote NW login scripts (most people going NW to W2K...) but I think LO/GIN is the variable not LOGIN_NAME.

Have a go anyway!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
New style of hardware planning for Microsoft Exchange server.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question