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

How to hide rows based on user login

0

I have a workbook with 3-4 sheets.  I have created a registration and a login userform.  The registration userform consists of a Reference number, Full Name, address, email, phone, etc. and allows each user to create a username and password for logging in later.  Once registered, ALL of their information will be listed on a database sheet (Data) but only their reference number and name wiill be listed on the main sheet (Picks). Once logged in,  I only want each user to be able to view the row their name is listed on in the main sheet (Picks).  I have no idea how to handle this via VBA or any other way without doing it manually after each person registers.  There will be around 1000 users so this would be very time consuming and tideous.  I hope someone has some knowledge with this.  PLEASE HELP!!  I'm so close having this workbook done.  Thanks in adnvance.    If anyone would like me to add the Picks sheet I can.  

excel
MS Office Version: 2019
posted by: scockster (rep: 4) on Mon Jul 8, 2019 at 6:36 pm
Answer
Discuss

Answers

0
Selected Answer

In the attached demonstration workbook I have made a few preparations. 

  1. The table on the Data tab is called DataTable. This name recurs in the formulas. The reason for creating a table is to have all ranges dynamic by default, meaning you can add or delete rows without the need to change formulas.
    Note that all cells in the table are formatted as Text as should be all cells in the Pick sheet that reference them.
  2. On the Pick tab I created a validation dropdown in B3. It refers to the column UserName of the DataTable table. Add a name to the table and it appears in the dropdown without further ado because all column ranges are dynamic.
    Observe that the UserName column is the first column in the table so that it can be used with the VLOOKUP function.
  3. Picks!C3 contains this formula 
    =IFERROR(VLOOKUP($B$3,DataTable,2,FALSE),"")

    It retrieves the reference number matching the user name. This is probably different from what you intended because the user can't enter or select the reference number which would require a little more effort. You will only need that if you want to allow several identical user names in the list.
  4. Finally, there are two formulas in B9 abd C9 of the Pick sheet as follows.
    [B9] =INDEX(DataTable[#Headers],,ROW()-5)
[C9] = IFERROR(IF(VLOOKUP($B$3,DataTable,3,FALSE)=$C$5,VLOOKUP($B$3,DataTable,ROW()-5,FALSE),"No match"),"Invalid User")
    Copy both formulas down as required.
    Both formulas refer to the active row number in order to reference a table column. The ROW() function is used to create a counter here. =ROW() returns the number of the row in which the formula resides, in my example 9 because it resides in row 9. ROW()-5 therefore returns 4 which is the 4th column in the DataTable. Now, as the formula is copied down ROW() successively returns 10, 11, 12 etc. In context it specifies columns 5, 6, 7 etc of the DataTable.

Now, you can select a user name in the dropdown, see the matching reference number in the adjacent cell but no data. Enter a password in C5. If it matches the password in the DataTable the data will be displayed.

I suggest that you hide the Data tab by the following method.

  1. Open the VBA Editor
  2. Make sure that the Properties pane is displayed (left bottom).
  3. Click on the Data sheet's name in the Project Explorer pane on the left, above the Properties pane. The sheet's properties will be displayed.
  4. Scroll down to the Visible property and change the setting to xlSheetVeryHidden.

The sheet can't be made visible again from the user interface. The only way to make it visible is to reset the Visible property to xlSheetVisible in the VBA Editor window. To make this more difficult you can protect the VBA Project with a password. Such passwords can be broken easily by any determined hacker but they can't be broken accidentally.

To set a password for the VBA project, click on the VBA Project in the Project Explorer and select VBA Project Properties from the dropdown that opens.

posted by: Variatus (rep: 4889) on Mon Jul 8, 2019 at 11:58 pm
Discuss

Discussion

Hello, I want to thank you for the extensive answer you have given me.  It has taught me alot about security and how to give certain access outside of just sheet access.  I understand totally what you have offered but I wanted to see if you would look at what I have already and see if maybe I should get rid of some things to make my workbook run a little quicker.  It seems to lag a little when a user adds themselves to the database (Data Sheet).  I havent added your formulas yet because I wanted to see what you thought.  Maybe I will have something to offer to you even though I'm still new to the VBA vehicle.  It seems I cant post my file until I gain a little more reputation on this site.  Is there somewhere else I can send it to you.  It has 6 sheets but one of them is the start sheet so my userform is the only thing that is visible when starting the workbook.  Or is there a way to send this to you via this site.  Let me know and thanks again.  
scockster (rep: 4) Jul 13, '19 at 3:39 am
I'm glad my effort helped you. You seem to have a problem with your code running slow. However, this thread should now have run its course and shouldn't be used to ask other questions. Create a new one. It would be essential to have a copy of your workbook with the code in it. You ought to be able to attach a workbook regardless of reputation. Look for the button "Add files to your post" (or something like that) below the posting pane. I think it's below the "Submit" button which is why many people don't find it.
Variatus (rep: 4889) Jul 13, '19 at 4:56 am
Thanks again.  I will move foward with the speed once I get the original question mastered which I thought I had understood.  Evidentially I did not.  I guess I am having trouble taking your advice and trying to involve my login userform.  I'm a little unsure of where to put my VLOOKUP formulas.  I am going to submit a new post and add my workbook.  I will name it VLOOKUP using a userform.  Would you mind expanding a little furthur.  I am sorry.  I have learned alot but I think sometimes I make the obvious a little harder than they should be.  Thanks again.  
scockster (rep: 4) Jul 18, '19 at 4:55 am
Sorry, I didn't see this post until after I had posted my reply to your new question. However, I extensively reorganised your code for the LoginForm. You should be able to see where the VLOOKUP result is required. I think your procedure CheckUser tries for a dual purpose which is beyond its capability. It should be a function, not a sub, and it should return the number of the row found or 0. Therefore it should employ MATCH, not VLOOKUP because your form doesn't have a field where the data should be displayed. At this stage, UserName and PW must be entered by the user seeking access. The VLOOKUP might come later, but you need to let go of the idea of hiding all but one. Instead, you should hide all and extract one. Your code hasn't proceded to that point yet and your workbook design doesn't support the idea (see Admin).
Variatus (rep: 4889) Jul 18, '19 at 7:46 am
Add to Discussion
0

Try making the worksheet that should have hidden data password protected and then do an AutoFilter (Filter) in VBA that only shows their row based on the Reference number.

Each time they login, unprotect the worksheet, perform the filter, re-protect the worksheet.

posted by: don (rep: 1989) on Mon Jul 8, 2019 at 9:13 pm
Discuss


Answer the Question

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