Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Freeze
I have a spreadsheet that has titles in Columns A5:A55. Column A is frozen.
In Row 2 (B2:IV2) I have monthly dates B2 = 1/1/2008, B3 = 2/1/2008, B3 =3/1/2008, etc. I am trying to create code in the sheet (sheet 4) that will open the sheet and position the sheet to the current date, i.e. if today is 7-13-2008 opening sheet 4 will position the current month (7-1-2008) next to column A. This would be in column H. Thanks for any Help! Ronbo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Freeze
Hi Ronbo,
In the below code you can change the Range and Sheet name as you required, and paste the code to a module it will work, if you want the macro to work immediately when s/s is opened then paste the code as said, open your s/s then do Alt + F11, after that paste the code to respective workbook/sheet Sub Find_Date() Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Sheet1").Range("B2:IV2") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Nothing found" End If End With End Sub "Ronbo" wrote: I have a spreadsheet that has titles in Columns A5:A55. Column A is frozen. In Row 2 (B2:IV2) I have monthly dates B2 = 1/1/2008, B3 = 2/1/2008, B3 =3/1/2008, etc. I am trying to create code in the sheet (sheet 4) that will open the sheet and position the sheet to the current date, i.e. if today is 7-13-2008 opening sheet 4 will position the current month (7-1-2008) next to column A. This would be in column H. Thanks for any Help! Ronbo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Freeze
Hi Ronbo
If I understand your requirements right, when sheet4 is activated, you want to hide columns B:M, except the column containing the current date, and select current date cell. The code below is an event code, so it is to be copied into the code sheet for sheet4. Private Sub Worksheet_Activate() TargetColumns = "B:M" Columns(TargetColumns).Hidden = True Columns(TargetColumns).Find(What:=Date, After:=Range("B1"), LookAt:=xlWhole).Select Columns(ActiveCell.Column).Hidden = False End Sub Best regards, Per "Ronbo" skrev i meddelelsen ... I have a spreadsheet that has titles in Columns A5:A55. Column A is frozen. In Row 2 (B2:IV2) I have monthly dates B2 = 1/1/2008, B3 = 2/1/2008, B3 =3/1/2008, etc. I am trying to create code in the sheet (sheet 4) that will open the sheet and position the sheet to the current date, i.e. if today is 7-13-2008 opening sheet 4 will position the current month (7-1-2008) next to column A. This would be in column H. Thanks for any Help! Ronbo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Window Tool Bar - Freeze pane does not appear | Excel Discussion (Misc queries) | |||
how do I freeze the window with VBA? | Excel Programming | |||
How to make API call to freeze excel window? | Excel Programming | |||
Excel: find Window in which I can freeze panes | New Users to Excel | |||
Advanced Window Split & Freeze Question | Excel Worksheet Functions |