+ Reply to Thread
Results 1 to 21 of 21

Worksheet_BeforeDoubleClick 1004 error on worksheet selection

  1. #1
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Hi guy's,

    Hoping someone can help with a VBA problem I am having.

    For the last 2 weeks I try a to get this code working. I would like a popup userform with a search box that pops up with a bubble click in column b. But I do something wrong and get a 1004 error on the red line below I don't understand why.
    Does anyone know what i'm doing wrong? And is so kind to help me?

    Please Login or Register  to view this content.

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

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Sheet Specific Macros cannot affect changes on other sheets.
    Try Moving the code that affects other sheets to a normal macro module.


    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.

  3. #3
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Thanks for the fast replay!

    I try the code but get now a ByRef argument type mismatch error, on my prompt, I'm sorry for ask agoin, but do you know why?

    Please Login or Register  to view this content.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    See yellow banner above...Add sample file...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Hi,

    please see attached a sample, I skip al not relevant information, I don't see the same error anymore but the the userforum doesn't open.
    Attached Files Attached Files

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    What must happen when selection is made and okay is pressed...First part works...
    Perhaps you should explain in detail what you are trying to achieve...I cannot make out with your code....
    Attached Files Attached Files
    Last edited by sintek; 04-05-2020 at 09:49 AM.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    You have the wrong sheet name for the named range. Try
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Thanks for the fast aswers!
    Unfortunately the last code does not work.
    What I would like to achieve is that with the help of a combobox with search function opened by dubble click in the cell in colomn B.
    I like to search for a name in mylist and write it back to the same cellof the double click in colomn B.
    Lookslike a validation list with autocomplete. But then a bit more user friendly, because sometmies it may happen that new names will added.

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    With the sheet name changed to the correct sheet, as shown in post#7 your code does what you want.

  10. #10
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Why so complicated? Use a search box and a listbox.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Wow that's works great! thank you! this is where I looking for! one more time thanks a lot!

  12. #12
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    I apologize for asking another question. the method with the listbox works perfectly thanks a lot i'm very happy with it!
    However i would like to choose which list i want to search. so that I keep an overview of the search lists. I have been busy yesterday but haven't really got any further, would anyone want to help me again?

    My intention is that after clicking twice in colomn B on the relevant cell the userform will be opened, after this i would like to determine which list i can search by choosing the combobox. as in the example, mylist, first name or last name.
    Does some one know how I get this working?
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Without a combo box, just type what you are looking for
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Thank you for the quick response!
    It's not quite what I mean yet. Now I see the first name, when I search by last name I get the value of the first name in cell B.
    I would like to make a choice between mylist, last name and city to only put back a first name or last name or city in the cell in column B.
    Is this possible?

  15. #15
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Like this?
    Attached Files Attached Files

  16. #16
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  17. #17
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    I'm super happy with it! i am trying to understand what you did, but don't quite understand this piece of code, is it possible you give me a little explanation what are you doing in this part?
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    When I select the last name and search for a name, the slected value change to the slection of mylist. I don't see what's going wrong? do you know?

  19. #19
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Give this a try.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    01-08-2020
    Location
    amsterdam
    MS-Off Ver
    2010
    Posts
    45

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Thank you bakarman,it works!
    I learn a lot form you thanks again!

  21. #21
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Worksheet_BeforeDoubleClick 1004 error on worksheet selection

    Glad to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. “Selection.Insert Shift:=xlDown” gets Run-time error '1004'
    By watsonea4 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-02-2023, 07:07 AM
  2. [SOLVED] Error 1004 Select Method of Range Failed issue for Selection
    By rakotonirinas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2019, 09:51 AM
  3. Combobox selection / Delete row after saving in another sheet / Run-time error '1004'
    By Arvin.Amaro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-15-2015, 07:27 PM
  4. Copy selection, run-time error 1004 issue
    By nellaneb in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-19-2015, 09:55 AM
  5. [SOLVED] Intermittent Error 1004 on Selection.PasteSpecial
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 10-21-2012, 01:23 AM
  6. Runtime Error '1004' At Range Selection
    By Anubeon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2009, 10:47 AM
  7. Run Time Error 1004 - Selection too Large
    By sgedo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-01-2009, 10:47 PM

Tags for this Thread

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