Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Thanked 0 Times in 0 Posts

    [Excel] Look up target values in a table > Allow editing > Generate a target string

    Hi all,

    After some rather clumsy first attempts which only worked out thanks to the great advice I got on this forum, I'm finally making some headway on VBA and feeling ready to take on something a bit more complex.

    My current project is an Excel macro that, given a list of comma-separated keywords, looks them up in an Excel sheet containing a list of keywords and their translations, and outputs the translated keyword list. I already have a basic prototype working, which looks like this:


    This works fine for its intended purpose, but I've got something a bit more ambitious in mind and I need some guidance to make sure I'm choosing the best approach to implement it.

    If you look at the above screenshot, you may notice that the macro outputs "_____" for any keywords not found in the list (e.g. "donate" in the above screenshot). What I'd like to do is to build some sort of dual-column interface, showing the English keywords on the left and the translated keywords on the right. The right column should be blank for any keywords not found on the list, so that the user can input the translation (and the new enty can eventually be added to the list). What I have in mind is something along the lines of the (very basic) mockup below:


    My questions are:

    - What would be the best way to store each keyword and its translation, esp. considering that a given keyword may have two or more translations? I've been reading up on the Dictionary class and it looks like it should do what I need (storing each original keyword as a key and its translation as its value). Am I missing a better way to handle this?

    - UI-wise, are there any built-in (or easily available) controls I could use to implement the dual-column form I have in mind? I've been toying with the ListView control, but I feel a bit out of my depth dealing with it, and I'm not sure how to populate it with my boring, string-type keywords.

    Thanks so much for any help you can provide!



  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts
    I'd use a listbox for your UI. It's easy to work with, and is a control that everyone will have, unlike a listview.

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    fparedes (2015-09-29)

Tags for this Thread

Posting Permissions

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