multiple Private Subs on same sheet

[Homer_J]

Hi All,

I have the below but cant get it to work.

Column A is a date field and when someone adds a date Column B shows the month same logic in Column L and M. If i just have the first sub it works fine but when i add the second it stops working. Reason for this is that the sheet is filled in my many people and i display the month in a separate sheet so trying to avoid anyone deleting formulas but also not having formulas hard coded to keep the file size down.

I would like to actually have this set the Month in Column B then remove the formula and leave the month again to keep the file size down0. Hopefully i have been clear enough please help......


Private Sub Scanned_Date(ByVal Target As Range)


Dim B As Range, A As Range, t As Range


Set B = Range("B:B")
Set A = Range("A:A")
Set t = Target


If Intersect(t, A) Is Nothing Then Exit Sub


Application.EnableEvents = False
Range("B" & t.Row).Value = "=IF(RC[-1]="""","""",MONTH(RC[-1]))"
Application.EnableEvents = True


End Sub


Private Sub Rejected_Date(ByVal Target As Range)


Dim M As Range, L As Range, t As Range


Set M = Range("M:M")
Set L = Range("L:L")
Set t = Target


If Intersect(t, L) Is Nothing Then Exit Sub


Application.EnableEvents = False
Range("M" & t.Row).Value = "=IF(RC[-1]="""","""",MONTH(RC[-1]))"
Application.EnableEvents = True


End Sub

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter a Date in column A or column L the month will be entered in column B or column M

You have two choices how you want formatting either like 7 Or July
Remove the ' from in front of one you want to use.

MMM equal Jan Or MMMM equals January

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/9/2018  2:21:38 AM  EST
If Not Intersect(Target, Range("A:A,L:L")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
'Target.Offset(, 1).Value = Month(Target.Value) ' Use if you want month like 4
Target.Offset(, 1).Value = Format(Target.Value, "MMMM") ' Use to format Month like April
End If
Exit Sub
M:
MsgBox "You did not enter a proper date. You entered  " & Target.Value
End Sub

 

[Homer_J]

Thank you this worked perfectly,

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter a Date in column A or column L the month will be entered in column B or column M

You have two choices how you want formatting either like 7 Or July
Remove the ' from in front of one you want to use.

MMM equal Jan Or MMMM equals January

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/9/2018  2:21:38 AM  EST
If Not Intersect(Target, Range("A:A,L:L")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
'Target.Offset(, 1).Value = Month(Target.Value) ' Use if you want month like 4
Target.Offset(, 1).Value = Format(Target.Value, "MMMM") ' Use to format Month like April
End If
Exit Sub
M:
MsgBox "You did not enter a proper date. You entered  " & Target.Value
End Sub

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you this worked perfectly,