Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed
Hi All,
I have a strange problem (or may be I do not understand it well) I am trying to run a code on the Before Save event which would delete sheet named as todays date (The function here will convert date in a string format t be saved) . The problem is when I press the Alt Save button it runs fine and deletes the sheet named "5-2-2007" but when I save the file using an Activex Button that I created on the sheet it does't deletes the sheet. The Button has the code: activeworkbook.save following is the code I have ..... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim NameOfCurrentSheet As String NameOfCurrentSheet = NameOfToday(Now()) Sheets(NameOfCurrentSheet).Delete End Sub Function NameOfToday(d As Date) As String Day1 = Day(d) Month1 = Month(d) Year1 = Year(d) NameOfToday = Day1 & "-" & Month1 & "-" & Year1 End Function Thanking you all in advance. Regards Ankur Richard www.xlmacros.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed
Why not just refer to it by its index number?
"ankur" wrote: Hi All, I have a strange problem (or may be I do not understand it well) I am trying to run a code on the Before Save event which would delete sheet named as todays date (The function here will convert date in a string format t be saved) . The problem is when I press the Alt Save button it runs fine and deletes the sheet named "5-2-2007" but when I save the file using an Activex Button that I created on the sheet it does't deletes the sheet. The Button has the code: activeworkbook.save following is the code I have ..... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim NameOfCurrentSheet As String NameOfCurrentSheet = NameOfToday(Now()) Sheets(NameOfCurrentSheet).Delete End Sub Function NameOfToday(d As Date) As String Day1 = Day(d) Month1 = Month(d) Year1 = Year(d) NameOfToday = Day1 & "-" & Month1 & "-" & Year1 End Function Thanking you all in advance. Regards Ankur Richard www.xlmacros.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed
Why not use the index number?
idx = ActiveSheet.Index Worksheets(idx).Delete You can substitute your sheet name or number for "ActiveSheet", or if you know the index number, just use the one line with worksheets() and fill in the index number. "ankur" wrote: Hi All, I have a strange problem (or may be I do not understand it well) I am trying to run a code on the Before Save event which would delete sheet named as todays date (The function here will convert date in a string format t be saved) . The problem is when I press the Alt Save button it runs fine and deletes the sheet named "5-2-2007" but when I save the file using an Activex Button that I created on the sheet it does't deletes the sheet. The Button has the code: activeworkbook.save following is the code I have ..... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim NameOfCurrentSheet As String NameOfCurrentSheet = NameOfToday(Now()) Sheets(NameOfCurrentSheet).Delete End Sub Function NameOfToday(d As Date) As String Day1 = Day(d) Month1 = Month(d) Year1 = Year(d) NameOfToday = Day1 & "-" & Month1 & "-" & Year1 End Function Thanking you all in advance. Regards Ankur Richard www.xlmacros.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows With Non-Needed Data between Needed Data | Excel Worksheet Functions | |||
More Help Needed! | Excel Discussion (Misc queries) | |||
If/AND/OR help needed.. | Excel Discussion (Misc queries) | |||
xla/xls help needed! | Excel Programming | |||
help needed | Excel Programming |