kazmdav
asked on
Excel 2010 - prompt user for name upon opening workbook then unhide column with same name
Hi,
I am using the Shared workbook then Compare & Merge feature to merge user specific columns into my master workbook. 'User specific' means users are allocated their own columns (range = Columns F to Q) to update in their copy of the workbook, eg's. Sue = updates column F only, Bill = updates column G only & so on up to 12 users, thus user 12 uses column Q. Row 1 has their name as the header. The master workbook (ie. the originally 'shared' file) has columns F to Q hidden & then was copied & sent out to each of the 12 users.
When a user opens their copy, I would like the workbook to automatically unhide their named column ONLY. This is because columns A to D need to be visible for each user without horizontal scrolling.
Thus, I ask if this is possible to (a) upon opening the workbook, to prompt for the user name (It would be great if a drop down list presents containing their names but that's not mandatory) (b) to unhide their user specific column (c) active cell to be in row B of their column.
Any takers?
I am using the Shared workbook then Compare & Merge feature to merge user specific columns into my master workbook. 'User specific' means users are allocated their own columns (range = Columns F to Q) to update in their copy of the workbook, eg's. Sue = updates column F only, Bill = updates column G only & so on up to 12 users, thus user 12 uses column Q. Row 1 has their name as the header. The master workbook (ie. the originally 'shared' file) has columns F to Q hidden & then was copied & sent out to each of the 12 users.
When a user opens their copy, I would like the workbook to automatically unhide their named column ONLY. This is because columns A to D need to be visible for each user without horizontal scrolling.
Thus, I ask if this is possible to (a) upon opening the workbook, to prompt for the user name (It would be great if a drop down list presents containing their names but that's not mandatory) (b) to unhide their user specific column (c) active cell to be in row B of their column.
Any takers?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all, I'll review & try tomorrow.
@slubek: You'll find that a single Environ("UserName") statement returns the username without any other code.
@MartinLiss: Yes, I know, but environment variables can be spoofed.
ASKER
I appreciate your great responses. My delay has been because I sensed that my customer was changing her mind about her requirement & she did. Therefore I don't need to pursue this issue anymore. I was in the process of familiarising myself with your suggestions but I was unsure of which way I was going to go & will accept multiple solutions. Thank you.
Why Grade "B"'s?
ASKER
I did that because I didn't get a chance to test out the responses in full. What would you have done if you were me?
Say you are still testing and when you are done decide what grades the solution is worth. BTW if you can 'request attention' I'm sure the moderator will direct you to the article on grading that I can't find and/or reopen the question so you can if you want change the grade.
ASKER
Thanks for your thoughts. At this point in time my priorities are fulfilling my customer's needs & not to continue with something that is no longer needed (at this point in time anyway). However, I should understand grading guidelines or rules & will 'request attention' to find the article & read it. Then suitably respond.
1. there is a Workbook_Open event , under the ThisWorkbook object. You can then trigger a Textbox (or userform) to get the name. Textbox is easiest but not the dopdown list you want.
2. you can then act on the data to show & hide columns..