Forcing specific location on startup 1

[shytott]

Hi
Is there a way when starting up Excel, to force it to display todays data in the centre of a viewing area. Ie Column E houses a full years calendar starting 1 Jan with 3 cols to the left and 7 to the right displaying data for each date. The top 8 rows are frozen with a totals block which leaves a viewing window of aprox 35 rows , so I would like to force todays entry to be visible at roughly halfway – sort of , on startup, moving daily down one row as the year progresses (or could this be tied to a button?)

Many Thanks

 

[DirkStruan]

To select the date:

Sub Macro1()
rw = Int(Now() - DateSerial(Year(Now()), 1, 1)) + 1
Range("E" & rw).Select
End Sub

To just show it, try something like:

Sub Macro2()
cll = ActiveCell.Address
Range("E9").Select
rw = Int(Now() - DateSerial(Year(Now()), 1, 1)) + 1
ActiveWindow.SmallScroll Down:=rw - 16
Range(cll).Select
End Sub

D

 

[shytott]

Hi Dirk
Thanks for replying
I tried both your solutions, (ie accessing them from a button) but they both refenced cell E323. I was wodering if there was some code that could just seek out the row which has today()in it and centre that row in teh middle of the screen. ie, from E10 onwards i have:
e10
1 Nov
2 Nov
3 Nov
4 Nov
"
"
18 Nov
19 Nov
20 Nov
etc

and Id like to be able to centre (eg. for today), 19 Nov in the middle of my screen
then tomorrow, centre 20 Nov in the middle etc.

Any clearer?
Thanks
Andy

 

[shytott]

Dirk = more news
your 2nd solution has the following effect

If the current cell location is in the past ie 18 Nov or earlier, theres no chnage, the cursor stays where it is.

If the cursor is in the future, eg 1 Dec, (and 1 Dec happens to be at the bottom of the screen), it then centers 1 Dec. But there is still no link to todays date, ie it does not leap to 19 Nov.
cheers
Andy

 

[DirkStruan]

Andy

Apologies, I misunderstood. When you said your E column held a full year's dates I assumed E1 was Jan 1st, E2 Jan 2nd etc (which I admit would be odd given your frozen 8 rows at the top of your screen). This is why my code was looking to send you to E323.

If E1 doesn't contain Jan 1st a couple of minor amendments will be necessary. The code will work, I just need to know the start point of your dates in column E - which cell houses the first date?

Change

rw = Int(Now() - DateSerial(Year(Now()), 1, 1)) + 1

to something like

