Solved: can't name darn worksheet!

[wolf.stalker]

hi all and thanks for any help you can add.

here is the basic idea of what i am doing.

1) i ask user for a date via input box (DateAns)
2) i then open up a workbook
<stuck>
3) i need to see if that worksheet exists and if not, create it. but i am stuck when trying to name wSheet the cStr(DateAns) value. i have tried everything i can think of...and i have read everything i can find on this :dunno

i have a bunch of comments of stuff i have tried but i removed them to try and keep this somewhat clean. anyone have any ideas?



Dim wSheet As Worksheet
Dim sAns As Date
Dim sNewName As String

sAns = InputBox("Please enter in date dd/mm/yy")

If Err.Number = 13 Then
MsgBox ("Please try again, you entered a bad date")
End
End If

'when i keyed in 9-9 for sAns is stores it as 9/9/2008 which is good.
sNewName = CStr(sAns) 'CStr(sAns) shows "9/9/2008" which is what i
'want but that value is NOT being stored in sNewName even though it's a 'string type...
wSheet.Text = sNewName 'sNewName shows correct value, but won't
'store in wSheet

' and of course the rest doesn't work .....

Set wSheet = Worksheets(sNewName)

If wSheet Is Nothing Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sNewName
End If

[mdmackillop]

"/" is a prohibited character in sheet naming.

[wolf.stalker]

omg :banghead: you have No idea how long i have been playing with this....lol
course, the fact that i am on a deadline (by 0600) and this is only 1/2 of the project might have something to do with it...

my solution was that i added this nice little line here..
sNewName = Format(sAns, "dd-mm-yy")
and of course it worked like a charm.

thanks again! :-)

[Bob Phillips]

Here is a useful function for checking a filename for validity




Function ValidFileName(ByVal TheFileName As String) As Boolean
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "[\\/:\*\?""<>\|]"
ValidFileName = Not RegEx.Test(TheFileName)
Set RegEx = Nothing
End Function

[wolf.stalker]

wow...thats neat..i will try and file that in to my storage bin somewhere! lol

thanks again