Hoopsah
11-24-2010, 02:04 AM
Hi
I have a piece of code in the tab (View Code) for my spreadsheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G11" '<== change to suit
Dim pos As Long
On Error GoTo ws_exit
Application.EnableEvents = False
ActiveSheet.Unprotect
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
pos = Application.Match(.Value, Worksheets("Input Sheet").Columns(2), 0)
On Error GoTo 0
If pos > 0 Then
Me.Range("D14").Value = Worksheets("Input Sheet").Cells(pos, "A").Value
Me.Range("D17").Value = Worksheets("Input Sheet").Cells(pos, "C").Value
Me.Range("G17").Value = Worksheets("Input Sheet").Cells(pos, "D").Value
Me.Range("J17").Value = Worksheets("Input Sheet").Cells(pos, "E").Value
Me.Range("D20").Value = Worksheets("Input Sheet").Cells(pos, "F").Value
Me.Range("G20").Value = Worksheets("Input Sheet").Cells(pos, "G").Value
Me.Range("D22").Value = Worksheets("Input Sheet").Cells(pos, "H").Value
Me.Range("D26").Value = Worksheets("Input Sheet").Cells(pos, "I").Value
Me.Range("J14").Value = Worksheets("Input Sheet").Cells(pos, "J").Value
Me.Range("H26").Value = Worksheets("Input Sheet").Cells(pos, "K").Value
Me.Range("D28").Value = Worksheets("Input Sheet").Cells(pos, "L").Value
Me.Range("H28").Value = Worksheets("Input Sheet").Cells(pos, "M").Value
Else
Me.Range("D14,D17,G17,J17,D20,G20,D22,D26,H26,D28,H28,J14").Value = ""
End If
End With
End If
ActiveSheet.Protect
ws_exit:
Application.EnableEvents = True
End Sub
However, it is causing things to go funny.
Everything works when I input some data in cell G11 all of the cells are populated, but some of the cells have a drop down validation on it.
When I change one of these I am automatically thrown into another tab.
When I remove the ActiveSheet.Protect then this doesn't happen but then of course the sheet is no longer protected.
Any help appreciated
I have a piece of code in the tab (View Code) for my spreadsheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G11" '<== change to suit
Dim pos As Long
On Error GoTo ws_exit
Application.EnableEvents = False
ActiveSheet.Unprotect
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
pos = Application.Match(.Value, Worksheets("Input Sheet").Columns(2), 0)
On Error GoTo 0
If pos > 0 Then
Me.Range("D14").Value = Worksheets("Input Sheet").Cells(pos, "A").Value
Me.Range("D17").Value = Worksheets("Input Sheet").Cells(pos, "C").Value
Me.Range("G17").Value = Worksheets("Input Sheet").Cells(pos, "D").Value
Me.Range("J17").Value = Worksheets("Input Sheet").Cells(pos, "E").Value
Me.Range("D20").Value = Worksheets("Input Sheet").Cells(pos, "F").Value
Me.Range("G20").Value = Worksheets("Input Sheet").Cells(pos, "G").Value
Me.Range("D22").Value = Worksheets("Input Sheet").Cells(pos, "H").Value
Me.Range("D26").Value = Worksheets("Input Sheet").Cells(pos, "I").Value
Me.Range("J14").Value = Worksheets("Input Sheet").Cells(pos, "J").Value
Me.Range("H26").Value = Worksheets("Input Sheet").Cells(pos, "K").Value
Me.Range("D28").Value = Worksheets("Input Sheet").Cells(pos, "L").Value
Me.Range("H28").Value = Worksheets("Input Sheet").Cells(pos, "M").Value
Else
Me.Range("D14,D17,G17,J17,D20,G20,D22,D26,H26,D28,H28,J14").Value = ""
End If
End With
End If
ActiveSheet.Protect
ws_exit:
Application.EnableEvents = True
End Sub
However, it is causing things to go funny.
Everything works when I input some data in cell G11 all of the cells are populated, but some of the cells have a drop down validation on it.
When I change one of these I am automatically thrown into another tab.
When I remove the ActiveSheet.Protect then this doesn't happen but then of course the sheet is no longer protected.
Any help appreciated