Results 1 to 2 of 2
2015-09-25, 04:47 #1
- Join Date
- Jun 2015
- Thanked 0 Times in 0 Posts
[Excel] Look up target values in a table > Allow editing > Generate a target string
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!
2015-09-28, 03:20 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 187 Times in 173 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.Regards,
Microsoft MVP - Excel
The Following User Says Thank You to rory For This Useful Post: