+ Reply to Thread
Results 1 to 4 of 4

Run-time error 1004 when coding in-cell dropdown

Hybrid View

excell_rookie Run-time error 1004 when... 09-30-2018, 07:41 PM
Norie Re: Run-time error 1004 when... 09-30-2018, 08:53 PM
excell_rookie Re: Run-time error 1004 when... 10-01-2018, 03:29 AM
Norie Re: Run-time error 1004 when... 10-01-2018, 04:36 AM
  1. #1
    Registered User
    Join Date
    09-27-2018
    Location
    Gothenburg, Sweden
    MS-Off Ver
    excel2016
    Posts
    17

    Run-time error 1004 when coding in-cell dropdown

    Dear forum,

    I´m trying to code a in cell dropdown list.

    First I recorded a macro, and after that, I´m trying to ustomize the code for VBA
    Sub inCell()
    Dim ws2 As Worksheet, ws3 As Worksheet
    Dim rngList As Range, lr2 As Long
    
    Set ws2 = Sheet2
    Set ws3 = Sheet3
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    Set rngList = ws2.Range(ws2.Cells(2, 5), ws2.Cells(lr2, 5))
    
    'E2 on Sheet 2 I want insert the drop down list
    With ws3.Cells(2, 5).Validation
        .Delete
    'replace "=A1:A6" with the range the data is in.
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=rngList
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    
    
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    End Sub
    when I run debug (F8) this code
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=rngList
    gives
    Run-time error 1004
    Application-defined or object defined error
    I would appreciate your help solving this problem
    Thanks in advance

    Regards
    Peter

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Run-time error 1004 when coding in-cell dropdown

    Peter

    Should you not be using the address of rngList preceded by '=' when adding the data validation?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    09-27-2018
    Location
    Gothenburg, Sweden
    MS-Off Ver
    excel2016
    Posts
    17

    Re: Run-time error 1004 when coding in-cell dropdown

    Thanks for your answer Norie,
    the right way is
    Formula1:="=rngList"

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Run-time error 1004 when coding in-cell dropdown

    Peter

    That would only work if you have a named range called 'rngList'.
    Last edited by Norie; 10-01-2018 at 05:14 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Runtime error 1004 VBA Coding assistance
    By Sintek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-11-2016, 02:31 AM
  2. Prevent editing more than one cell at a time - get error 1004
    By tv69 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-24-2014, 10:00 AM
  3. [SOLVED] Coding Issue Error 1004
    By pmensch in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-01-2013, 12:53 AM
  4. Run-Time Error 1004 when changing cell fill to None via macro
    By Gara in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2012, 02:53 PM
  5. [SOLVED] run time error 1004, cell or chart is protected.
    By ndtsteve in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-09-2012, 12:42 PM
  6. changing cell triggers Run-time error 1004
    By Langston in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2009, 11:36 AM
  7. Run-time error 1004: Too many different cell formats
    By Randy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2006, 10:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1