private sub worksheet_change

[jgarvey23]

All, I need to use a private sub worksheet_change(byval target as range) for Columns B & C. This code needs to change all data input into Column B & C to the date format of "yyyymmdd".

Any suggestions? The below code isnt working...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell   As Range

    On Error Resume Next
    Application.EnableEvents = False
        Cell = Range("B:C")
    For Each Cell In Target
        Cell = NumberFormat."yyyymmdd"(Cell)
    Next
    Application.EnableEvents = True
End Sub

 

[SkipVought]

hi,

Well that Worksheet_Change event is not what you want.

Just put this in a procedure and run it on demand...

Range("B:C").NumberFormat = "yyyymmdd"

 

[jgarvey23]

SKip,

I currently have that in my macro except I would prefer to skip that if I could.

Is there no method of writing that formatting rule into a private sub within the worksheet?

 

[SkipVought]

Sure, you can put that in a private sub in your sheet, what's keeping you from doing that?

 

[Deniall]

Deepest apologies if I am wrong here, but the way you have expressed your query makes me wonder whether you misunderstand the purpose of the Worksheet_Change event handler.[ ] It is automatically run in response to the user making a change to the worksheet.[ ] It is not directly initiated by the user.

 

[SkipVought]

Why do you want this executed with each change?

Once is enough, really!

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    
    On Error Resume Next
    Application.EnableEvents = False
    
    For Each t In Target
        If Not Intersect(t, Range("B:C")) Is Nothing Then
            Target.NumberFormat = "yyyymmdd"
        End If
    Next
    
    Application.EnableEvents = True
End Sub

 

[SkipVought]

In reality, you don't need to use this event code, if you were to set up your table as s Structured Table, formatting the first row of data, ie B22 accordingly. The formats and formulas are propagated from row to row as data is added.