Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 19
Default opening a worksheet at a specific point

i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,123
Default opening a worksheet at a specific point

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 19
Default opening a worksheet at a specific point

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP




  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,123
Default opening a worksheet at a specific point

Hi Hi crusty53

You can try this find todays date in column A in "Sheet1"

Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Sheet1").Range("A:A")
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

You van run the macro in the workbook open event in the thisworkbook module


--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP






  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 19
Default opening a worksheet at a specific point

Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP






  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,123
Default opening a worksheet at a specific point

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP





  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 19
Default opening a worksheet at a specific point

Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP






  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,123
Default opening a worksheet at a specific point

Hi crusty53

i think my sheet is named"Diary" and the colunm is "B:B"

Think ? <g

Change the Sheet name and column then like this


Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Diary").Range("B:B")
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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP








  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 19
Default opening a worksheet at a specific point

Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Diary").Range("B:B")
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


Hi Ron
still a run time error in 9 .At line with sheet name and colunms.

"crusty53" wrote:

Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP






  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,123
Default opening a worksheet at a specific point

Then your sheet name is not Diary
Maybe there is a space after the sheet name

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Diary").Range("B:B")
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


Hi Ron
still a run time error in 9 .At line with sheet name and colunms.

"crusty53" wrote:

Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message
...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP










  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 19
Default opening a worksheet at a specific point

You are a genius Ron Problem solved.
Now how do i get it to run automaticly.

"Ron de Bruin" wrote:

Then your sheet name is not Diary
Maybe there is a space after the sheet name

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Diary").Range("B:B")
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


Hi Ron
still a run time error in 9 .At line with sheet name and colunms.

"crusty53" wrote:

Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message
...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP









  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,123
Default opening a worksheet at a specific point

Keep the macro in the normal module

We now use a event that run when you open the workbook
When you open the workbook in run your macro in the normal module

Private Sub Workbook_Open()
Call Find_Todays_Date
End Sub

You must copy this event in the Thisworkbook module

Right click on the Excel icon next to File in the Worksheet menu bar
Choose view code
Paste the event there
Alt-q to go back to Excel

See also
http://www.cpearson.com/excel/codemods.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
You are a genius Ron Problem solved.
Now how do i get it to run automaticly.

"Ron de Bruin" wrote:

Then your sheet name is not Diary
Maybe there is a space after the sheet name

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Diary").Range("B:B")
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


Hi Ron
still a run time error in 9 .At line with sheet name and colunms.

"crusty53" wrote:

Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message
...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP











  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 19
Default opening a worksheet at a specific point

Ron does the macro box always come up when you open the file and ask you to
enable macros.Thanks for all the help it was fantastic and you showed great
patience with a newby.
One more question please Ron is there a way to move cell comments or notes
on a vlookup table to your worksheet with a macro,i know you cant do it in
normal excel
"Ron de Bruin" wrote:

Keep the macro in the normal module

We now use a event that run when you open the workbook
When you open the workbook in run your macro in the normal module

Private Sub Workbook_Open()
Call Find_Todays_Date
End Sub

You must copy this event in the Thisworkbook module

Right click on the Excel icon next to File in the Worksheet menu bar
Choose view code
Paste the event there
Alt-q to go back to Excel

See also
http://www.cpearson.com/excel/codemods.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
You are a genius Ron Problem solved.
Now how do i get it to run automaticly.

"Ron de Bruin" wrote:

Then your sheet name is not Diary
Maybe there is a space after the sheet name

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Diary").Range("B:B")
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


Hi Ron
still a run time error in 9 .At line with sheet name and colunms.

"crusty53" wrote:

Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message
...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP












  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,123
Default opening a worksheet at a specific point

Hi crusty53

If you only open files you trust that you can set your security to Low
ToolsMacro....Security

Another way is to sign your macro's
Read this reply from Gord Dibben

Check out Help on Digital Signing and use the Self-Cert utility which allows you
to sign your projects on your own computer only.

Self-cert digital signatures are not exportable as are paid-for ones from Thawte
and Versisign.

Go to StartProgramsMicrosoft OfficeOffice Tools and build a selfcert from
there.


One more question please Ron is there a way to move cell comments


See the code on Debra's site
http://www.contextures.com/xlcomment...l#CopyAdjacent



--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Ron does the macro box always come up when you open the file and ask you to
enable macros.Thanks for all the help it was fantastic and you showed great
patience with a newby.
One more question please Ron is there a way to move cell comments or notes
on a vlookup table to your worksheet with a macro,i know you cant do it in
normal excel
"Ron de Bruin" wrote:

Keep the macro in the normal module

We now use a event that run when you open the workbook
When you open the workbook in run your macro in the normal module

Private Sub Workbook_Open()
Call Find_Todays_Date
End Sub

You must copy this event in the Thisworkbook module

Right click on the Excel icon next to File in the Worksheet menu bar
Choose view code
Paste the event there
Alt-q to go back to Excel

See also
http://www.cpearson.com/excel/codemods.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
You are a genius Ron Problem solved.
Now how do i get it to run automaticly.

"Ron de Bruin" wrote:

Then your sheet name is not Diary
Maybe there is a space after the sheet name

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message ...
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With Sheets("Diary").Range("B:B")
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


Hi Ron
still a run time error in 9 .At line with sheet name and colunms.

"crusty53" wrote:

Hi Ron, Did as directed ,i get a run time error at 9,i think my sheet is
named"Diary" and the colunm is "B:B"

"Ron de Bruin" wrote:

Hi

Copy the macro in a normal module

1. Alt-F11
2. InsertModule from the Menubar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs


Note :My Sheet is names "Sheet1"
Test the macro first and I help you to run it automatic

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message
...
Ron, thanks,but this is a bit too advanced for me.But if you have time i
would like to explore it further.Please a bit more of an explanation as to
what your reply means and how i go about performing what you told me.Your
humble beginner crusty.

"crusty53" wrote:

Yes it has a column were the date is entered every day.

"Ron de Bruin" wrote:

Hi crusty53

Do you have column where you enter the date each day ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"crusty53" wrote in message
...
i have a diary , i would like it to open at the specific date every time i
openen the file.PLEASE HELP














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Invalid Web Query" error on opening a 2003 worksheet Linus Excel Discussion (Misc queries) 0 February 21st 06 05:55 PM
Event macro that targets specific worksheet retseort Excel Discussion (Misc queries) 3 February 20th 06 03:47 PM
Open Excel App without a blank worksheet opening Chris Excel Discussion (Misc queries) 2 September 14th 05 01:46 AM
Can specific cells on a worksheet be hidden & password protected? Ann Excel Worksheet Functions 6 July 5th 05 09:08 PM
Hyperlink to specific worksheet in Excel Glenn Mulno Links and Linking in Excel 2 February 7th 05 07:01 PM


All times are GMT +1. The time now is 07:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"