Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VB script error
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 |
#2
|
|||
|
|||
Apart from option 2 and 3 which were not mentioned before, that is
consistent with what you said before, so does enlighten me at all. So, what are the answers to my previous questions, and can we keep to one thread please. I and others who may wish to participate struggle to keep abreast of the topic otherwise. -- HTH Bob Phillips "Anthony" wrote in message ... 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 |
#3
|
|||
|
|||
Bob,
Sorry for cross threading but this driving me loopy now, in answer to your previous questions, if I create a log for today and it has not already been done -all is fine, however if I try and create again a pop up msg box says "Run-time error 1004 Cannot rename a sheet to the same name as another sheet, a referenced object libary or a workbook referenced by Visual Basic" I then click on debug and the ActiveSheet.Name = Format(Date, "DDMMM") part of the code is highlighted in yellow. Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "DDMMM") .Visible = False End With Application.ScreenUpdating = True End Sub any ideas ?? thanks again "Bob Phillips" wrote: Apart from option 2 and 3 which were not mentioned before, that is consistent with what you said before, so does enlighten me at all. So, what are the answers to my previous questions, and can we keep to one thread please. I and others who may wish to participate struggle to keep abreast of the topic otherwise. -- HTH Bob Phillips "Anthony" wrote in message ... 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 |
#4
|
|||
|
|||
Anthony,
Sub Create_log() Dim Foundflag as Boolean Dim MySheet 'First see if sheet already exists Foundflag = False For Each MySheet In Sheets If MySheet.Name = Format(Date, "DDMMM") Then Foundflag = True Next MySheet 'Tell user that sheet already exists If Foundflag Then MsgBox "Sheet " & Format(Date, "DDMMM") & " already exists" 'Else create sheet Else Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "DDMMM") .Visible = False End With Application.ScreenUpdating = True End If End Sub Henry "Anthony" wrote in message ... Bob, Sorry for cross threading but this driving me loopy now, in answer to your previous questions, if I create a log for today and it has not already been done -all is fine, however if I try and create again a pop up msg box says "Run-time error 1004 Cannot rename a sheet to the same name as another sheet, a referenced object libary or a workbook referenced by Visual Basic" I then click on debug and the ActiveSheet.Name = Format(Date, "DDMMM") part of the code is highlighted in yellow. Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "DDMMM") .Visible = False End With Application.ScreenUpdating = True End Sub any ideas ?? thanks again "Bob Phillips" wrote: Apart from option 2 and 3 which were not mentioned before, that is consistent with what you said before, so does enlighten me at all. So, what are the answers to my previous questions, and can we keep to one thread please. I and others who may wish to participate struggle to keep abreast of the topic otherwise. -- HTH Bob Phillips "Anthony" wrote in message ... 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 |
#5
|
|||
|
|||
Henry,you are a star..thanks very much it works !
kind rgds "Henry" wrote: Anthony, Sub Create_log() Dim Foundflag as Boolean Dim MySheet 'First see if sheet already exists Foundflag = False For Each MySheet In Sheets If MySheet.Name = Format(Date, "DDMMM") Then Foundflag = True Next MySheet 'Tell user that sheet already exists If Foundflag Then MsgBox "Sheet " & Format(Date, "DDMMM") & " already exists" 'Else create sheet Else Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "DDMMM") .Visible = False End With Application.ScreenUpdating = True End If End Sub Henry "Anthony" wrote in message ... Bob, Sorry for cross threading but this driving me loopy now, in answer to your previous questions, if I create a log for today and it has not already been done -all is fine, however if I try and create again a pop up msg box says "Run-time error 1004 Cannot rename a sheet to the same name as another sheet, a referenced object libary or a workbook referenced by Visual Basic" I then click on debug and the ActiveSheet.Name = Format(Date, "DDMMM") part of the code is highlighted in yellow. Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Format(Date, "DDMMM") .Visible = False End With Application.ScreenUpdating = True End Sub any ideas ?? thanks again "Bob Phillips" wrote: Apart from option 2 and 3 which were not mentioned before, that is consistent with what you said before, so does enlighten me at all. So, what are the answers to my previous questions, and can we keep to one thread please. I and others who may wish to participate struggle to keep abreast of the topic otherwise. -- HTH Bob Phillips "Anthony" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Script error | Excel Discussion (Misc queries) | |||
error in script from internet explorer | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Macro script error - pls help !! | Excel Discussion (Misc queries) | |||
Script Error | Excel Discussion (Misc queries) |