Excel 2010 - prompt user for name upon opening workbook then unhide column with same name

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
yes i think it is possible.

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..
Robberbaron (robr)Commented:
this is how I would do it.
Private Sub Workbook_Open()
End Sub

Open in new window

Sub Users_Change()
    Dim userid As Integer
    Dim username As String
    Dim usercolumn As Integer
    Dim shtData As Worksheet
    Set shtData = ActiveWorkbook.Sheets("Data")
    userid = Range("SelectedUser")  'F2
    usercolumn = Range("Usernames").Cells(1, 1).Offset(userid - 1, 1).Value 'F3-F8
    'hide all usercolumns
    For i = 2 To 10
        shtData.Cells(1, i).EntireColumn.Hidden = True
    Next i
    'show the current
    shtData.Cells(1, usercolumn).EntireColumn.Hidden = False
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
There's no need to prompt for the user name. You can just do this where you would substitute Sue's and Bill's (and the others)  actual user names. The password for the sheet and the VBA code is three spaces but that can be changed to whatever you like, and the passwords for locking the sheet and locking the code don't need to be the same.

Private Sub Workbook_Open()
' Unprotect the sheet. Password is 3 spaces
Sheets("Sheet1").Unprotect "   "
Columns("F:Q").EntireColumn.Hidden = True
Columns("F:Q").EntireColumn.Locked = True

'Sheets ("Sheet1")
Select Case Environ("USERNAME")
    Case "Sue's user name"
        Columns("F:F").EntireColumn.Hidden = False
    Case "Bill's user name"
        Columns("G:G").EntireColumn.Hidden = False
    ' add the rest of the cases here
End Select

' Protect the sheet.
Sheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="   "
Sheets("Sheet1").EnableSelection = xlNoRestrictions

End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

You can get user login name from OS with:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function OSUserID() As String
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    Dim i As Integer

    'strUserName = String$(254, 0)
    strUserName = ""
    For i = 1 To 254
      strUserName = strUserName + Chr(0)
    Next i
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        'OSUserID = Left$(strUserName, lngLen - 1)
        OSUserID = Replace(strUserName, Chr(0), "")
        OSUserID = ""
    End If
End Function

Open in new window

kazmdavAuthor Commented:
Thanks all, I'll review & try tomorrow.
Martin LissOlder than dirtCommented:
@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.
kazmdavAuthor Commented:
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.
Martin LissOlder than dirtCommented:
Why Grade "B"'s?
kazmdavAuthor Commented:
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?
Martin LissOlder than dirtCommented:
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.
kazmdavAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.