Page 2 of 2 FirstFirst 1 2
Results 11 to 19 of 19

Thread: Dynamically show and hide optional controls in a userform.

  1. #11
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Forgot to mention that another reason I was looking for a non-cell approach is that I wanted users to be able to type multiple lines as input..for instance in the 'Brief Overview' field. Excel cells would require you to use ALT-ENTER to enter a new line within the cell, this, which users may not know. Or they might know, but (like me) not remember until they've pushed ENTER.

  2. #12
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    I managed to overcome the tabindex problem using the ActiveX-based form, but it took a heck of a lot of code, and I get all sorts of horrible flashing despite the fact that I've turned screenupdating off during the code application. Plus the form is sometimes increadibly slow in Excel 2010, which we are moving to soon (currently using 2002.)

    So I recreated the form using a worksheet-based approach as suggested above . Managed to escape the grid by making column cells very narrow and then merging particular blocks of cells together to get different sized input blocks.

    The only problem is that this stuffs up the tabbing. Here's what's going on:
    1. Everything works fine until I hit a merged block of cells with the range M22:AJ27 ("Address")
    2. When I push tab, then I get taken to the next input box which happens to be one row deep in the range AT22:BF22 ("Phone"), which is fine
    2. When I push tab again, then I get taken to the next input box which also happens to be one row deep, in the range BL22:BR22 ("Ext")
    3. But when I push the tab this time, I get taken back to the top of that multi-row merged block in the range M22:AJ27 again ("Address")

    So I go round in circles.

    I had a little experiment with Application.OnKey in the hope that I could get it to go past the "Address" box any time that Tab was pushed from the "Ext" box, but couldn't work out how to still allow tab to act like tab in the cases that it is anywhere else. That is, how the heck do I get excel to tab with VBA?
    Attached Files Attached Files

  3. #13
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,325
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Jeff,

    I haven't actually looked at your file, but based off your description... can you work with the Worksheet_SelectionChange event? Rather than trying to use onKey to capture the key as it's pressed, use the SelectionChange to react to where you end up and move the user to where you want them?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #14
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Of course! Why the heck didn't I think of that? Your comment has also helped me realize that tabbing is only part of the picture anyway...I also need to make the next cell to the right be selected on enter ( Application.MoveAfterReturnDirection = xlToRight ) rather than down (the default movement)

    Thanks Ken.

  5. #15
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,325
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    LOL! Sometimes we get so focussed we lose site of other routes to the same goal. Been there many times myself. <grin>
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #16
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Bob...not sure if you are still monitoring this thread, but wanted to say that I just found your dynamic dependent dropdowns example at
    http://www.xldynamic.com/source/xld.Dropdowns.html
    which is a real time-saver for my now workbook-based approach. THe data validation example is just what the doctor ordered. Tomorrow I've got to make a small change so that if there is only one possible choice in the 2nd dropdown then that choice is selected automatically. But this has saved me significant time. Next time you're in Wellington New Zealand I owe you one (if not several) of our fine Tuatara Brewery beers as recompense.




  7. #17
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,622
    Articles
    0
    Excel Version
    O365
    Jeffrey,

    You should be careful what you promise. I am coming to New Zealand in October. It is primarily to visit my daughter, and tour the South Island, but I hope to travel up to Auckland in November by train, which would entail stopping over in Wellington for a night.

  8. #18
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Awesome...New Zealand in desperate need of hard currency! Looks like you're taking a good amount of time to get around...fantastic. I look forward to shouting you those beers, and showing you round if you need it.

    Here's two of my favourite places in the South Island:

    Make sure you include Wharariki Beach in Golden Bay (Right at the North West tip of the South Island)...it is one of the most beautiful places in New Zealand that I've been. Lovely limestone islands with archways big enough to fly a small plane through, awesome caves, amazing wind sculpted sand, and a seal colony to boot. Plus hardly anyone there. Check out http://www.goldenbaynz.co.nz/vg_wharariki.html Another awesome place easy to overlook is the Catlins, which is on the South East coast. There's a great tidy little campground with comfy cabins we stayed at in a place called Surat Bay, plus a clean backpackers right next door if that's all you need. You'll have sealions and penguins galore on the beach right outside (in fact the sealions often sneak up behind you). Check out http://www.visit-newzealand.co.nz/ca...lidaypark.html or http://www.newhavenholiday.com/index.html (note it is much more interesting than it looks on these sites). Also in the Catlins is the stunning view from Nugget Point lighthouse (take a bottle of bubbly, some nice cheese and crackers, and go at dusk), and many other great nooks and crannys.

    I can also recommend some amazing places to visit in Northland (north of Auckland) where I come from originally. Please let me know if you decide to poke your nose up there, and I'll give you a suggested itineary. You won't regret visiting there. I'm at weir dot jeff at gmail dot com.

    Cheers
    Jeff

  9. #19
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Bob: just noticed a strange behavior in excel 2002 (which is what they give me at work) with your dropdowns code...running it seems to trigger 198 refreshes. If i type =rand() into a cell, then activate the first dropdown, then I see the results of the RAND function cycle though those 198 refreshes. And if I add the line debug.print "test" just before END SUB of Private Sub Dropdown_Change(ByVal Target As Range) then I see that "test" gets printed in the immediate window 198 times.

    Any idea what is causing this, and how I might circumvent it? I attach the workbook.
    Attached Files Attached Files

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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