Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
New Macros Not Being Listed In Macro Dialog Box?
I inserted in a NEW workbook a Chip's Macro presented below;
Then I also saved the file; Closed & reopened the same; But upon Alt+F8 the macro name is not found to be listed in the Alt+F8 dialog Box nor can be run from VBA editor by Alt+F5? Why??? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim DateStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Formula) Case 4 ' e.g., 9298 = 2-Sep-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2) Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2) Case 6 ' e.g., 090298 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2) Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4) Case 8 ' e.g., 09021998 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4) Case Else Err.Raise 0 End Select .Formula = DateValue(DateStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid date." Application.EnableEvents = True End Sub -- Best Regards, FARAZ A. QURESHI |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
New Macros Not Being Listed In Macro Dialog Box?
I inserted in a NEW workbook a Chip's Macro presented below;
Did you insert it into a module or a sheet? This is sheet event code. Cliff Edwards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
New Macros Not Being Listed In Macro Dialog Box?
Another bit of information: no macro that is declared as Private will appear
in the [F8] (list of macros) list. "FARAZ QURESHI" wrote: I inserted in a NEW workbook a Chip's Macro presented below; Then I also saved the file; Closed & reopened the same; But upon Alt+F8 the macro name is not found to be listed in the Alt+F8 dialog Box nor can be run from VBA editor by Alt+F5? Why??? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim DateStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Formula) Case 4 ' e.g., 9298 = 2-Sep-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2) Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2) Case 6 ' e.g., 090298 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2) Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998 DateStr = Left(.Formula, 1) & "/" & _ Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4) Case 8 ' e.g., 09021998 = 2-Sep-1998 DateStr = Left(.Formula, 2) & "/" & _ Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4) Case Else Err.Raise 0 End Select .Formula = DateValue(DateStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid date." Application.EnableEvents = True End Sub -- Best Regards, FARAZ A. QURESHI |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable Macros Pop-Up Dialog Box??? | Excel Worksheet Functions | |||
Deleting shortcut key In Recording new Macros dialog box | New Users to Excel | |||
Assign Macro Dialog Box | Excel Discussion (Misc queries) | |||
I deleted all macros in Excel but still get dialog box | Excel Discussion (Misc queries) | |||
Can't run macros and security dialog not present in xl2000 | Setting up and Configuration of Excel |