Question regarding an auto-search facility

[D4WNO]

Hi,

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

Can I add a front page that comes up upon opening the spreadsheet wher
people can choose their names from a list, and then only any line
assigned to them pop up? Then when they save and someone else goes bac
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 s
would love if it was

 

[lhkittle]

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 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 inProgramming 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("C110")
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

 

[lhkittle]

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

Find this line of code and make the change.

Select Case ActiveCell.Value

change to:
Select Case Target.Value

Howard

 

[Claus Busch]

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