hyperlink navigation using combo box or list
 

I have a worksheets each named for a week in the year. Within each worksheet
a person has a log entry for that week. I want people to be able to navigate
through the workbook using a combo box or list that hyperlinks to each
person's log entry for the specified week.

Right now I have hyperlinked the date under each person's name like this:

John Jane Jack
01-07 01-07 01-07
01-14 01-14 01-14
01-21 01-21 01-21

But it requires too much scrolling when you have 52 weeks, so I want to use
combo boxes that navigates based on the selected Person and Week. What is the
best way to do this?

Thanks,
Kate

hyperlink navigation using combo box or list
 

It can be done, but would be easier without hyperlinks. This can become
quite a project depending on how much you are willing to set up manually and
how much you want the code to do automatically. For example, will you create
a list of users or will the code do it. Using code it would ideally loop
through all the log sheets and gather all the user names from them. Then
remove duplicate names leaving a list of unique names of anyone that has an
entry on any log sheet. That list would appear in a listbox to choose a name
from. The other list would come from again looping through all the sheets
and making a list of their names to choose from. When you choose first, a
sheet name and then a user name, Excel would activate the chosen sheet and
use the find method to locate the user name and activate that cell. I am
sure no one here is going to do all that for you, but if you get it started
with some code we can help you plug through it. Using the macro recorder to
write code for what you do manually, is a good way to get started and learn
how Excel code is written.
Mike F
"Kate Choi" wrote in message
...
I have a worksheets each named for a week in the year. Within each
worksheet
a person has a log entry for that week. I want people to be able to
navigate
through the workbook using a combo box or list that hyperlinks to each
person's log entry for the specified week.

Right now I have hyperlinked the date under each person's name like this:

John Jane Jack
01-07 01-07 01-07
01-14 01-14 01-14
01-21 01-21 01-21

But it requires too much scrolling when you have 52 weeks, so I want to
use
combo boxes that navigates based on the selected Person and Week. What is
the
best way to do this?

Thanks,
Kate

hyperlink navigation using combo box or list
 

Mike,

Yeah i don't expect anyone to come up with that much code. I'll give it a
try like you suggested using the Macro recorder (that's the way I've usually
done it anyhow)--just thought there might be simple method. Since I plan
setting up the names and dates manually, it will not be quite as involved as
the automatic set up--simplifying matters quite a bit. But now that you
brought up the automatic set up, I will pursue that once I've figured out how
do it with a manual set up.

Thanks




"Mike Fogleman" wrote:

It can be done, but would be easier without hyperlinks. This can become
quite a project depending on how much you are willing to set up manually and
how much you want the code to do automatically. For example, will you create
a list of users or will the code do it. Using code it would ideally loop
through all the log sheets and gather all the user names from them. Then
remove duplicate names leaving a list of unique names of anyone that has an
entry on any log sheet. That list would appear in a listbox to choose a name
from. The other list would come from again looping through all the sheets
and making a list of their names to choose from. When you choose first, a
sheet name and then a user name, Excel would activate the chosen sheet and
use the find method to locate the user name and activate that cell. I am
sure no one here is going to do all that for you, but if you get it started
with some code we can help you plug through it. Using the macro recorder to
write code for what you do manually, is a good way to get started and learn
how Excel code is written.
Mike F
"Kate Choi" wrote in message
...
I have a worksheets each named for a week in the year. Within each
worksheet
a person has a log entry for that week. I want people to be able to
navigate
through the workbook using a combo box or list that hyperlinks to each
person's log entry for the specified week.

Right now I have hyperlinked the date under each person's name like this:

John Jane Jack
01-07 01-07 01-07
01-14 01-14 01-14
01-21 01-21 01-21

But it requires too much scrolling when you have 52 weeks, so I want to
use
combo boxes that navigates based on the selected Person and Week. What is
the
best way to do this?

Thanks,
Kate