Another Excel Question

[MOSTAFA]

Hi

I've got a decent excel spreadsheet (10MB) to run 10 pilots hours plus a summary IAW CAP 371.

It's obviously quite big but works well. The only thing it does not do is when opened it does not open on todays date, is this possible? Any ideas would be appreciated.

Basically, each pilot has his/hers own page and enters their own data which then appears on a daily summary for management.

[Temet_Nosce]

Look around here (http://www.mrexcel.com/forum/index.php) for information on running a script on Opening the document. It is possible to make it appear to open on todays date...

Good luck!

TN

[Whirlygig]

Not quite sure exactly what you're after but have you tried the formula

=TODAY()

That can be used to update other cells.

Cheers

Whirls

[Atlas Shrugged]

=NOW()

Can be formatted to display date only, date & time anyway you wish

[MOSTAFA]

Sorry about this but but I have not given you enough information. The =TODAY() works for a cell and thanks to all for the tips. I have also now posed the question on the Excel site.

Each pilot has a sheet with 30 odd columns going accross to cover all the eventualities of the limitations. Each line is a day (dated in column A)

Daily entry by the pilot is easy in the 5 unprotected columns, these are: start time, end time (split shift), start, End and hours flown.

As they enter data on their sheets this it is transfered to the summary albeit, in the same format but it has to be slightly larger (10 lines) to accommodate 10 pilots.

So at the end of the day or whenever the computer is switched off! Or used to run another application ie the internet, whatever. Then, when the pilot opens the Icon to the spreadsheet; for data entry purposes, I would like it to go to todays date on the spreadsheet. At present this goes to the day that the last data entry was made (this is no big thing but it would be nice if it didn't)

How do I get it to open on todays line so that data can be entered?

I hope that makes it a little clearer.

Many thanks

[vindaloo]

In the Workbook code, put something like this:

Private Sub Workbook_Open()

On Error GoTo error
Sheets(Format(Date, "dd-mm-yyyy")).Select
Exit Sub

error:
MsgBox ("Today's sheet doesn't exist")

End Sub

This will select the sheet named as today's date (eg 29-01-2008) or issue a message if it can't be found.

HTH,
Vindaloo

[tired-flyboy]

Can't think exactly how to do it but you could modify the code from this post (#5) to suit your purposes.

should work

post 5 is the one you want (http://www.ozgrid.com/forum/showthread.php?t=63326).

this opens up a hidden sheet given a specific date, you could modift it to open on a row in a sheet.

hth

[MOSTAFA]

This one?

Private Sub Workbook_Open()

Dim strDate As String
strDate = Format(Now(), "DD")
For Each SHT In Sheets

If InStr(1, SHT. Name, strDate, vbBinaryCompare) <> 0 Then
Sheets(SHT.Name).Activate
End If
Next SHT

End Sub

[tired-flyboy]

yea thats the one (i think - been a while since i did any excel to be honest)

another option would be asking a quesiton on experts-exchange (http://www.experts-exchange.com/)

normally a quick response, used it a couple of times.

just be sure to ask in the correct topic area.

hth

TFB

[Temet_Nosce]

Another approach you could use is to create a data entry FORM.

Look up FORMS on Excel help - you can download samples.

Or look at this article:

Creating a data entry form with the Excel Template Wizard (http://office.microsoft.com/en-us/excel/HA010346501033.aspx)

[vindaloo]

oops, didn't read the question properly - I thought that each day was on a separate sheet, not a separate line.

Anway, here's an example of what you could put in the Workbook code to place the cursor on a field containing today's date:

Private Sub Workbook_Open()
For Each cell In Worksheets("Sheet1").Range("A1:A10").Cells
If cell.Value = Date Then
cell.Select
Exit For
End If
Next cell
End Sub

You'd need to replace the Worksheet Name and the range of cells that contain the dates according to your own workbook.

Vindaloo

[MOSTAFA]

sorry to sound like a clown but it's taken me forever to get this far and I have learnt so much but!!!!

"what you could put in the Workbook code to place the cursor on a field containing today's"

When you say the Workbook Code what exactly do you mean? Up until now I put whatever summation I require it to do in the cell I want the answer to appear in or get it sent elsewhere. If, and I presume you are talking about the whole worksheet? Where does this sum/task get entered?

I have tried to highlight the whole sheet but where can you enter the code?

I truly apologise if I am sounding a little thick!

[vindaloo]

The menu option Tools --> Macro --> Visual Basic Editor will allow you to enter some code - once in here, ensure the Project Explorer is open (Ctrl + R) and then double-click on ThisWorkbook. Paste the code into the window that just opened up and then you can adjust the Worksheet Name and cell range as required.

Vindaloo

[tired-flyboy]

the code is put into the VB editor (in 2007 found under the developer tab),

can't remember what it is in 2003 or before.

here you can choose the option of individual sheets or the complete workbook.

keep asking - only way to learn (or i believe anyhow!)

edit *or as above!! lol ;)

[MOSTAFA]

Thanks everybody - sorted and working a treat

[vindaloo]

If you used my method of VB code in the WorkbookOpen() section, you might want to try the following amendment:

Put the cursor into the cell containing the Column Header of your dates, and then overtype the cell name (eg. A1) with "start_date".

Now amend the code:

Private Sub Workbook_Open()

For Each cell In Range("start_date", Range("start_date").End(xlDown)).Cells

If cell.Value = Date Then
cell.Offset(0, 1).Select
Exit For
End If

Next cell

End Sub

This will do 2 things:

1) The whole date column will be searched for today's date, even as it expands (the first example I gave you had a fixed range of A1:A10), and you won't ever have to change the VB code.

2) The line containing cell.Offset means that the cursor will be positioned on the cell to the right of today's date, as presumably you won't want to be changing the date field. The numbers can be adjusted to position the cursor elsewhere.

Regards,
Vindaloo (bored at work)

[MOSTAFA]

I like the sound of Cell offset:):):) because you are spot on.

All I go to do now is incorporate it into my code.

Many thanks