VBA and Macros 2013 book question

[Nurzhan]

Hallo,
Tried to use the below code from the book on page 136 but it doesn't work. I use excel 2010.

Private Sub Worksheet_Change(ByVal Target As Range)Dim ThisColumn As Integer
Dim UserInput As String, NewInput As String
ThisColumn = Target.Column


If ThisColumn < 3 Then
  If Target.Count > 1 Then Exit Sub
  If Len(Target) = 1 Then Exit Sub


  UserInput = Target.Value
  If UserInput > 1 Then
     NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
     Application.EnableEvents = False
     Target = NewInput
     Application.EnableEvents = True
    
   End If
End If
End Sub

 

[sheetspread]

Try this on a blank sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ThisColumn As Integer
Dim UserInput As String, NewInput As String
ThisColumn = Target.Column

  If Target.Count > 1 Or Len(Target) = 1 Then
  Exit Sub
  End If
  
  UserInput = Target.Value
  If UserInput > 1 Then
     NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
     Application.EnableEvents = False
     Target = NewInput
     Application.EnableEvents = True
    
   End If

End Sub

I don't have the book so not sure why it doesn't want columns A-B filled (data from other exercises are there?)

 

[Dryver14]

As a newbie user myself a simple question is are you putting this code in a worksheet or a module

 

[Nurzhan]

Doesn't work either.
This Ex's task acc to the book:
C A S E S T U DY: QUICKLY ENTERING MILITARY TIME IN TO A CELL
You’re entering arrival and departure times and want the times to be formatted with a 24-hour clock, which is also known
as military time. You have tried formatting the cell, but no matter how you enter the times, they are displayed in the 0:00
hours and minutes format.
The only way to get the time to appear as military time such as 23:45, is to have the time entered in the cell in this manner.
Because typing the colon is time-consuming, it would be more efficient to enter the numbers and let Excel format it
for you. The solution is to use a Change event to take what is in the cell and insert the colon for you: then goes the code above.

An entry of 2345 will display as 23:45. Note that the code limits this format change to Columns A and B (If
ThisColumn < 3). Without this limitation, entering numbers anywhere on a sheet such as in a totals column would
force it to be reformatted.

 

[Nurzhan]

It's in module. In VBA I insert a module and type the coding there. Is it correct?

 

[Nurzhan]

Thanks man! Good question that allowed me to find the error. I didn't know that I can put codes in Worksheets.

 

[Nurzhan]

Now I want to rewrite this code so that this event works in columns AU,AV, BA and BB. How can i do that?