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

VLOOKUP using a userform

0

I have a workbook that has a Data Sheet and a Sheet called Picks that users will access to make changes to their row.  I want to hide all the other remaining rows based on who is logged in.  I will include my sheet to hopefully make it more clear.  Each week users will make a selection but I do not want them to see what other users have selected.  They will login and their row will be based on when they register.  There will probably be around 900 users.   Hopefully it will make more sense when you open it.  Thanks for any help.  This forum has been very helpful so far and I plan on looking through and answering things I'm capable of to give back.  Thanks again.  It's sitll not a finished product but this is the only thing I seem to be stuck on.  You can close the userform once the worksheet starts to access VBA.  

Answer
Discuss

Answers

0
Selected Answer

There is no easy way to hide rows in a sheet in such a way that the user couldn't make them visible simply by removing the filter. The easy way is to hide the entire sheet and extract those dat you want to show into a user form.

In the attached copy of your project I made major organisational changes - just trying to introduce some shape and direction. You may not approve of all of it but that is mutual. I hate the lack of format in your code. You may not care for how hard it is to read your code but I point out that no one wastes more time on reading your code than yourself. The more complicated code you write the greater will be the reward for making it easy to read.

Your code starts with the Workbook_Open event procedure and end with the event procedure for BeforeClose. The Open procedure calls FrmLogin and shuts down the workbook if the user couldn't identify himself. This logic appears unsupported by your code. I have a problem with both the form and the procedure CheckUser. It seems like you intend to create a system of "log in or sign in". Possible, of course, but your design of the Admin sheet seems too complicated and the idea of allowing "Row access" not feasible (see above).

So, in essence I showed how to open and close forms, how to extract a form's result into the main program, and I clarified a lot of the naming. You will find most of your WBSecurity module commented and moved elsewhere, mostly to ThisWorkbook. Code of LoginForm is reorganised and rewritten.

You would now need to clarify the flow of the program: what do you want to do after the password has been accepted? In fact, where is the test that could lead to such acceptance? (Admin!B5:B8 seems superfluous). The logic behind the Register button (label) seems incomplete. Strip the project of everything that is now redundant and, when you come next time, please ask a question :-)

Discuss

Discussion

One might consider after reading your response that I just failed my first test in excel.  You have been very helpful and I want to express my gratitude for taking the time to give such detailed explanations.  Moreover, sending me a workbook to explain such is just icing on the cake.  I hope to give back to others in this forum one day soon but it seems I still have alot to learn.  I will keep working but THANK YOU, THANK YOU, THANK YOU!!!!
scockster (rep: 4) Jul 22, '19 at 4:10 pm
You are welcome. I'm really encouraged by your effusive thanks. Please mark my answer as "Selected" and ask your next question based on the project as it will be after you have implemented what you have learned. Good luck!
Variatus (rep: 4889) Jul 23, '19 at 12:51 am
Add to Discussion


Answer the Question

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