Merging some VBA together?

dannyok90

Board Regular
Joined
Aug 30, 2016
Messages
115
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!

Code:
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

Code:
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 :)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You have two IF statements but only one END IF ... I'd guess you need END IF just before the second IF
 
Upvote 0
Try changing the 2nd If to ElseIf
 
Upvote 0
And delete this:

Code:
With ActiveSheet

You dont have an end with and even if you did you arent using it anyway.
 
Upvote 0
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!

Code:
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

Code:
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 :)

Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top