VB Script error
Hi,
I was given this script below in answer to a previous question by Bob Phillips (thanks Bob) . Here is my previous question : "if a log for 'today' has already been created, what code do I (well you!) need to add to advise the user of this, and a message box asking them if they want to view it,then being taken there if required., thanks again" the script Bob provided is Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True On Error Resume Next Set sh = Worksheets(Format(Date, "YYYY-MM-DD")) On Error Goto 0 If Not sh Is Nothing Then ans = Msgbox("Sheet already exists - view it?", vbYesNo) If ans = vbYes Then sh.ACtivate End If Else .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "YYYY-MM-DD") .Visible = False End If End With Application.ScreenUpdating = True End Sub ....but on trying this the macro stops and places the error at : If Not sh Is Nothing Then Can anybody resole this as I am new and haven't got a clue! thanks |
Anthony,
I have just tried it again, and it works fine for me. A couple of questions. On the day you ran it, did that day's worksheet already exist? What error dialog did you get back? -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi, I was given this script below in answer to a previous question by Bob Phillips (thanks Bob) . Here is my previous question : "if a log for 'today' has already been created, what code do I (well you!) need to add to advise the user of this, and a message box asking them if they want to view it,then being taken there if required., thanks again" the script Bob provided is Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True On Error Resume Next Set sh = Worksheets(Format(Date, "YYYY-MM-DD")) On Error Goto 0 If Not sh Is Nothing Then ans = Msgbox("Sheet already exists - view it?", vbYesNo) If ans = vbYes Then sh.ACtivate End If Else .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "YYYY-MM-DD") .Visible = False End If End With Application.ScreenUpdating = True End Sub ...but on trying this the macro stops and places the error at : If Not sh Is Nothing Then Can anybody resole this as I am new and haven't got a clue! thanks |
Sorry Bob - I do appreciate ur help but now I'm very confused,
maybe if I explain 'the whole picture' you can come up with some better code?? I have a menu on my personal toolbar with 3 options 1) create new log 2) view today's log 3) vire historic log so what I requie is :- in option1 for the user to be able to create a log, which is a copy of my 'master log' worksheet and rename this new sheet as today's date. However if a log has already been opened for 'today' a msg box advising the user so and if they wish to view it. in option2 for the user to be able to view todays log (ie a search for a log named 'todays date') - again if one not found then a msg box advising so and a further option to create one.in option3 a msg box to alow the user to input a date eg 13Jul and a search is carried out for that named worksheet if found it is displayed - if not a msg box advising so. I Hope this has made my final goal more understanable and I would be very very very gratful for any code you could provide. Thanks for your understanding Anthony "Bob Phillips" wrote: Anthony, I have just tried it again, and it works fine for me. A couple of questions. On the day you ran it, did that day's worksheet already exist? What error dialog did you get back? -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi, I was given this script below in answer to a previous question by Bob Phillips (thanks Bob) . Here is my previous question : "if a log for 'today' has already been created, what code do I (well you!) need to add to advise the user of this, and a message box asking them if they want to view it,then being taken there if required., thanks again" the script Bob provided is Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True On Error Resume Next Set sh = Worksheets(Format(Date, "YYYY-MM-DD")) On Error Goto 0 If Not sh Is Nothing Then ans = Msgbox("Sheet already exists - view it?", vbYesNo) If ans = vbYes Then sh.ACtivate End If Else .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "YYYY-MM-DD") .Visible = False End If End With Application.ScreenUpdating = True End Sub ...but on trying this the macro stops and places the error at : If Not sh Is Nothing Then Can anybody resole this as I am new and haven't got a clue! thanks |
All times are GMT +1. The time now is 08:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com