Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Still need help hiding rows using a userform

0

I must confess that I didnt post the full working version of my sheet last time but so I wanted to make it clear what I needed.  Thank you Variutus for your knowledge and expertise.  You asked me what I wanted out of the userform so here it its.  For most users, once they log into the workbook it will take them straight to Sheet3(Picks) which will gvie them access to make their weekly picks.  I do not want them to be able to see other users picks for the week so I need to give them access to only one row that either their name resides or username.  Doesnt matter.  As you can see on Sheet1Admin) I have created a table that you can adjust sheet access with.  If you double click in a cell next to the user it will give you 3 options under each sheet.  

1, Green unlocked symbol  which gives you full access to the sheet

2.,  Yellow locked symbol which give you read only access to the sheet

3.  Red prohibition (if you will) which denies all access to the sheet completely 

What I dont know how to do is set row access so they only see their row once they log into the workboot using the userform.  I hope this makes sense and I'm sorry my previous workbook wasnt functioning.  I didnt restructure it completely using your previous advice but I will once I figure out my current issue.  Thanks again for your help.  I will be using Application.OnTime to lock the cells after certain games have started and I know I have some security stuff to add later.  

excel
MS Office Version: 2019
posted by: scockster (rep: 4) on Fri Aug 2, 2019 at 2:45 am
Answer
Discuss

Discussion

We are at cross purposes with regard to the meaning of "permission to view one row only". To be clear: this effect can best be achieved by using a filter but filters can be removed easily by anyone, revealing the entire sheet. The safer way is to extract a copy the data of one row into a form or another sheet. You might permit editing of those data, meaning copying back the modified data to the database.
Anyway, you keep insisting on showing only one row and that isn't possible - or I don't know how to do it, if the condition is that the user shall not access the other data easily.
As regards access to the entire sheet, either with or without editing authority, that is an Admin functionality, in essence unrelated to the above. It can't be achieved using the same code, nor the same method. It doesn't belong in the same pot, nor in the same thread here - perhaps because I don't know how to apply the same system to both ends.
Variatus (rep: 4889) Aug 2, '19 at 10:45 am
Maybe I'm making things harder than I should.  I think we are atleast on the same page as far as what I am trying to accomplish.  I have learned alot from this website so I willl ponder a bit on this maybe come up with a vehicle of some sort to accomplish this another way.  I dont want keep exhortating you but if you think you might have a thought on users making picks without seeing what others are doing I'm all ears.  I think I'm actually enjoying playing with excel more than video games at this point.  Its addicting.  Thanks again brother.
scockster (rep: 4) Aug 2, '19 at 9:28 pm
So this is what I'm thinking.  After they login, a second userform will pop up which will contain several drop down menus with the data that is their row.  They can choose the week they want to make their pick and it will have the remaining teams they have left to choose from as the weeks progress.  I'm assuming this is possible but I havent attempted anything like this before.  If you come up with a better solution let me know.  
scockster (rep: 4) Aug 2, '19 at 11:30 pm
Add to Discussion

Answers

0

You are right to consider the workflow at this point of developing your project. The user is shown the LogInForm and is identified as "registered" or "new". This lookup is carried out from the LogInForm. Your present setup seems to support the idea that each user has the right to view certain sheets. Presumably those sheets will be made visible once the LogInForm is closed.

From that point forward, your idea is to hide some (most) of the rows in a sheet. This isn't possible, if the invisible rows must be kept secret, as I have explained. The correct solution is to extract the data that should be shown to a userform for viewing and modification.

Of this, the consequence is that sheets with secret data must always be kept hidden. One of the consequences of this is that the Admin columns A:B have no use in the system. The identification of the user is done in the LogInForm and doesn't need to be repeated on each sheet.

The Admin sheet will be shown only to the Administrator. It seems that you plan on having a Mini-Administrator as well, one who can view but not modify. Your Admin sheet can't serve this purpose. The consequences are as follows.

  1. Assign roles to each user rather than rights. Then assign rights to each role. This is the system by which Windows works. It's good.
  2. Let's say you have Administrators (view and modify all), Oberservers (view all but don't modify) and Users (read and write their own rows in Picks).
    The Admin sheet must then allocate a code (say 0, 1, 2) to each name. I would choose -1 (Administrator), 0 (User) and 1 (Observer) because it's easy to program that zero has no rights.

Then, if an Administrator or Observer signs in, you make all sheets visible. Otherwise all sheets remain hidden and you show the user form with the user's picks.

What if Scott, the Administrator, wants to see his picks?
The LogInForm stores the REF number (Admin!D:D) in the system memory. The row in which the user has picks in Picks has the same number in column A. With the help of this number any user's data can be retrieved. However, when an A or O signs in the REF number is lost because the PickForm isn't shown. I think the easiest way of dealing with this problem is to instruct As and Os to create separate User identities with different user names and passwords.

posted by: Variatus (rep: 4889) on Sat Aug 3, 2019 at 12:45 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login