Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
Sub Auto_Open()
Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
Check the name of that sheet before you try to insert another.
If SH.Name=Format(Date, "yyyymmdd") Then 'Decide what to do NickHK wrote in message ps.com... Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
i new to excel vba - can you give me the complete code which would
leave the workbook unchanged if a sheet already exist with todays date thxs wrote: Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
Is this what you mean ?
If SH.Name=Format(Date, "yyyymmdd") Then Exit Sub NickHK wrote in message oups.com... i new to excel vba - can you give me the complete code which would leave the workbook unchanged if a sheet already exist with todays date thxs wrote: Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
that's it !! - how do i continue with the else command.
thxs NickHK wrote: Is this what you mean ? If SH.Name=Format(Date, "yyyymmdd") Then Exit Sub NickHK wrote in message oups.com... i new to excel vba - can you give me the complete code which would leave the workbook unchanged if a sheet already exist with todays date thxs wrote: Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
Hi Transfer,
Further to Nick's suggestion, in case the sheet might be present anywhere in the workbook, try: '============= Sub Auto_Open() Dim SH As Worksheet Dim sStr As String Dim blExists As Boolean sStr = Format(Date, "yyyymmdd") Set SH = Worksheets(Worksheets.Count) On Error Resume Next blExists = CBool(Len(Sheets(sStr).Name)) On Error GoTo 0 If blExists Then 'Sheet already exists,do something, e.g.: MsgBox "Sheet " & sStr & " already exists!" Else Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = sStr End If End Sub '<<============= --- Regards, Norman wrote in message ps.com... Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
If you exit the sub, there is no "Else" part.
But if you mean : If SH.Name=Format(Date, "yyyymmdd") Then 'Sheet exists already, so do something Else Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End If 'Continue with other code ...... etc Seems like you do not want to do anything if the sheet exists already. NickHK wrote in message oups.com... that's it !! - how do i continue with the else command. thxs NickHK wrote: Is this what you mean ? If SH.Name=Format(Date, "yyyymmdd") Then Exit Sub NickHK wrote in message oups.com... i new to excel vba - can you give me the complete code which would leave the workbook unchanged if a sheet already exist with todays date thxs wrote: Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
Norman,
I was thinking I should expand it as you have, but I was being a bit lazy in answering the OP's question and sticking to the requested situation. NickHK "Norman Jones" wrote in message ... Hi Transfer, Further to Nick's suggestion, in case the sheet might be present anywhere in the workbook, try: '============= Sub Auto_Open() Dim SH As Worksheet Dim sStr As String Dim blExists As Boolean sStr = Format(Date, "yyyymmdd") Set SH = Worksheets(Worksheets.Count) On Error Resume Next blExists = CBool(Len(Sheets(sStr).Name)) On Error GoTo 0 If blExists Then 'Sheet already exists,do something, e.g.: MsgBox "Sheet " & sStr & " already exists!" Else Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = sStr End If End Sub '<<============= --- Regards, Norman wrote in message ps.com... Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
you are right - do not want to do anything if the sheet exists already
I guess the code below should work? Sub Auto_Open() Dim SH As Worksheet If Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") Then Exit Sub Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub NickHK wrote: If you exit the sub, there is no "Else" part. But if you mean : If SH.Name=Format(Date, "yyyymmdd") Then 'Sheet exists already, so do something Else Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End If 'Continue with other code ..... etc Seems like you do not want to do anything if the sheet exists already. NickHK wrote in message oups.com... that's it !! - how do i continue with the else command. thxs NickHK wrote: Is this what you mean ? If SH.Name=Format(Date, "yyyymmdd") Then Exit Sub NickHK wrote in message oups.com... i new to excel vba - can you give me the complete code which would leave the workbook unchanged if a sheet already exist with todays date thxs wrote: Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Auto_Open() - Sheet name - error code
Hi,
Or like that Sub Auto_Open() Dim wk As Worksheet Dim sStr As String sStr = Format(Date, "yyyymmdd") On Error Resume Next Set wk = Worksheets(sStr) On Error GoTo 0 If wk Is Nothing Then Set wk = Worksheets.Add(after:=Worksheets.Count) wk.Name = sStr End If Set wk = Nothing End Sub Regards Jean-Yves wrote in message ps.com... Sub Auto_Open() Dim SH As Worksheet Set SH = Worksheets(Worksheets.Count) Worksheets.Add after:=SH Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") End Sub What additional code do i need to add & where - in order not to get an error message if sheet name already exists when opening file a second time Thxs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Error Messaga from Sheet Code | Excel Programming | |||
Overflow error - same code, different sheet | Excel Programming | |||
Need Code to Set Scaling Zoom in PageSetup in Auto_Open Sub | Excel Programming | |||
Auto_open and environ variable error cannot find library | Excel Programming |