Hi,

I hope I manage to explain what I'm looking for well here. I have an Excel spreadsheet that contains information that many people need to add comments for.

Can I add a front page that comes up upon opening the spreadsheet where people can choose their names from a list, and then only any lines assigned to them pop up? Then when they save and someone else goes back in, I'd like the selection list to come up for the next eprson.

I've no idea if this is possible but it'd solve all of my issues so would love if it was.

Hi,

This is certainly possible but requires more information than you have provided.

Here are a few questions that I can think of.

1) Do you want multiple users to work at the file at the same time. Special considerations would be required in this case.

2) Do you want a login form for each person.

3) Where is the actual data residing (Excel or some Database)?

It might not be possible to post the entire code but I can try to navigate you to the right path.

Let me know if you have any questions.

Thanks,
Prashant

On Thursday, October 18, 2012 2:04:32 AM UTC-7, D4WNO wrote:
Hi,



I hope I manage to explain what I'm looking for well here. I have an

Excel spreadsheet that contains information that many people need to add

comments for.



Can I add a front page that comes up upon opening the spreadsheet where

people can choose their names from a list, and then only any lines

assigned to them pop up? Then when they save and someone else goes back

in, I'd like the selection list to come up for the next eprson.



I've no idea if this is possible but it'd solve all of my issues so

would love if it was.









--

D4WNO

Hi D4WNO,

Perhaps you can "adjust" this select case code to do what you for an auto-search. I got bogged down trying to move from sheet to sheet so I posted in Programming for help and Claus boomed this back at me, which does exactly what I shooting for. Hope it will move you along.

I used a drop down in B1 for the names + a blank and just picked some ranges on three different sheets to represent the peoples 'lines of intrest'. So, when Kim opens to sheet 1, ckicks her name in the drop down list she is taken to a specific range that belongs to her.

Again, Claus, a frequent MVP contributor wrote the code.

Regards,
Howard

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TheDon As Range, TheKim As Range, TheBob As Range

If Target.Address < "$B$1" Then Exit Sub

ActiveWorkbook.Names.Add Name:="TheDon", _
RefersTo:=Sheets("sheet1").Range("C1:D10")
ActiveWorkbook.Names.Add Name:="TheKim", _
RefersTo:=Sheets("sheet2").Range("E1:F10")
ActiveWorkbook.Names.Add Name:="TheBob", _
RefersTo:=Sheets("sheet3").Range("G1:H10")


Select Case ActiveCell.Value
Case Is = "Don"

Application.Goto "TheDon"

MsgBox "Don's stuff"
Case Is = "Kim"

Application.Goto "TheKim"

MsgBox "Kim's stuff"
Case Is = "Bob"

Application.Goto "TheBob"

MsgBox "Bob's stuff"

Case Is = " "
MsgBox "Blank (space) stuff"
End Select
End Sub

On Thursday, October 18, 2012 2:04:32 AM UTC-7, D4WNO wrote:
Hi,



I hope I manage to explain what I'm looking for well here. I have an

Excel spreadsheet that contains information that many people need to add

comments for.



Can I add a front page that comes up upon opening the spreadsheet where

people can choose their names from a list, and then only any lines

assigned to them pop up? Then when they save and someone else goes back

in, I'd like the selection list to come up for the next eprson.



I've no idea if this is possible but it'd solve all of my issues so

would love if it was.









--

D4WNO

D4WNO

Find this line of code and make the change.

Select Case ActiveCell.Value

change to:
Select Case Target.Value

Howard

Hi,

Am Thu, 18 Oct 2012 09:04:32 +0000 schrieb D4WNO:

Can I add a front page that comes up upon opening the spreadsheet where
people can choose their names from a list, and then only any lines
assigned to them pop up? Then when they save and someone else goes back
in, I'd like the selection list to come up for the next eprson.

try it with Review = Allow users to edit ranges. In foolowing code you
have to adapt the ranges, the user names and the sheet name:

Private Sub Workbook_Open()
Dim rngClaus As Range
Dim rngAnne As Range
Dim rngJim As Range

On Error Resume Next
With Sheets("Sheet1")
Set rngClaus = .Range("A1:H11")
Set rngAnne = .Range("A12:H19")
Set rngJim = .Range("A20:H25")

..Unprotect "myPassword"
..Protection.AllowEditRanges.Add _
Title:="Claus", _
Range:=rngClaus
..Protection.AllowEditRanges.Add _
Title:="Anne", _
Range:=rngAnne
..Protection.AllowEditRanges.Add _
Title:="Jim", _
Range:=rngJim

Select Case Application.UserName
Case "Claus B."
rngAnne.EntireRow.Hidden = True
rngJim.EntireRow.Hidden = True
Case "Anne W."
rngClaus.EntireRow.Hidden = True
rngJim.EntireRow.Hidden = True
Case "Jim G."
rngAnne.EntireRow.Hidden = True
rngClaus.EntireRow.Hidden = True
End Select
..Protect "myPassword"
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2