Data validation dropdown arrow moving

[TheJay]

Hello there, I've come across a problem with my dropdown list. The arrow moves from the normal place by the side of the data validation list to several cells lower depending on the selection made. Has anyone else come across this before? I need to stop it from happening.

 

[kevin9999]

This sometimes happens when the data validation is inadvertently copied to more cells. When you select the cell with the second/unwanted arrow, and then select Data/Data Validation, does it say "any value" (it should) or does it indicate some condition for that cell?

 

[jaeiow]

I believe this is a bug that could be fixed by switching sheets and switching back. It's similar to when Excel get's laggy, and you click one place and it selects somewhere else.

 

[TheJay]

I'm not able to isolate the issue properly, because it only seems to happen when I lock the worksheet and a number of rows are hidden (so I am not sure which one the arrow is moving to). The worksheet has two dropdown lists. When the one shown is selected and then the second has a value chosen, it seems that's when it starts happening. The first dropdown (pictured previously) has four choices and the arrow only moves when user selects the first choice. Any other option and the arrow moves back to the correct place?

 

[TheJay]

This is causing me a problem, making it difficult for people to use the worksheet. Has anyone got a solution?

 

[TheJay]

The problem seems to disappear if I unhide cells and reappear when I show them again. That's a problem though, as I need to hide the relevant rows.

 

[TheJay]

I've attached a sample worksheet.

Sample.xlsm

drive.google.com

Click the green cell and the the arrow and choose "Example" from the validation drop down list. Then hit the reset button and change the green cell by choosing "Formatting" and you will see the problem occur. Change between "Formatting" and "Example" and it keeps happening when choosing "Formatting".

 

[jaeiow]

Thats some impressive stuff with how your workbook is like an app. I turned gridlines on and it seems to have fixed it.

 

[Peter_SSs]

Try just adding this line near the end of your Worksheet_Change code.

            If Target = "Formatting" Then formatting.EntireRow.Hidden = False
            If Target = "Example" Then example.EntireRow.Hidden = False
            If Target = "Instructions" Then instructions.EntireRow.Hidden = False
            If Target = "Help" Then help.EntireRow.Hidden = False
        End With
        Range("B2").Select
    End If
End Sub

 

[TheJay]

Thats some impressive stuff with how your workbook is like an app. I turned gridlines on and it seems to have fixed it.

It really helps with user friendliness to prevent users from needing to scroll and having as little information displayed at a time.

Try just adding this line near the end of your Worksheet_Change code.

            If Target = "Formatting" Then formatting.EntireRow.Hidden = False
            If Target = "Example" Then example.EntireRow.Hidden = False
            If Target = "Instructions" Then instructions.EntireRow.Hidden = False
            If Target = "Help" Then help.EntireRow.Hidden = False
        End With
        Range("B2").Select
    End If
End Sub

Thank you, I will test this and come back. Out of interest, what are your thoughts behind this being a fix?