1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel (2010) Data Validation using VBA not working as expected

Discussion in 'Business Applications' started by Keebellah, May 4, 2015.

Thread Status:
Not open for further replies.
  1. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,
    I'm working on an Excel project of mine and wanted to use VBA to create a Data Validation List in a merged cell.
    The strange thing is that using VBA for some reason the list does not show as a drop down list but as a long string
    The string is the 26 letters of the alphabet
    Since I was doubting my own VBA code I decided to record a macro and added all the 26 letters of the alphabet
    The resulting code:
    Code:
    Sub DVApply()
    ' DVApply Macro
        Sheets("DataForm").Select
       ActiveSheet.Unprotect
        Range("B2:C2").Select
        ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Range("D5").Select
    End Sub
    
    Now here is the thing. The moment I record the macro the list shows up nicely as a dropdown list, but if I run the macro the new list will show up again a a single string and not in a list unless I open the Data Validation Dialog and click Apply and close it.
    I've attached a pdf file with some screenshots and comments.
    Can anyone point out to me where it is going wrong?

    I've been at it since yesterday but I give up ... (for now :))
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Whatever the problem is I wrote another routine and it works now.
    I've thrown every range at it and it (still) works.
    I'ts still strange why even the recorded macro does not work when you run it again afterwards.
    Let it be, one more of Microsoft's deep secrets :)
    At least this post had 15 views, but I'm marking it solved.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1147701

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice