how can I create access level list

[uyeveuye]

I wanna write a macro on excel, which gets user name and then allows user to access some part of the document. How can I make a access level list.
Also when macro runs, I want it to run at the background, so user will not be allowed to see what macro is doing. I want user to see the final report. Is it possible?

 

[SkipVought]

Hi,

The KEY is going to be that the user opens Excel with macros enabled. Otherwist, your access control will have not effect.

So you make a workbook where all the sheets excelpt ONE are VERY hidden, which means that only VBA code or manipulation in the VB Editor will make any sheet visible. If the user does not enable macros, they can never see any very hidden sheets. If they do enable macros, the WOrkbook_Open event, fires the access macro to ask the user for the proper responses and then unhides the sheet(s) the user can view. Finally when the user exits, the Workbook_Close event fires a macro to place the workbook in the original "protected" state.

Is that what you want to do?

Skip,

_[red][/red]

 

[uyeveuye]

I meant that as example:
Mr/Mrs X manager. code 100
Mr/Mrs Y supervisor. code 110
Mr/Mrs Z employee. code 111
Z can see the report for only 111
Y can see 110 as well as 111
X can see all of them.
Is it possible to create access level as above? How can define the levels?

Also, when macro is working, i want user to see only one blank page and all of the work unseen by user. Is it also possible?
Thanks

 

[SkipVought]

Yes and yes.

But you does, Mr/Mrs X, validate him/herself in your scenario.

Skip,

_[red][/red]

 

[SkipVought]

But HOW does, Mr/Mrs X, validate him/herself in your scenario?

Skip,

_[red][/red]

 

[uyeveuye]

macro will validate them.
When they open the document they will click a button to start.
At that time macro will get their id.
I have completed the above bit.
Problem is below.Specially as I have various position matrixs.

When macro gets the ID, I want project to check the level for this user and either give permission for only one report, or if the person is manager have option of choosing various reports.

 

[SkipVought]

[tt]
Access
100
110
111

Groups Access
All 100
All 110
All 111
Gp1 110
Gp1 111

ID Access
X ALL
Y Gp1
Z 111
[/tt]
You can either list an ID with each access code or use a Group code if you have many users with similar groupings.

Skip,

_[red][/red]

 

[uyeveuye]

Thank you.
Final question?
What is the command on visual basic to define levels?

 

[SkipVought]

What do you mean by "levels"?

Skip,

_[red][/red]

 

[uyeveuye]

levels as you mentioned above.
Is it basically something like
definelevels(all,1) etc???

 

[uyeveuye]

levels as you mentioned above

 

[SkipVought]

The simplest way for you would be to set this up on a sheet with a simple lookup table. I use Named Ranges to document the process...
[tt]
ID Access
X 100
X 110
X 111
Y 110
Y 111
Z 111
[/tt]
I have a cell named SelectedID and one named SelectedIndex

The here are two formulas...
[tt]
TheNumberOfAccesses
=COUNTA(OFFSET($A$1,MATCH(SelectedID,ID,0),1,COUNTIF(ID,SelectedID),1))

TheAccessForGivenIndex
=INDEX(OFFSET($A$1,MATCH(SelectedID,ID,0),1,COUNTIF(ID,SelectedID),1),SelectedIndex,1)
[/tt]
If SelectedID has X, then TheNumberOfAccesses is 3. Then if you enter SelectedIndex 1, you get 100 in TheAccessForGivenIndex.

You can use the cell containin the number of accesses as the limit in a for...next loop and then assign the Access Index...

for i = 1 to [TheNumberOfAccesses]
  [SelectedIndex] = i
  Sheets([TheAccessForGivenIndex]).visible = xlsheetvisible
next

Skip,

_[red][/red]

 

[uyeveuye]

Thank you so much.