rw = Int(Now() - Range(<first cell in col E with a date in>) + <row number of first cell in E with a date in>

Both bits of code refer to the number of rows down you need to travel, so it's just a case of getting the count right.

D

 

[shytott]

Hi Dirk

the fisrt cell with a date is in E10 (this spreafdsheet is only for 1st Nov to 31st Dec) but I'll need to be doing this for next years too.

I substituted your new line but it came up with a syntax error.

i typed in:
rw = Int(Now() - Range(E10) + <10>

I presuming thats wrong!

Andy
ps I have to leave for work in 20 mins and wont have internet access until Wednesday (incase i dont get back to you tonight!)

Many thanks
Andy

 

[DirkStruan]

Sorry - used <...> to highlight instructions, not for them to be included.

Try rw = Int(Now()) - Range(E10) + 10

Now() will give you today's date plus the time, hence the INT() function.

Range(E10) is your first date so INT(NOW())-Range(E10) is the number of rows down from E10 you need to go to hit the correct date.

Add 10 to allow for you starting on row 10. Given you just want the cell central-ish, I won't go into the +9 or the -1's etc.

This will hopefully get you there.

P.S. Another option would be something like Range("E10").Offset(Int(Now())-Range("E10"),0).Select

D

 

[shytott]

Hi Dirk
Managed to find myself a terminal for a few mins!

I tried option 1 with....
Sub Macro2()
cll = ActiveCell.Address
Range("E9").Select
rw = Int(Now()) - Range(E10) + 10
ActiveWindow.SmallScroll Down:=rw - 16
Range(cll).Select
End Sub


and VB came back with error:
Method 'range of' object '_GLobal' failed.
Is this what you intended for me to try?

option 2 did work but took me to row 39036!

Andy

 

[DirkStruan]

Sounds like the first run was trying to move you off the sheet, hence the range error

The following did work for me, but you need the cursor to be positioned in the "frozen" rows.

Sub Macro2()
cll = ActiveCell.Address
Range("E9").Select
rw = Int(Now() - DateSerial(Year(Now()), 1, 1)) + 1
Range("E10").Offset(Int(Now()) - Range("E10"), 0).Select
Msgbox Int(Now()) - Range("E10")
Range(cll).Select
End Sub

Can you tell me where you start the cursor off, and what values are central(ish) to where you get sent?

The Msgbox has been added so you can check that the number of rows to scrolldown are correct - remove it when you are happy.

D

 

[shytott]

Hi Dirk
Sorry its taken so long to reply - (I work away from home through the week)

Im now ending up on row 39038! Maybe a different tack might be easier......

Would it be possible for the row containing todays date always be just below the frozen header section ie on line 11 (so the field containing todays date would be positioned just below cell E10.

I have a series of buttons which run a simple macro to jump between the 8 sheets. Could these be used/modified to run each time the buttons are used?

Andy

 

[DirkStruan]

Hi Andy

Have a look at the following (in use with a sheet with a frozen pane).

Sub GotoE35()
    x = 35
    Range("E10").End(xlDown).Select
    Application.Goto Reference:="R" & x & "C5"
End Sub

Using the Goto command will position the cell (here E35) just below the freeze line. If this is acceptable for you then all we need to do is to work out the relevant row number (x) to goto.

If row 10 is your headings and the first date is in E11 then x should equal 11+Int(Now())-Range("E11"). The code is then

Sub GotoX()
    x = 11+Int(Now())-Range("E11")
    Range("E11").End(xlDown).Select
    Application.Goto Reference:="R" & x & "C5"
End Sub

Note that this actually selects the cell you need so your may want to select a cell in the frozen area as an "anchor" point.

I'm not clear on the last part of your post - do you want to click a button and go to each sheet in turn running the above macro? If so, after you select the sheet a "Call GotoX" line should solve the problem. If not, what do you want to do and can you post the code of your other macro.

D

 

[shytott]

Hi Dirk
Latest installment.........

I pasted in your last chunk of code (by right clicking the sheet name tab and view code) and ran it by using a button to Call GotoX. So of I got that all right, then in theory it should have worked. But, it zapped right to the last date entry and crashed with a debug error. When I openned up the debugger, the following line was highlighted by VB...
Application.Goto Reference:="R" & x & "C5"


With regard to the second part of my post, I have 8 sheets and you can navigate between them by simple links which simply access the required sheet tab then place the cursor in cell h8. The code for these buttons is:
Sub AD()
'
' AD Macro
' Macro recorded 11/11/2006 by home
'
' Sheets("AD").Select
Range("h8").Select
End Sub

My little idea was for when ever some one jumps to their own sheet, excel would run the code to present todays line just below the frozen headers.

Hope this isnt driving you nuts!

Andy

 

[stevexff]

Can you use a 'find' of some sort?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[shytott]

Yes, i was pondering that, but I dont kno wthe sysntax, or even if I did track down Today(), how you would anchor that to a specific point on the screen (as its going to have to move down one line every day.

Sorry, Im not too VB literate (at the moment anyway!)

 

[DirkStruan]

The crash will have been a result of a bad value for X. Add in a msgbox x and see what the result is.

Given "10" is fine and Int(Now()) should give 27/11/06 in dd/mm/yy format (illustrated by the following)

Sub now1()
MsgBox Int(now())
End Sub

I'm wondering what is in E11? If you pick a cell and type in =E11+1 and format it to general, do you get a number somewhere near 39000? (27/11/2006 being 39048) It just occured to me that if you were evaluating (27/11)/2006 [or (11/27/2006) depending on the side of the ocean] the result will be less than 1 which is why the earlier code was sending you to 39038 or thereabouts.

Today() doesn't appear to be available in VBA (hence the Int(Now()) line) but you could use it somewhere on your spreadsheet and then refer to it within your code.

With regard to the second part, amend your code to something like:

Sub AD()
'
' AD Macro
' Macro recorded 11/11/2006 by home
'
'    Sheets("AD").Select
    Range("h8").Select
     Call GotoX
End Sub

where GotoX should be in a general module rather than under the code for the sheet.

D

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[shytott]

WHHOOOOOOOOOOO HHHOOOOOOOOOOOOOOOOOOOOO
We're there! LOL

Dirk, you're a real gem

Thanks a million, I really DO apprecaite it!

My master sheet has a bigger forzen area so I twiddled the X values and the ranges and they are all reading 27 Nov right now no matter where i leave them scrolled to. Acid test will be when i log on tomorrow!

One last thing beofre I stop tormenting you, could you explain what the 3 lines of gotox are doing (I can guess bits of what it says but itwould be nice to know!).....
x = 11+Int(Now())-Range("E11")
Range("E11").End(xlDown).Select
Application.Goto Reference:="R" & x & "C5"

especially that last line!
Cheers
Andy

 

[DirkStruan]

Fantastic! Can you tell me if there was a problem with the formatting of the dates as I suspected, as it will probably annoy me for days?

Ok, here we go:

INT(val) takes the integer part of a number.
NOW() returns the current date and time. The date is the integer part and the time is given as a fraction of a day, hence the integer part gives you the date only.

Range("E11") simply refers to the value in E11 on the currently selected sheet.

Range("E11").End(xlDown).Select is the same as selecting E11 and then holding control down while pressing the down arrow key. It takes you to the end of the current (unbroken) column. You can do similarly for rows. If you get more into VBA look into the currentregion property as you will find this useful.

Application.Goto Reference:="R" & x & "C5" Is basically the same as Edit menu\Goto but you are defining the target using the variable X in row/column notation (row x on column 5/E).

If you used the goto method when your selected cell is above the "target" the target cell would appear at the bottom of your screen (it basically gets it into view with the minimum of scrolling), hence why I sent the cursor to the bottom of the column and had it move up the sheet.

If you want to check the code for the above, record a new macro and perform the actions.

Hope the above makes it clear, if not, shout and I will try to explain better.

D

 

[shytott]

Ahhhhhhhhhh I getcha!

The date thingy.........errr, well at SOME point I saw that a formatting of the date cell was 'custom' but i cant remember if it was the occasion when it zoomed to 39038. - equally, it may have been when I was working on next years spreadsheet which , surprisingly runs from Jan 07 to Dec 07 therefore doesnt have a Today() to play with so might that have something to do with it?

Anyhoo, we seem to be there. As I said, teh acid test is where do we end up tomorrow! Only 5.5 hours to go until midnight, so I might even be sad enough to stay up and watch it happen LOL!

I'll let you know tomorrow

Thanks once again for your support

 

[DirkStruan]

No probs - but to save you staying up: tomorrow INT(NOW()) will return a value one digit higher than today so you could substitute in INT(NOW())+1 to see what happens.

My dates are usually formatted to custom (dd/mm/yyyy) but if you had a format something like 00/00/0000 you would simply be putting "/" into an 8 digit number and a problem would occur. The other thing to look out for is when data has been imported and your dates are actually text (in this case take away any alignment on the cell and see if it's clutching the left - left is a date, right is text).

Cheers

Dirk