| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help please !
Hi,
I have a workbook containing 7 pages (one for each day of week), on each one I want the user to input the 'time' in a certain manner. I have the code below which works. However I have to place the code into each VBA editor sheet relating to that particular day, hence I have the code in the VBA editor 7 times. Is there a place to place it just once so that it will work on the entire workbook. I have tried placing it ion the thisworkbook page without any joy. the code I am using is :- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False With Target If .Count = 1 Then .Value = UCase(.Value) End If End With Application.EnableEvents = True Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C8:C100,J8:J100")) 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(.Value) Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You entered an invalid time - Please re-enter again eg 0936 for 9.36am or 2150 for 9.50pm" Application.EnableEvents = True End Sub Thankd for any help |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA help please !
Anthony, just copy the whole sub to the workbook using the
Workbook_SheetChange event. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) It will then apply to all sheets in the workbook. If you only want it to apply to certain sheets then you can test which sheet caused the event from the 'Sh' parameter. Fred "Anthony" wrote in message ... Hi, I have a workbook containing 7 pages (one for each day of week), on each one I want the user to input the 'time' in a certain manner. I have the code below which works. However I have to place the code into each VBA editor sheet relating to that particular day, hence I have the code in the VBA editor 7 times. Is there a place to place it just once so that it will work on the entire workbook. I have tried placing it ion the thisworkbook page without any joy. the code I am using is :- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False With Target If .Count = 1 Then .Value = UCase(.Value) End If End With Application.EnableEvents = True Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C8:C100,J8:J100")) 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(.Value) Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You entered an invalid time - Please re-enter again eg 0936 for 9.36am or 2150 for 9.50pm" Application.EnableEvents = True End Sub Thankd for any help |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA help please !
Fred,
You're a star - thanks One other thing - I seem to have disabled the right click part of my mouse - and I need to attach macros to things - any ideas why this has happened?? thanks Anthony "Fred" wrote: Anthony, just copy the whole sub to the workbook using the Workbook_SheetChange event. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) It will then apply to all sheets in the workbook. If you only want it to apply to certain sheets then you can test which sheet caused the event from the 'Sh' parameter. Fred "Anthony" wrote in message ... Hi, I have a workbook containing 7 pages (one for each day of week), on each one I want the user to input the 'time' in a certain manner. I have the code below which works. However I have to place the code into each VBA editor sheet relating to that particular day, hence I have the code in the VBA editor 7 times. Is there a place to place it just once so that it will work on the entire workbook. I have tried placing it ion the thisworkbook page without any joy. the code I am using is :- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False With Target If .Count = 1 Then .Value = UCase(.Value) End If End With Application.EnableEvents = True Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C8:C100,J8:J100")) 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(.Value) Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You entered an invalid time - Please re-enter again eg 0936 for 9.36am or 2150 for 9.50pm" Application.EnableEvents = True End Sub Thankd for any help |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA help please !
In the VB IDE immediate window, type
-- HTH Bob Phillips "Anthony" wrote in message ... Fred, You're a star - thanks One other thing - I seem to have disabled the right click part of my mouse - and I need to attach macros to things - any ideas why this has happened?? thanks Anthony "Fred" wrote: Anthony, just copy the whole sub to the workbook using the Workbook_SheetChange event. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) It will then apply to all sheets in the workbook. If you only want it to apply to certain sheets then you can test which sheet caused the event from the 'Sh' parameter. Fred "Anthony" wrote in message ... Hi, I have a workbook containing 7 pages (one for each day of week), on each one I want the user to input the 'time' in a certain manner. I have the code below which works. However I have to place the code into each VBA editor sheet relating to that particular day, hence I have the code in the VBA editor 7 times. Is there a place to place it just once so that it will work on the entire workbook. I have tried placing it ion the thisworkbook page without any joy. the code I am using is :- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False With Target If .Count = 1 Then .Value = UCase(.Value) End If End With Application.EnableEvents = True Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C8:C100,J8:J100")) 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(.Value) Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You entered an invalid time - Please re-enter again eg 0936 for 9.36am or 2150 for 9.50pm" Application.EnableEvents = True End Sub Thankd for any help |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA help please !
and to finish
In the VB IDE immediate window, type application.CommandBars("Cell").enabled=true -- HTH Bob Phillips "Bob Phillips" wrote in message ... In the VB IDE immediate window, type -- HTH Bob Phillips "Anthony" wrote in message ... Fred, You're a star - thanks One other thing - I seem to have disabled the right click part of my mouse - and I need to attach macros to things - any ideas why this has happened?? thanks Anthony "Fred" wrote: Anthony, just copy the whole sub to the workbook using the Workbook_SheetChange event. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) It will then apply to all sheets in the workbook. If you only want it to apply to certain sheets then you can test which sheet caused the event from the 'Sh' parameter. Fred "Anthony" wrote in message ... Hi, I have a workbook containing 7 pages (one for each day of week), on each one I want the user to input the 'time' in a certain manner. I have the code below which works. However I have to place the code into each VBA editor sheet relating to that particular day, hence I have the code in the VBA editor 7 times. Is there a place to place it just once so that it will work on the entire workbook. I have tried placing it ion the thisworkbook page without any joy. the code I am using is :- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False With Target If .Count = 1 Then .Value = UCase(.Value) End If End With Application.EnableEvents = True Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C8:C100,J8:J100")) 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(.Value) Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You entered an invalid time - Please re-enter again eg 0936 for 9.36am or 2150 for 9.50pm" Application.EnableEvents = True End Sub Thankd for any help |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA help please !
Sorry Bob,
where can I find this VB IDE immediate window ???? thanks again "Bob Phillips" wrote: and to finish In the VB IDE immediate window, type application.CommandBars("Cell").enabled=true -- HTH Bob Phillips "Bob Phillips" wrote in message ... In the VB IDE immediate window, type -- HTH Bob Phillips "Anthony" wrote in message ... Fred, You're a star - thanks One other thing - I seem to have disabled the right click part of my mouse - and I need to attach macros to things - any ideas why this has happened?? thanks Anthony "Fred" wrote: Anthony, just copy the whole sub to the workbook using the Workbook_SheetChange event. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) It will then apply to all sheets in the workbook. If you only want it to apply to certain sheets then you can test which sheet caused the event from the 'Sh' parameter. Fred "Anthony" wrote in message ... Hi, I have a workbook containing 7 pages (one for each day of week), on each one I want the user to input the 'time' in a certain manner. I have the code below which works. However I have to place the code into each VBA editor sheet relating to that particular day, hence I have the code in the VBA editor 7 times. Is there a place to place it just once so that it will work on the entire workbook. I have tried placing it ion the thisworkbook page without any joy. the code I am using is :- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False With Target If .Count = 1 Then .Value = UCase(.Value) End If End With Application.EnableEvents = True Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C8:C100,J8:J100")) 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(.Value) Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You entered an invalid time - Please re-enter again eg 0936 for 9.36am or 2150 for 9.50pm" Application.EnableEvents = True End Sub Thankd for any help |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA help please !
open XL, press ALT+F11 press CTRL+G
DM Unseen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
