Issue with Data Validation List (does not display) after VBA runs

[Insert_Key]

Hi all ?

I am seeking some help with a Data Validation dropdown that is not functioning properly after VBA does its thing.

An answer to a question I posted recently to resize some rows and clear a cell (and a little bit more) was provided by steve the fish, and his solution works brilliantly aside from this issue with a dropdown breaking. The original post has fallen off the front page and not getting viewed; I’m not confident I’ll get an answer from it so I’ve decided to mark it as solved and seek a solution to the issue here.

Here is the VBA Code that was supplied / I am using:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 Then
    Select Case Target.Address(0, 0)
        Case "C2"
            Application.EnableEvents = False
            Range("C14") = ""
            Rows("3:13").EntireRow.AutoFit
            Application.EnableEvents = True
        Case "C14"
            Rows("15:26").EntireRow.AutoFit
    End Select
End If

End Sub

Each time the worksheet is closed and reopened, the Data Validation in C14 seems 'inactive' and the dropdown list does not present.

The dropdown list icon/arrow is present and when I click on it there is the briefest flicker of what I think is a progress wheel but no visible change on the sheet. The list box doesn't seem to appear at all, meaning that the user cannot make their second selection and the information presented by the table is incomplete.

Selecting the cell and opening the Data Validation dialogue shows that the formula applied to it is still in place, and hitting enter or selecting OK 're-enables' the dropdown list. This only needs doing once after opening the file; the dropdown will continue to work fine until the next time the file is closed and reopened.

Any idea on what the issue is and how I address it? I'm completely inexperienced with VBA so can't think through a solution for myself - will be very grateful for any asssistance!

Thanks in advance for your help. ?

 

[Fluff]

Duplicate to: Clear cell and resize all row heights to fit cell contents with VBA (total noob)

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.