Combine 2 VBA macro codes into one

[gopirang]

Hi - I am Gopi and completely new to VBA macros. I want to automate my work related file. I found these 2 macros online and working fine if I test separately. In Sheet 1, I have a columns with Data validation for text length and proper case. I used first macro to do this. But data validation gets removed if i copy past over the cell. To prevent this i want to use second macro where it finds data validation and pops up message to prevent data validation. But i dont know how to edit or combine these 2 macros and paste into single worksheet module. I want these macros to do their action in the same sheet which i am working. Need your expert help to edit and combine into correct VBA code flow.

First macro - To change text to proper case:

[/U]Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    If Not Application.Intersect(Me.Range("G2:K10000"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If
ErrHandler:
    Application.EnableEvents = True

End Sub[U]

Second macro - To prevent data validation deleted while pasting:

[/U]Private Sub Worksheet_Change(ByVal Target As Range)    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub


Private Function HasValidation(r) As Boolean
'   Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False

End Function[U]

 

[gopirang]

Hi - I have cross posted the dame issue in other forums too. Below are the cross post links.

https://www.excelforum.com/excel-pr...243460-need-help-to-combine-2-vba-macros.html
https://www.mrexcel.com/forum/excel...acro-codes-into-one.html?posted=1#post5133945

 

[Logit]

.
First understand that I haven't checked if your macros work as intended. Assuming they do you can do one of two things :

COMBINE THE MACROS LIKE THIS :

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    If Not Application.Intersect(Me.Range("G2:K10000"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If


If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
ErrHandler:
    Application.EnableEvents = True


End Sub

Or you could place one macro into a regular sub and call it from the first Worksheet_Change event :

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    On Error GoTo ErrHandler:
    If Not Application.Intersect(Me.Range("G2:K10000"), Target) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            Application.EnableEvents = False
            Target.Value = StrConv(Target.Text, vbProperCase)
            Application.EnableEvents = True
        End If
    End If


Call HasValidation [B]'<<----- call other sub here[/B]

ErrHandler:
    Application.EnableEvents = True


End Sub

'Here is other sub:

Sub HasValidation()
If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub