VB script error

A

Anthony

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
 
B

Bob Phillips

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.
 
A

Anthony

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
 
H

Henry

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
 
Top