New to VBA, need help with " Compile error: Block If without End If "

[Novelec]

Hi all,

Let me start by saying this is my first post, so I apologize if my etiquette/post format is not 100%, however I will do my best. I've been referencing this forum for a while now and have found it a fantastic source of info, so thanks to all those that contribute, particular the forum veterans.

I was hoping someone could give me some help trouble shooting an error I get as a result of my VBA coding. I have worked with excel for several years, however this is the first project I’ve used VBA, so forgive me if my errors are amateur. I’m not sure if the layout of my code is correct, but I’ve done my best to keep it organised. What I am trying to achieve is centred around a double clicked marlett check box. At work I complete weekly maintenance checks on various parts of my building. When the sheet is opened, a cell containing the item that needs to be inspected has two empty cells next to it. One of the empty cells is a marlett checkbox for if the inspection passes, the other for if it fails.

I’ve used named ranges to refer to groups of check boxes, according to whether they are a pass or fail, and their location in the building. I have written separate codes for the pass and fail check boxes. This is because I am trying to display an input box when the fail option is selected, prompting the user to give reason for the failure. The reason is inserted one cell to the right of the active check box cell. If the user enters nothing, or closes the input box, the check box is cleared. The pass check box will simply check or uncheck when double clicking the cell. I’ve also included code for starting the sheet in a full screen view, but have disabled it until I have finished the workbook.
Can someone help me identify where I’ve gone wrong...? I’ve attached the code below. Any help will be greatly appreciated.
Dan



Option Explicit
Dim User_Fault_Input As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' "No" Check Box Command '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Target.Count > 1 Then
Exit Sub

If Intersect(Target, Range("Generator_Room_No_Checks, Generator_Control_Room_No_Checks, UPS_2_4_Room_No_Checks, Generator_Main_Switchroom_No_Checks, Lift_Motor_Room_No_Checks")) Is Nothing Then
If Intersect(Target, Range("Main_Corridor_No_Checks, UPS_1_3_5_Room_No_Checks, Battery_Room_No_Checks, UPS_6_7_Room_No_Checks, Fuel_Pump_Room_No_Checks, Undercroft_No_Checks")) Is Nothing Then Exit Sub
End If

Target.Font.Name = "marlett"

If Target.Value <> "a" Then
Target.Value = "a"
Cancel = True
Exit Sub
End If

User_Fault_Input = InputBox("Equipment check unaccebtable - Please enter reason", "Equipment Check Unaccebtable", "Begin typing...")
ActiveCell.Offset(0, 1).Value = User_Fault_Input

If User_Fault_Input = "" Then
Target.ClearContents
Cancel = True
Exit Sub
End If

If Target.Value = "a" Then
Target.ClearContents
Cancel = True
Exit Sub
End If
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^'


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' "Yes" Checkbox Command '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Target.Count > 1 Then
Exit Sub

If Intersect(Target, Range("Generator_Room_Yes_Checks, Generator_Control_Room_Yes_Checks, UPS_2_4_Room_Yes_Checks, Generator_Main_Switchroom_Yes_Checks, Lift_Motor_Room_Yes_Checks")) Is Nothing Then
Exit Sub
End If

If Intersect(Target, Range("Main_Corridor_Yes_Checks, UPS_1_3_5_Room_Yes_Checks, Battery_Room_Yes_Checks, UPS_6_7_Room_Yes_Checks, Fuel_Pump_Room_Yes_Checks, Undercroft_Yes_Checks")) Is Nothing Then
Exit Sub
End If

Target.Font.Name = "marlett"

If Target.Value <> "a" Then
Target.Value = "a"
Cancel = True
Exit Sub
End If

If Target.Value = "a" Then
Target.ClearContents
Cancel = True
Exit Sub
End If


End Sub
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^'


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Sets "Full Screen Mode", and specifies zoom upon activation of Basement Worksheet '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Activate()

'ActiveWindow.Zoom = 122
'Application.DisplayFullScreen = True
'Application.DisplayFormulaBar = False
'ActiveWindow.DisplayWorkbookTabs = False
'ActiveWindow.DisplayHeadings = False
'ActiveWindow.DisplayGridlines = False

End Sub
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^'

 

[Andrew Poulsom]

This:

If Target.Count > 1 Then
Exit Sub

Can be either of:

If Target.Count > 1 Then
    Exit Sub
End If

If Target.Count > 1 Then Exit Sub

 

[Novelec]

Thanks Andrew,

I have added this in, but now have a an error:

run-time error '1004': Method 'Range' of object '_Worksheet' failed.

The debug highlights the second " If Intersect (Target, " line of text in the "No Check Command Box". What else could I be missing...?

Thanks for your assistance.

 

[Andrew Poulsom]

If they are named ranges try:

If Intersect(Target, Union(Range("Generator_Room_No_Checks"), Range("Generator_Control_Room_No_Checks"), Range("UPS_2_4_Room_No_Checks"), Range("Generator_Main_Switchroom_No_Checks"), Range("Lift_Motor_Room_No_Checks"))) Is Nothing Then

 

[fhoest]

Hi,
if you goto a new line with the same command use this "_" (underscore)
like this:

If Intersect (Target,  " line of text in the "No Check Command Box". _
......... then
code
end if

Try.
Sorry if it's wrong (I'm learning english ,so it's not easy to understand all)
Regards.

 

[Novelec]

Thanks fhoest,

Unfortunately this didn't work either. Now the error looks like it has reversed...? "End If without Block If."

Any other suggestions?

 

[Andrew Poulsom]

Did you try what I suggested?

 

[Novelec]

Hi Andrew,

I did try using the following, as you suggested. I also changed the rest of the If Intersect commands to match... I had the same error: " Method 'Range' of object '_Worksheet' failed. ". Thanks so much for your help so far, do you have any other advice?


If Intersect(Target, Union(Range("Generator_Room_No_Checks"), Range("Generator_Control_Room_No_Checks"), Range("UPS_2_4_Room_No_Checks"), Range("Generator_Main_Switchroom_No_Checks"), Range("Lift_Motor_Room_No_Checks"))) Is Nothing Then
</pre>

 

[Andrew Poulsom]

What is eg Generator_Room_No_Checks exactly?

 

[Novelec]

It is a named range of cells in a worksheet. In this case (Generator_Room_No_Checks) are marlett check boxes for failed checks in the Generator Room.