Solved: Drop Down Data Validation - Quirks

[Hoopsah]

Hi

I have a piece of code in the tab (View Code) for my spreadsheet.

[VBA]
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
[/VBA]

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

[Bob Phillips]

Sounds very odd. Can you post the workbook?

[Hoopsah]

Hi Bob

I have attached a copy.

If you go to the tab called Main_Input_Sheet and enter reference 5454

All the correct cells populate,

Then if you try the validation celss J17 (Region), D26 (Current Status) or H26 (Complaint Cause) and change one of them you get thrown into the menu screen.

I have put the protection off then on so that cell J14 gets populated but then the user cannot amend it.

When I remove the protection everything works fine though

(P.S. Sorry about the delay - We have had 2 fire alarms this morning!!!)

[Bob Phillips]

Gerry,

I cannot reproduce the problem.

I have tried it with Excel 2007 and Excel 2000, and both behave properly.

[Hoopsah]

That is even more worrying!!!

If I take out the ActiveSheet.Protect and it works ok for me, do you know the best way to try and unprotect/Protect the sheet?

[Bob Phillips]

I would have said that the way that you are doing it is the best way. I have no idea why it behaves erratically for you. Have you tried the age old solution of rebooting?

[Hoopsah]

Yeah, Iv'e tried that. I have even tried it on someone elses computer and it done the same thing.

Back to the drawing board I think!!

[shrivallabha]

Not that it is going to help I think. But I did not get any hiccups.

[Hoopsah]

Don't know why but,

I deleted the tab called Menu 1 and then re-created it again, still calling it Menu 1 and now it works perfectly.

Don't know why it was doing that at all but it is now resolved.

Cheers guys, I would have been looking at code until I was blue in the face, the fact that it worked on your machines had me trying something different.