I have a workbook that has about 30 sheets. Each line has a date in it (in
Column B).
The same date never (at least shouldn't) repeat itself.
I am interested in 2 different things (and that an additional add-on):
#1: When Excel opens, to get it to automatically, jump to Todays Date.
#2: To enable some kind of Search, that I can enter a date and it will give
me the corresponding line.

#3: It would be nice if I can make a Front Page, that is shown when the
Excel file opens. Ideally, I would like it to fill in the information from
Today's Date, and then below it, to allow me to have the option to search
out a date (and then maybe also to search based on other (text-based
information -that would also not repeate itself) information in the
Workbook.

I'm not a total n00b, but I would like some direction.
Also, is there anyway to create this, and then distribute it, that people
will not have to deal with Security pop-up issues?

Thanks for any help,
Yoni

You have dates spread across 30 sheets.

Are the dates random or is there some organization to them (each sheet is a
separate month - the first 12 sheets are 2003, dates start in row 2 of
column B, one per day as an example.

How does the front page fit into #1 and #2. In #2 where do you want to
enter the date

You have said jump to a sheet and show a "main" sheet with filled in data --
which is it.

Excel macro protection is what it is. You can certify your projects and
have the user accept the certification or they can lower there security
level.

--
Regards,
Tom Ogilvy

"Yonah Sudwerts" wrote in message
g.com...
I have a workbook that has about 30 sheets. Each line has a date in it (in
Column B).
The same date never (at least shouldn't) repeat itself.
I am interested in 2 different things (and that an additional add-on):
#1: When Excel opens, to get it to automatically, jump to Todays Date.
#2: To enable some kind of Search, that I can enter a date and it will
give
me the corresponding line.

#3: It would be nice if I can make a Front Page, that is shown when the
Excel file opens. Ideally, I would like it to fill in the information from
Today's Date, and then below it, to allow me to have the option to search
out a date (and then maybe also to search based on other (text-based
information -that would also not repeate itself) information in the
Workbook.

I'm not a total n00b, but I would like some direction.
Also, is there anyway to create this, and then distribute it, that people
will not have to deal with Security pop-up issues?

Thanks for any help,
Yoni

For #1), you would need a Workbook_Open event procedure that does a search
(i.e. a Range.Find) to look for the date. In the crudest form, you would
iterate through the sheets and execute the find for column B in each until
the date Date() is found, but if there is any logical order to the dates you
could add some logic to speed up the search.

For #2), you could pop up an input box for the date (or, more sophisticated,
a userform, maybe with an ActiveX date and time picker control) and then just
run the same sub as you used for Workbook_Open, but with the chosen date. To
do this, would be best to have a separate sub FindDate(SearchFor as Date) or
something that does the search; call it from both the Workbook_Open and the
procedure that prompts for a date.

For #3): You could create your "Front Page" either as a separate worksheet,
with embedded controls, or as a UserForm. In your Workbook_Open procedure
and in the other date search procedure, once the proper cell is found for
today's date extract the data you need (the Range.Offset method might work
well for that) and put the values in the cells on the first page; or perhaps
better, store the sheet name and row in some cells somewhere and then use the
INDIRECT worksheet function to create formulas that read the values in your
list (so that if you change the values on the data sheets, the values
immediately update on the front page) - e.g., assume my sub has found the
date on sheet "Sheet20" row 45 and I want the value in column E of that row
to show in a cell on my first page; my macro could store the sheet name in
cell A1 and the row in A2 (and I would hide column A) and then I could use
the formula:
= INDIRECT(A1&"!E"&A2)
to show Sheet20!E45 in the cell containing the formula.

As for deploying it, the macros by their nature will cause security issues
that need to be addressed by your users - this is by design and is necessary
(otherwise there would be no security - what if your code was malicious?).
But I am not an expert on deployment (most of my apps are for my own use, or
a small group of users), so you might find some advice on that from someone
else.

Hope this at least sparks some ideas or gives some general direction...
K Dales

"Yonah Sudwerts" wrote:

I have a workbook that has about 30 sheets. Each line has a date in it (in
Column B).
The same date never (at least shouldn't) repeat itself.
I am interested in 2 different things (and that an additional add-on):
#1: When Excel opens, to get it to automatically, jump to Todays Date.
#2: To enable some kind of Search, that I can enter a date and it will give
me the corresponding line.

#3: It would be nice if I can make a Front Page, that is shown when the
Excel file opens. Ideally, I would like it to fill in the information from
Today's Date, and then below it, to allow me to have the option to search
out a date (and then maybe also to search based on other (text-based
information -that would also not repeate itself) information in the
Workbook.

I'm not a total n00b, but I would like some direction.
Also, is there anyway to create this, and then distribute it, that people
will not have to deal with Security pop-up issues?

Thanks for any help,
Yoni