+ Reply to Thread
Results 1 to 2 of 2

ComboBox filter - error when the selected value does not exist

  1. #1
    Registered User
    Join Date
    11-22-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    23

    ComboBox filter - error when the selected value does not exist

    I have a combo box with a list of ID's that I use as a filter to copy the associated values from another sheet ("Extração") where I have the ID's and more information about them on several columns.

    Here follows the code I used and it works fine.

    The problem is that I may have values on my combo box list that sometimes are not on the other sheet, so when that happens I select the value from the list, run the macro, and it gives back an error.

    What I would like the macro to do is to look for the value selected on the combo box on the other sheet and if it's not there to give back a messagem saying that there are no values for that selection and stop running the rest of the macro.

    Is this possible?

    [CODE]

    Dim src As Worksheet
    Dim tgt As Worksheet

    Dim filterRange As Range
    Dim copyRange As Range
    Dim copyRange2 As Range
    Dim copyRange3 As Range
    Dim cutRange As Range
    Dim removeRange As Range



    Dim Lastrow As Long


    Set src = ThisWorkbook.Sheets("Extração")
    Set tgt = ThisWorkbook.Sheets("Top")



    tgt.AutoFilterMode = False

    Lastrow = tgt.Range("B" & tgt.Rows.Count).End(xlUp).Row

    Set cutRange = tgt.Range("B20:D" & Lastrow)

    cutRange.ClearContents


    src.AutoFilterMode = False

    Lastrow = src.Range("B" & src.Rows.Count).End(xlUp).Row


    Set filterRange = src.Range("B9:N" & Lastrow)

    Set copyRange = src.Range("E10:E" & Lastrow)

    Set copyRange2 = src.Range("J10:J" & Lastrow)

    Set copyRange3 = src.Range("N10:N" & Lastrow)


    filterRange.AutoFilter Field:=1, Criteria1:=Me.ComboBox1.Value




    copyRange.SpecialCells(xlCellTypeVisible).Copy
    tgt.Range("B20").PasteSpecial xlPasteValues


    copyRange2.SpecialCells(xlCellTypeVisible).Copy
    tgt.Range("C20").PasteSpecial xlPasteValues


    copyRange3.SpecialCells(xlCellTypeVisible).Copy
    tgt.Range("D20").PasteSpecial xlPasteValues



    filterRange.AutoFilter Field:=1


    [CODE]

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: ComboBox filter - error when the selected value does not exist

    Turn off error handling

    Please Login or Register  to view this content.
    Check to see if your search was successful

    Turnon error handling

    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

+ 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] Check if a combobox exist in a cell or not
    By polooop in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2014, 06:06 AM
  2. [SOLVED] selected combobox will automatic update in the list to another combobox
    By aimjhun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2014, 07:22 AM
  3. Macro to filter data to search terms selected from ComboBox (Form Control)
    By JasonRay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2013, 05:08 PM
  4. Replies: 3
    Last Post: 04-02-2013, 06:02 PM
  5. I need a ComboBox to return another Combobox if a specific answer is selected
    By concatch in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-22-2012, 03:43 AM
  6. Combobox: How to determine the location of the selected item if combobox has rowsource
    By ahsanzafar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2012, 03:24 PM
  7. VBA - Check if selected worksheets exist
    By Lucifix in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2009, 05:04 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