Merging some VBA together?

[dannyok90]

Hi all


Can anybody help me merge this code correctly?

im getting a complie error block if without end if?
im new to vba and im sort of trying my hardest to understand it, i can usually snip bits of code together that i find but im struggling!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastCol As Integer
    With ActiveSheet
If (Target.Value = "Double click to add new") Then
    Target.Worksheet.Unprotect ("TG1")
    Target.Offset(, -1).EntireColumn.Copy
    Cells(1, Columns.Count).End(xlToLeft).Offset(, -1).Insert
    Cells(1, Columns.Count).End(xlToLeft).Offset(, -2).EntireColumn.Hidden = False
    Target.Worksheet.Protect ("TG1")
    Cancel = True
    
If (Target.Value = "Double click to add new row") Then
    Target.Worksheet.Unprotect ("TG1")
    Target.Offset(-1, 0).EntireRow.Copy
    Target.Worksheet.Range("A" & Target.Worksheet.Cells(Target.Worksheet.Rows.Count, "B").End(xlUp).Row - 1).Insert
    Target.Worksheet.Range("A" & Target.Worksheet.Cells(Target.Worksheet.Rows.Count, "B").End(xlUp).Row - 2).EntireRow.Hidden = False
    Target.Worksheet.Protect ("TG1")
    Cancel = True
End If

End Sub

I would also like to incorporate this bit into it too but its not essential :D

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 If Target.Column < 14 Then Exit Sub
 If Target.Column > 24 Then Exit Sub
 On Error GoTo ErrHandler
 Application.EnableEvents = False
 Target.Formula = UCase(Target.Formula)
ErrHandler:
 Application.EnableEvents = True
 End Sub

Thanks

 

[WaterGypsy]

You have two IF statements but only one END IF ... I'd guess you need END IF just before the second IF

 

[Fluff]

Try changing the 2nd If to ElseIf

 

[steve the fish]

And delete this:

With ActiveSheet

You dont have an end with and even if you did you arent using it anyway.

 

[Steve_]

Hi all


Can anybody help me merge this code correctly?

im getting a complie error block if without end if?
im new to vba and im sort of trying my hardest to understand it, i can usually snip bits of code together that i find but im struggling!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastCol As Integer
    With ActiveSheet
If (Target.Value = "Double click to add new") Then
    Target.Worksheet.Unprotect ("TG1")
    Target.Offset(, -1).EntireColumn.Copy
    Cells(1, Columns.Count).End(xlToLeft).Offset(, -1).Insert
    Cells(1, Columns.Count).End(xlToLeft).Offset(, -2).EntireColumn.Hidden = False
    Target.Worksheet.Protect ("TG1")
    Cancel = True
    
If (Target.Value = "Double click to add new row") Then
    Target.Worksheet.Unprotect ("TG1")
    Target.Offset(-1, 0).EntireRow.Copy
    Target.Worksheet.Range("A" & Target.Worksheet.Cells(Target.Worksheet.Rows.Count, "B").End(xlUp).Row - 1).Insert
    Target.Worksheet.Range("A" & Target.Worksheet.Cells(Target.Worksheet.Rows.Count, "B").End(xlUp).Row - 2).EntireRow.Hidden = False
    Target.Worksheet.Protect ("TG1")
    Cancel = True
End If

End Sub

I would also like to incorporate this bit into it too but its not essential :D

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 If Target.Column < 14 Then Exit Sub
 If Target.Column > 24 Then Exit Sub
 On Error GoTo ErrHandler
 Application.EnableEvents = False
 Target.Formula = UCase(Target.Formula)
ErrHandler:
 Application.EnableEvents = True
 End Sub

Thanks

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastCol As Integer
    With ActiveSheet
        If (Target.Value = "Double click to add new") Then
            Target.Worksheet.Unprotect ("TG1")
            Target.Offset(, -1).EntireColumn.Copy
            Cells(1, Columns.Count).End(xlToLeft).Offset(, -1).Insert
            Cells(1, Columns.Count).End(xlToLeft).Offset(, -2).EntireColumn.Hidden = False
            Target.Worksheet.Protect ("TG1")
            Cancel = True
        ElseIf (Target.Value = "Double click to add new row") Then
            Target.Worksheet.Unprotect ("TG1")
            Target.Offset(-1, 0).EntireRow.Copy
            Target.Worksheet.Range("A" & Target.Worksheet.Cells(Target.Worksheet.Rows.Count, "B").End(xlUp).Row - 1).Insert
            Target.Worksheet.Range("A" & Target.Worksheet.Cells(Target.Worksheet.Rows.Count, "B").End(xlUp).Row - 2).EntireRow.Hidden = False
            Target.Worksheet.Protect ("TG1")
            Cancel = True
        End If
    End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column < 14 Then Exit Sub
    If Target.Column > 24 Then Exit Sub
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    Target.Formula = UCase(Target.Formula)
ErrHandler:
    Application.EnableEvents = True
 End Sub

I was tempted to remove your "With ActiveSheet" from your code due to it being completely unused. I decided to leave it, and correct it, simply to show you how it should have looked. I would recommend you remove the "With ActiveSheet" and the "End With" from the code. They are not being used at all.