Posts by MattRNR

    Re: Simple VBA adding only one feild from a multifeild dataform in the wrong place


    G'day guys


    Bryce, I feel like an absolute idiot for not working that one out, cheers for pointing that out, working perfectly now.


    Roy, that does look substantially more functional, I'll have a bit more of a play around with that on my next version. I'll make a point of avoiding merged cells as well


    Clearly I've got a lot to sink my teeth into here


    Cheers again guys


    Regards


    MattRNR


    G’day Ozgrid



    I’m heading on holiday next year and I thought I’d take the opportunity to learn how to build data entry forms while I'm doing the planning (quite unnecessarily but if I can kill two birds with one stone why not).



    I was hoping someone might have some idea as to what I’m doing wrong.



    I first successfully replicated the process and userform detailed in this youtube video: https://www.youtube.com/watch?…T7cEe71b4J8&v=5PN7lWJSobQ, augmenting it only by adding a button to launch the form.



    When I attempted to modify the code for my own purpose however I ran into problems (see attached)



    I enter data in all fields and click ‘add’ however only the last field appears (notes) and it appears in the wrong place (column 1)



    The other thing I was wondering (also looking to kill two birds with one stone, although it may well be the same problem) can anyone tell me how to change the starting row and column where the data ends up? This is more an aesthetic issue as I usually like to structure my databases in the format shown in sheet 2



    I’m certain I’ve overlooked something stupidly simple but I’m buggered if I can work it out, I’m still very much a novice at VBA and this is my first data entry form.



    Any advice on either of these issues would be greatly appreciated.



    Cheers



    MattRNR


    Re: Preventing Combo Box drop down list from activating when not in use


    G’day Rory


    My apologies for taking so long to get back to you, I've been working out bush and haven't had reliable internet.


    This looks perfect; I can’t begin to tell you how much wasted time and hassle that drop down caused me before you gave me this solution.



    Really appreciate you taking the time to help me out on this last little complication as well.



    Cheers for everything mate



    Regards


    MattRNR

    Re: Preventing Combo Box drop down list from activating when not in use


    G’day Rory


    I’m not sure if you remember this post, it has been some time, however you came up with a solution to a problem that had been plaguing me for months, concerning a google style dropdown search suggestions setup based on an article on trump excel.



    Your solution worked flawlessly on the original version (attached as EV1) of the spreadsheet I am working with however neither I nor a very knowledgeable bloke I’ve been working with from the Netherlands have been able to work out how to incorporate it into the full version of the spreadsheet (attached as EV3).



    I was hoping you might be able to show me how to replace the ‘Search Filter’ button on EV3's ‘Search Sheet’, with the google style dropdown search suggestions box your method made work in the original version (EV1)?



    I can’t for the life of me work out how to incorporate this method into the new version successfully.



    Though I am very novice to this stuff.



    Would really appreciate any help you could provide



    Cheers mate



    MattRNR

    Files

    • EV3.xlsm

      (214.25 kB, downloaded 46 times, last: )
    • EV1.xlsm

      (92.84 kB, downloaded 53 times, last: )

    Re: Preventing Combo Box drop down list from activating when not in use


    G’day Guys



    Rory: This works perfectly Rory! It’s relieving, albeit a touch frustrating, that after all this time struggling with this it came down to modifying one little bit of code. Are you able to explain how/why this works? I’m quite a novice with VBA so I’m struggling to wrap my head around what is probably quite clear to your eye.



    Batman: Hey Batman, I couldn’t seem to enter the ‘rngData’ or ‘rngIndexData’ range names into the Fill List Range property of the Combo Box. The entry just keeps clearing when I hit enter. I’m assuming this might be because it is on another sheet.


    It certainly seems odd that it would work fine on another workbook but not on this one, if I run into trouble with Rory’s solution the only other approach I could take to my mind would be to reconstruct the spreadsheet from scratch which I may do anyway to try to pinpoint at which step it all falls apart.



    Cheers Guys



    Regards



    MattRNR

    Re: Preventing Combo Box drop down list from activating when not in use


    G’day again guys

    Sorry for the slow reply, busy season

    I’ve attempted both of these solutions

    Batman: I have replaced the formulas in H3 and H4 to eliminate the OFFSET function however the dropdown list issue remains unchanged (see attached), could I have inadvertedly replaced one volatile function with another?

    OzMVP: your solution resolved the issue of the list dropping down, but no longer gives me a subset of the values as I’m typing (see attached). Am I doing something wrong?

    Any ideas?

    Regards
    MattRNR

    G'day OzGrid


    I’ve been having a great deal of difficulty with a method I found online for generating a search bar with suggestions (like a google search) within an excel spreadsheet. I’m hoping the problem lies with the underlying VBA and also that someone on here might know something that can help.



    The method is described here; http://trumpexcel.com/2013/10/…-with-search-suggestions/


    I’m extremely keen to use this method not only on the project I’m working on at present but also on a number of others I’m looking to work on down the track.



    I have integrated the method into my current project (attached) but the combo box seems to be launching the drop down list sporadically when I use other parts of the spreadsheet.



    More specifically if I search a product using the search bar (located at cell H5 of the Search sheet), select one of the products in the drop down list and then do anything elsewhere in the sheet (enter something into another cell, select a checkbox, click a button, etc.), the dropdown list will dropdown again even through I’m not typing in the combo box.



    Ideally the dropdown list of options will only drop down when someone is typing in the combo box and has not yet selected an item. Once an item is selected from the dropdown list it should not dropdown again until that item is cleared from the dropdown box and a new search is made.



    It should function as in the GIF at the top of the original method



    To rectify this issue I've tried the following:



    • I’ve been unable to get in contact with the original designer
    • I’ve been discussing it with a very knowledgeable forum moderator from the excel forum, alas we’re both stumped.
    • I’ve tried the spreadsheet on numerous computers and with several versions of excel all to no avail.I’ve asked on the excel help forum also to no avail: http://www.excelforum.com/exce…dropdown-misbehaving.html
    • I found a workaround (or rather the forum moderator found a work around) however it is behaving similarly and isn’t as simple as the original method.


    Has anyone any idea why it might be doing this? Could it perhaps be clashing with another code or have something to do with the version of excel I'm using (2010)?



    The only solution I can think of at this point is to add code of some kind to dismiss the dropdown list automatically whenever it drops down and is not active but that seems like a sloppy workaround rather than a permanent solution (and I have no idea whether or not it’s even possible).

    Would appreciate any insight anyone would have.


    Regards


    MattRNR


    P.S. The helper columns required for the method can be found on the ‘Product Database’ sheet flanking the 'Entry Number' column.