Hi Luke, sorry for the rambling above, only I was trying several times to post the code and it was refusing, hence the posts declining into rambling as I was trying to sort out the reason - but yes if you could clean up the post that would be kind of you!
My code should have looked like this;-
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim n1 As Integer
Dim n2 As Integer
Dim d As Integer
If Target.Count > 1 Then Exit Sub
n1 = Target.Row - 23
n2 = Target.Row - 25
d = 34
If (n1 - d * Int(n1 / d) <> 0 And n2 - d * Int(n2 / d) <> 0) Or Intersect(Target, Range("D:I"
) Is Nothing Then Exit Sub
If Target = "ý" Then
Target = "=Hyperlink("""",""þ""
"
Target.Font.Name = "Wingdings"
Target.Font.Underline = False
Target.Font.Size = 22
ActiveCell.Offset(-1, 0).Select
Else
Target = "ý"
Target = "=Hyperlink("""",""ý""
"
Target.Font.Name = "Wingdings"
Target.Font.Underline = False
Target.Font.Size = 22
ActiveCell.Offset(-1, 0).Select
End If
If Target.Value = "Print" Then
Dim iCurRw As Integer
iCurRw = Target.Row + 1
Range("C" & iCurRw & ":Q" & (iCurRw + 25)).Select
Dim xCopies As Integer
On Error Resume Next
Do Until xCopies > 0
xCopies = InputBox("How many copies do you want?", "Copies", 1)
If xCopies = "" Then Exit Sub
Loop
On Error GoTo 0
Selection.PrintOut Copies:=xCopies
End If
If Target.Value = "Q1 Analysis" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Q2 Analysis" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Q3 Analysis" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Q4 Analysis" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Q1 Outturn" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Q2 Outturn" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Q3 Outturn" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Q4 Outturn" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Notes" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Baseline" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Prev. Outturn" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Target" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "National Comparator" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Value = "Statistical Neighbour" Then
ActiveCell.Select
Application.SendKeys "{F2}"
End If
If Target.Column > 1 Then Exit Sub
ActiveWindow.ScrollRow = ActiveCell.Row
End Sub
I apologise for the code above, even though it looks like repetition, it actually functions on a few select cells that I have selected for the user to click (i.e. if cell value = "Target" and the user clicks the cell, it will take them to the cell directly below (using a hyperlink) and allow them to edit the target.). This code was working prior to the additional code you gave me above to replace my "If Not Intersect..." code, however, although your code works beautifully for selecting my checkboxes, it now appears that when my labelled cells (including the cell named "Target") are selected, my code for "editing" the selected cell no longer works and also, the "Print" code from my previous post which you helped on (remember that one?) also fails to function.
I have probably done something really stupid and obvious that an expert like yourself can solve in an instant, but I can't find my way around it.
Thanks Luke
Alex
