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

Status
Not open for further replies.

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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. 🤗
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,957
Messages
5,767,312
Members
425,404
Latest member
Bairkus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top