I have been working on a new quoting tool for my organization and I guess I did such a great job( in large part to this forum! Thank you to everyone!
) that the Engineers are worried that the sales people will create quotes without engineering overview and create some serious issues..
I have been tasked to lock portions of the quote tool based on if a Sales Engineer or other person opens the workbook. I can do this easily with a password prompt but passwords are prone to leaks, forgetting, etc etc..
I would really like to do this by using the Environ statement I found the following that looks like it will work, but would require me to duplciate the macro based on every username allowed to have full access.
What I would like to do is have a worksheet with a list of all Engineer usernames and search that list to see who gets unfettered access. And then once the workbook is complete , Set a flag that changes what Sales people can see.
As an example. Sales opens workbook their username isn't defined and they can only see sheets 1-5, Engineer opens workbook, Their username is defined and they can see sheets 1-10. Engineer processes all the forms, Completes quote then presses a macro button called return to sales .. Sales opens completed workbook and can now see sheets 1-5, 9 and 10.. Sheets 7,8 and 9 are VeryHidden.
Any help would be much appreciated..
) that the Engineers are worried that the sales people will create quotes without engineering overview and create some serious issues.. I have been tasked to lock portions of the quote tool based on if a Sales Engineer or other person opens the workbook. I can do this easily with a password prompt but passwords are prone to leaks, forgetting, etc etc..
I would really like to do this by using the Environ statement I found the following that looks like it will work, but would require me to duplciate the macro based on every username allowed to have full access.
Code:
WinUser = Environ("USERNAME") ' Windows username
Private Sub Auto_Open()
' This macro unhides sheets when the user set below opens the workbook
' It is a Private Sub to prevent it being listed with other macros
On Error Resume Next
If Application.UserName = "[I]Enter Username Here[/I]" Then
Worksheets("Sheet2").Visible = True ' First sheet to be made visible
Worksheets("Sheet3").Visible = True ' More can be listed here
End If
End Sub
Private Sub Auto_Close()
' This macro ensures the sheets are hidden when any user closes the workbook
On Error Resume Next
Worksheets("Sheet2").Visible = xlSheetVeryHidden ' Hide worksheets
Worksheets("Sheet3").Visible = xlSheetVeryHidden ' Other sheets to be hidden
End SubWhat I would like to do is have a worksheet with a list of all Engineer usernames and search that list to see who gets unfettered access. And then once the workbook is complete , Set a flag that changes what Sales people can see.
As an example. Sales opens workbook their username isn't defined and they can only see sheets 1-5, Engineer opens workbook, Their username is defined and they can see sheets 1-10. Engineer processes all the forms, Completes quote then presses a macro button called return to sales .. Sales opens completed workbook and can now see sheets 1-5, 9 and 10.. Sheets 7,8 and 9 are VeryHidden.
Any help would be much appreciated..
