Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open sheet based on Username
I have a workbook with 6 worksheets. Titled Adults, Kids, Total, Kids 2,
Total Kids, and Total 2. There are certain cells on Adults, Kids, and Kids 2 that need to be filled in. Using Tools-Protection-Allow Users to Edit Ranges, I have made it so only certain people can edit certain cells without a password. I have also made all cells of all sheets open to two people (myself and my boss), so that we may edit without having to enter a password. What is need is a macro that grabs the username and then only allows that person to see a certain sheet. Example: Joe Smith and John Doe can view sheets Adults and Total Joan Smith and Jane Doe can view sheets Kids, Kids 2, Total, and Total Kids. Myself and my boss can view all sheets. If necessary, I can make them enter a password (which I would just make their username). Is this even possible? Thanks in advance for any assistance provided. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open sheet based on Username
Hi mrsviqt, Try below, but you need to:
1) Creat a new sheet name as Cover_Page or other name. 2) Use xlveryHidden (the first section of the for next loop) to hide all sheets during workbook save so that the moment workbook open all the sheets already hide. Sub Macro1() Dim Message, Title, Default, MyValue For Each sh In Sheets If sh.Name = "Cover_Page" Then Else: sh.Visible = xlVeryHidden End If Next sh Message = "Enter a username, case sensitive" ' Set prompt. Title = "InputBox Demo" ' Set title. MyValue = InputBox(Message, Title, Default) If MyValue = "Joe Smith" Or MyValue = "John Doe" Then Sheets("Adults").Visible = True Sheets("Total").Visible = True ElseIf MyValue = "Joan Smith" Or MyValue = "Jane Doe" Then Sheets("Kids").Visible = True Sheets("Kids 2").Visible = True Sheets("Total").Visible = True Sheets("Total kids").Visible = True ElseIf MyValue = "Myself" Or MyValue = "my boss" Then For Each sh In Sheets sh.Visible = True Next sh End If End Sub Hope the above help you "mrsviqt" wrote: I have a workbook with 6 worksheets. Titled Adults, Kids, Total, Kids 2, Total Kids, and Total 2. There are certain cells on Adults, Kids, and Kids 2 that need to be filled in. Using Tools-Protection-Allow Users to Edit Ranges, I have made it so only certain people can edit certain cells without a password. I have also made all cells of all sheets open to two people (myself and my boss), so that we may edit without having to enter a password. What is need is a macro that grabs the username and then only allows that person to see a certain sheet. Example: Joe Smith and John Doe can view sheets Adults and Total Joan Smith and Jane Doe can view sheets Kids, Kids 2, Total, and Total Kids. Myself and my boss can view all sheets. If necessary, I can make them enter a password (which I would just make their username). Is this even possible? Thanks in advance for any assistance provided. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open sheet based on Username
That works great. But I just thought of something else. Can we make it so
the username has to equal something, and the username that the system pullshas to be correct? This will prevent Joe Smith from entering Jane Doe's username? Thanks. "Nelson" wrote: Hi mrsviqt, Try below, but you need to: 1) Creat a new sheet name as Cover_Page or other name. 2) Use xlveryHidden (the first section of the for next loop) to hide all sheets during workbook save so that the moment workbook open all the sheets already hide. Sub Macro1() Dim Message, Title, Default, MyValue For Each sh In Sheets If sh.Name = "Cover_Page" Then Else: sh.Visible = xlVeryHidden End If Next sh Message = "Enter a username, case sensitive" ' Set prompt. Title = "InputBox Demo" ' Set title. MyValue = InputBox(Message, Title, Default) If MyValue = "Joe Smith" Or MyValue = "John Doe" Then Sheets("Adults").Visible = True Sheets("Total").Visible = True ElseIf MyValue = "Joan Smith" Or MyValue = "Jane Doe" Then Sheets("Kids").Visible = True Sheets("Kids 2").Visible = True Sheets("Total").Visible = True Sheets("Total kids").Visible = True ElseIf MyValue = "Myself" Or MyValue = "my boss" Then For Each sh In Sheets sh.Visible = True Next sh End If End Sub Hope the above help you "mrsviqt" wrote: I have a workbook with 6 worksheets. Titled Adults, Kids, Total, Kids 2, Total Kids, and Total 2. There are certain cells on Adults, Kids, and Kids 2 that need to be filled in. Using Tools-Protection-Allow Users to Edit Ranges, I have made it so only certain people can edit certain cells without a password. I have also made all cells of all sheets open to two people (myself and my boss), so that we may edit without having to enter a password. What is need is a macro that grabs the username and then only allows that person to see a certain sheet. Example: Joe Smith and John Doe can view sheets Adults and Total Joan Smith and Jane Doe can view sheets Kids, Kids 2, Total, and Total Kids. Myself and my boss can view all sheets. If necessary, I can make them enter a password (which I would just make their username). Is this even possible? Thanks in advance for any assistance provided. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open sheet based on Username
If that information cannot be shared or viewed by others, then don't put it into
an excel workbook. If you have to, then don't share it with others. Excel's worksheet and workbook protection is easily broken. It's designed to keep people from writing over formulas (and the like). It's not meant to protect information like this. mrsviqt wrote: I have a workbook with 6 worksheets. Titled Adults, Kids, Total, Kids 2, Total Kids, and Total 2. There are certain cells on Adults, Kids, and Kids 2 that need to be filled in. Using Tools-Protection-Allow Users to Edit Ranges, I have made it so only certain people can edit certain cells without a password. I have also made all cells of all sheets open to two people (myself and my boss), so that we may edit without having to enter a password. What is need is a macro that grabs the username and then only allows that person to see a certain sheet. Example: Joe Smith and John Doe can view sheets Adults and Total Joan Smith and Jane Doe can view sheets Kids, Kids 2, Total, and Total Kids. Myself and my boss can view all sheets. If necessary, I can make them enter a password (which I would just make their username). Is this even possible? Thanks in advance for any assistance provided. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open sheet based on Username
Hi Nelson,
A simple edit. Sub Macro1() Dim Message, Title, Default, MyValue For Each sh In Sheets If sh.Name = "Cover_Page" Then Else: sh.Visible = xlVeryHidden End If Next sh 'Message = "Enter a username, case sensitive" ' Set prompt. 'Title = "InputBox Demo" ' Set title. 'MyValue = InputBox(Message, Title, Default) MyValue = Application.UserName 'XXXXXXXXXXXXXXX If MyValue = "Joe Smith" Or MyValue = "John Doe" Then Sheets("Adults").Visible = True Sheets("Total").Visible = True ElseIf MyValue = "Joan Smith" Or MyValue = "Jane Doe" Then Sheets("Kids").Visible = True Sheets("Kids 2").Visible = True Sheets("Total").Visible = True Sheets("Total kids").Visible = True ElseIf MyValue = "Myself" Or MyValue = "my boss" Then For Each sh In Sheets sh.Visible = True Next sh End If End Sub John "Nelson" wrote in message ... Hi mrsviqt, Try below, but you need to: 1) Creat a new sheet name as Cover_Page or other name. 2) Use xlveryHidden (the first section of the for next loop) to hide all sheets during workbook save so that the moment workbook open all the sheets already hide. Sub Macro1() Dim Message, Title, Default, MyValue For Each sh In Sheets If sh.Name = "Cover_Page" Then Else: sh.Visible = xlVeryHidden End If Next sh Message = "Enter a username, case sensitive" ' Set prompt. Title = "InputBox Demo" ' Set title. MyValue = InputBox(Message, Title, Default) If MyValue = "Joe Smith" Or MyValue = "John Doe" Then Sheets("Adults").Visible = True Sheets("Total").Visible = True ElseIf MyValue = "Joan Smith" Or MyValue = "Jane Doe" Then Sheets("Kids").Visible = True Sheets("Kids 2").Visible = True Sheets("Total").Visible = True Sheets("Total kids").Visible = True ElseIf MyValue = "Myself" Or MyValue = "my boss" Then For Each sh In Sheets sh.Visible = True Next sh End If End Sub Hope the above help you "mrsviqt" wrote: I have a workbook with 6 worksheets. Titled Adults, Kids, Total, Kids 2, Total Kids, and Total 2. There are certain cells on Adults, Kids, and Kids 2 that need to be filled in. Using Tools-Protection-Allow Users to Edit Ranges, I have made it so only certain people can edit certain cells without a password. I have also made all cells of all sheets open to two people (myself and my boss), so that we may edit without having to enter a password. What is need is a macro that grabs the username and then only allows that person to see a certain sheet. Example: Joe Smith and John Doe can view sheets Adults and Total Joan Smith and Jane Doe can view sheets Kids, Kids 2, Total, and Total Kids. Myself and my boss can view all sheets. If necessary, I can make them enter a password (which I would just make their username). Is this even possible? Thanks in advance for any assistance provided. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Username at Workbook Open | Excel Programming | |||
Grab Open Outlook Username from Excel | Excel Programming | |||
How to get username during workbooks.open macro | Excel Programming | |||
Hide Columns based on network username | Excel Programming | |||
XMLHTTP.OPEN + PASSWORD + USERNAME | Excel Programming |