Ok, I've been slaving over this problem and even after 2 days of trying I only feel close by 20% if that.
Goal:
Create a dashboard with personnel's training information that they can print or click on hyperlinks (embeded) to specific course sites. This dashboard - snapshot as I am calling it - is protected and all of the working tabs indexed by it are hidden through use of a macro.
How:
File is emailed to personnel for them to open. On opening, a userform is displayed and has a combobox for them to select their name, an adjacent text box to enter their 5 digit employee number, and a verification command button.
There is another command button for back door access for training coordinators that prompts for a password to unlock and unhide all tabs of the workbook.
Inner workings:
I have managed to create the combobox in VBA using the list action on initialization so that box on the userform works as desired. it references the personnel information tab. The text box is txtBadgeNumber with a limited character group of 5.
The verification button is meant to index the name selected in the userform combobox to match the full name on the personnel information sheet. (i'm aware this is like looking in a mirror). The index function is offset to display the Badge Number for that employee from the Personnel Information table. THEN I need the command button function to verify that the badge number entered on the userform txtBadgeNumber matches the indexed badge number.
If the badge numbers match then the user is granted access to the protected dashboard to use a described previously.
If the badge numbers don't match then vbRetryCancel to where Cancel or hitting X on the userform runs my HideEverything macro and locks the document.
My hangup:
I don't know how to get the password-style functions to work how I want and I'm close to giving up.
Private Sub SearchandValidate_Click()
Dim ReturnVal As Variant
Dim PSIFullNames As Range
Dim PersonnelInfoTable As Range
Dim txtBadgeNumber As Variant
ActiveCell.Range("DashBoard!B2").Select
ActiveCell.Value = cmdEmployeeName.Value
ReturnVal = WorksheetFunction.Index(PersonnelInfoTable, WorksheetFunction.Match(Me.cmbEmployeeName, PSIFullNames, 0), 5)
If Not (ReturnVal) Then
Response = MsgBox("Badge Number does not match Employee Name", vbRetryCancel + vbCritical)
If Response = Retry Then
MsgBox ("Retry")
ElseIf Response = Cancel Then
MsgBox ("Cancel")
Application.Run ("HideAllOtherTabs")
End If
End Sub
Private Sub UserForm_Initialize()
cmbEmployeeName.List = [PSIFullNames].Value
End Sub
Private Sub cmdTCaccess_Click()
End Sub
Goal:
Create a dashboard with personnel's training information that they can print or click on hyperlinks (embeded) to specific course sites. This dashboard - snapshot as I am calling it - is protected and all of the working tabs indexed by it are hidden through use of a macro.
How:
File is emailed to personnel for them to open. On opening, a userform is displayed and has a combobox for them to select their name, an adjacent text box to enter their 5 digit employee number, and a verification command button.
There is another command button for back door access for training coordinators that prompts for a password to unlock and unhide all tabs of the workbook.
Inner workings:
I have managed to create the combobox in VBA using the list action on initialization so that box on the userform works as desired. it references the personnel information tab. The text box is txtBadgeNumber with a limited character group of 5.
The verification button is meant to index the name selected in the userform combobox to match the full name on the personnel information sheet. (i'm aware this is like looking in a mirror). The index function is offset to display the Badge Number for that employee from the Personnel Information table. THEN I need the command button function to verify that the badge number entered on the userform txtBadgeNumber matches the indexed badge number.
If the badge numbers match then the user is granted access to the protected dashboard to use a described previously.
If the badge numbers don't match then vbRetryCancel to where Cancel or hitting X on the userform runs my HideEverything macro and locks the document.
My hangup:
I don't know how to get the password-style functions to work how I want and I'm close to giving up.
Private Sub SearchandValidate_Click()
Dim ReturnVal As Variant
Dim PSIFullNames As Range
Dim PersonnelInfoTable As Range
Dim txtBadgeNumber As Variant
ActiveCell.Range("DashBoard!B2").Select
ActiveCell.Value = cmdEmployeeName.Value
ReturnVal = WorksheetFunction.Index(PersonnelInfoTable, WorksheetFunction.Match(Me.cmbEmployeeName, PSIFullNames, 0), 5)
If Not (ReturnVal) Then
Response = MsgBox("Badge Number does not match Employee Name", vbRetryCancel + vbCritical)
If Response = Retry Then
MsgBox ("Retry")
ElseIf Response = Cancel Then
MsgBox ("Cancel")
Application.Run ("HideAllOtherTabs")
End If
End Sub
Private Sub UserForm_Initialize()
cmbEmployeeName.List = [PSIFullNames].Value
End Sub
Private Sub cmdTCaccess_Click()
End Sub
