+ Reply to Thread
Results 1 to 23 of 23

Letter populating 14,15,16th etc position

  1. #1
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Letter populating 14,15,16th etc position

    Hi,

    When I select an option (with the mouse) from my ComboBox the information populates a letter at the 14th position depending on choice. However, if I use the arrow down cursor it continues to populate the TextBox rather than remain at 14th position? It is possible to explore the options with out putting a letter in TextBox5 until I press return??





    Please Login or Register  to view this content.
    Attached Images Attached Images

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Letter populating 14,15,16th etc position

    bralew, my guess is you're using a Change event? try the AfterUpdate event instead
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    The full area is this:

    Please Login or Register  to view this content.
    AfterUpdate didn't seem to work

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Letter populating 14,15,16th etc position

    so is this a change event on the combobox? the name of the sub is not in your post.
    You say afterupdate did not work, in what sense? changes with mouse or scrolling still did the same, or did nothing? (of course AfterUpdate would have to replace the change event, not just be an addition, but I assume you know that already?)

    Can you provide a sample workbook?

  5. #5
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    ill load the file now, gimme two minutes

  6. #6
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    Here we are
    Attached Files Attached Files

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Letter populating 14,15,16th etc position

    Hmm... Thanks for the file... have you tried the Exit event for the combobox? I'm thinking that's the best option.
    Cant just replace "Change" with exit... need "Exit(ByVal Cancel As MSForms.ReturnBoolean)"

  8. #8
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    ok ill try now

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Letter populating 14,15,16th etc position

    My other thought is... I see you are checking the length is > 4, but is is specifically the 5th letter that needs to be affected every time? If so, remember that users behave unpredictably and may not follow perfect order, so you may want to replace the 5th letter in the string every time that it changes, in case they pick something and then change their choice, or even do things out of order and suddenly you find it affected the 6th or 7th letter instead.

    If you target the specific letter, then it changes every time the choice changes, and it wonn't matter if the letter changes every time the combo changes, since only the last change will count.

  10. #10
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    It's better, thank you, because it doesn't keep adding a different letter to TextBox5. However, when I change the brake option (in a scenario whereby a user changes they're mind) the letter preferably would update to reflect the new choice?

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Letter populating 14,15,16th etc position

    I'm not sure I was looking at the right form... there are so many...

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Letter populating 14,15,16th etc position

    Perhaps rather than replacing one letter each time, you should rebuild the entire string based on the same rules that populated it in the first place.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  13. #13
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    Sorry Lads, can we continue this tomorrow, I've got to leave the office. Thank you very much both, Ill reply at 8a.m.

  14. #14
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Letter populating 14,15,16th etc position

    I see a few issues... left(string,14) takes the left 14 characters, but if there are only 3 characters, you will still append at the 4th character... so you would need to change the code to check for length. But... if you check for length and it is not long enough (textbox5 i mean) then nothing would happen, so it still leaves you without a brake code (if we use brake type as the example of what changes). you may need to, every time any of the boxes change, build the combobox string from scratch to ensure everything goes in the right order. If you do go with that approach, then write one sub to build the textbox5 value, and call it from each of the change events.

    @xlnitwit, I tend to agree (as per my post, which I was writing while you posted, lol)

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Letter populating 14,15,16th etc position

    @Arkadi

    Great minds, and all that.

  16. #16
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    Morning Lads, gonna give it a try now

  17. #17
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    Very confused, I think this is beyond me! Lol

  18. #18
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Letter populating 14,15,16th etc position

    Let's try and break it down a bit. Where does the information that populates Textbox5 come from?

  19. #19
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    Is this what your asking xlnitwit?


    The information that populates textBox5 comes from a series of other Comboboxes. The aim is to construct a 20 digit code which is used to identify a product, in this case an axle. Thus:
    This is achieved very well and serves me well. It only becomes a problem if a less experienced user made a mistake or changed a detail.


    A - axle - AQB is populated by combobox1 - when the user types in capacity e.g. 1350kg
    Q - Square profile of beam -
    B - Beam size 66mm
    1 - CAPACITY kg
    3 -
    5 -
    0 -
    X - type of hub - This is populated by combobox7
    4 - No of studs in the hub - This is expressed as 4100 and is populated by combobox10
    1 - Pitch Circle Diameter (100mm) -
    0 -
    0 -
    C - BRAKE SIZE - combobox4
    A - TYPE OF BRAKE e.g. Standard, Hydraulic etc - combobox 3
    D - Drop Arm - combobox5
    F - Type Of Drop Arm - comcobox 6
    T - Customer Code - populated by combobox18
    Y - Customer Code - populated by combobox 18
    0 - Axle No - manually inserted
    1 - Axle No. - manually inserted

  20. #20
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Letter populating 14,15,16th etc position

    That's about what I expected.

    What I would suggest is that any manual entry at the end should use an additional separate textbox. You would then have one routine that calculates the final code based upon all the constituent controls- so basically the code you have now, but put into one separate routine that is either called by all of the individual controls (e.g. from their Change events), or called by something like a Save button when all the other parts are ready.

    Does that make sense?

  21. #21
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    Yeah, I've got it mate. It works lovely as it is but a save button makes sense which the user presses when they are confident all necessary parts are correctly selected? as opposed to updating on the fly?

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Letter populating 14,15,16th etc position

    That would be my preference generally. If you put the code into a separate routine anyway, called by the Save button, you can always add it back into the other controls' events at a later stage, if desired.

  23. #23
    Forum Contributor
    Join Date
    07-05-2016
    Location
    cardiff
    MS-Off Ver
    2010
    Posts
    787

    Re: Letter populating 14,15,16th etc position

    Thanks all

+ 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] Letter position not responding?
    By bralew in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-23-2017, 10:37 AM
  2. [SOLVED] How to extract capitalize letter text at certain position
    By harimin in forum Excel General
    Replies: 16
    Last Post: 02-09-2016, 11:21 PM
  3. Replies: 2
    Last Post: 11-07-2015, 10:56 PM
  4. [SOLVED] Help assigning number value to letter based on position within a sequence
    By alexkap in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-12-2015, 12:17 PM
  5. Position of Last Upper Case Letter found
    By vidyuthrajesh in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-04-2013, 11:51 AM
  6. Populating cell with a letter
    By Deanobey in forum Excel General
    Replies: 4
    Last Post: 04-15-2009, 06:53 AM
  7. Replies: 7
    Last Post: 02-19-2007, 12:17 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