Results 1 to 8 of 8

Thread: Update a Named Range from a Drop Down Box
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Update a Named Range from a Drop Down Box

    I have a sheet that contains a Data Validated drop down.

    There will be occasions where the drop down does not contain the required value.

    So, I want to give the user the ability to type the required value into the drop down and it will then add it to the named range.

    I have already set up a dynamic Range [called Supplier]

    Is this possible??

    Thanks

    Derek

  2. #2
    Board Regular
    Join Date
    Apr 2016
    Posts
    117
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update a Named Range from a Drop Down Box

    I found this link. https://www.extendoffice.com/documen...ual-entry.html Is this what you are looking for/

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,893
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Update a Named Range from a Drop Down Box

    Yes it is possible
    - what is the RefersTo formula of named range Supplier?
    - requires a few lines of very basic VBA
    Last edited by Yongle; Feb 7th, 2019 at 08:09 AM.

  4. #4
    New Member
    Join Date
    Oct 2015
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update a Named Range from a Drop Down Box

    Thanks but this is not quite what I'm looking for

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update a Named Range from a Drop Down Box

    Quote Originally Posted by Yongle View Post
    Yes it is possible
    - what is the RefersTo formula of named range Supplier?
    - requires a few lines of very basic VBA
    Each cell in column D is linked to a dynamic range called Supplier

    The formula in the Named Range is:

    =OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)
    Last edited by tigerdel; Feb 7th, 2019 at 08:41 AM.

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,893
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Update a Named Range from a Drop Down Box

    Standard DV message is replaced with VBA message when unauthorised value is entered
    - click OK to add value to named range
    - click Cancel to abort

    Test on a COPY of your workbook

    1. Amend Data Validation in any cell in column D EXCEPT D1 (assumed header)
    - on Settings tab, check the box to "Apply these settings to all other cells with the same settings"
    - on Error Alert tab, uncheck the box "Show Error Alert after invalid data is entered"
    (allows user to enter any value)

    2. Add code as instructed belowCode goes in sheet module of the sheet containing Data Validation
    right-click on sheet tab\ select View Code \ Paste code below into code window \ {ALT}{F11} to go back to Excel
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Row < 2 Then Exit Sub
        If Not Intersect(Target, Range("D:D")) Is Nothing Then
            If WorksheetFunction.CountIf([Supplier], Target) = 0 Then
               If MsgBox(Target & vbCr & ".......... will be added to named range", vbOKCancel) = vbOK Then
                    [Supplier].Offset([Supplier].Rows.Count).Resize(1) = Target
                End If
            End If
        End If
    End Sub
    3. Test
    Last edited by Yongle; Feb 7th, 2019 at 09:26 AM.

  7. #7
    New Member
    Join Date
    Oct 2015
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update a Named Range from a Drop Down Box

    Yongle

    That's perfect

    Thank you

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,893
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Update a Named Range from a Drop Down Box

    thanks for the feedback

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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