none
Old RefEdit - Keyboard Shortcuts dont work - Pivot Table wizard - Select data source - Name of control being used. RRS feed

  • Question

  • Hi,

    I am trying to develop some code where I need to get the used to select a range. I have tried using the Application.inputbox or the refEdit control. However, as clearly documented and discussed in multiple forums, they both don't support keyboard shortcuts to select the range. 

    However, when I used the Pivot table wizard or 'change data source' option for a pivot, there is a form which has a control and that works perfectly allowing the use of keyboard shortcuts as well.

    Can someone help identifying that control so that I can use it in my form? 

    (I am using the form to get users to update the range of a pivot table - so any other suggestions will also be welcome)

    regards,
    Friday, August 31, 2018 7:23 AM

Answers

  • Hi Peter.

    I did currently end up using the Application inputbox.

    However, to do that, I had to make my form a modeless form. Even when I hid the form before calling the inputbox, it was not allowing the usage of keyboard shortcuts unless I opened the form as a modeless one. However, my code can run into issues if a user browses into another workbook during the time my form is open.

    Hence, I am trying to get the reference for the updated refEdit control. Since its already there within the various forms used by the inbuilt Excel functions, so I guess we should also be able to use it.

    • Marked as answer by -Abhishek- Thursday, September 13, 2018 5:57 AM
    Monday, September 3, 2018 12:13 PM

All replies

  • What is your version of Office? This workaround applies to: Excel 2013/2010/2007.

    Please see, Cannot use keyboard shortcuts to select ranges in RefEdit control in Excel

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, August 31, 2018 3:34 PM
    Moderator
  • You say even the app Inputbox doesn't work with keyboard shortcuts. It should do, and can be configured in all other respects to look and work like a RefEdit but without various other issues associated with the RefEdit, particularly in older XL versions.
    Friday, August 31, 2018 4:34 PM
    Moderator
  • Did you resolved your issue and any updates for this? 

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, September 3, 2018 11:44 AM
    Moderator
  • Hi Peter.

    I did currently end up using the Application inputbox.

    However, to do that, I had to make my form a modeless form. Even when I hid the form before calling the inputbox, it was not allowing the usage of keyboard shortcuts unless I opened the form as a modeless one. However, my code can run into issues if a user browses into another workbook during the time my form is open.

    Hence, I am trying to get the reference for the updated refEdit control. Since its already there within the various forms used by the inbuilt Excel functions, so I guess we should also be able to use it.

    • Marked as answer by -Abhishek- Thursday, September 13, 2018 5:57 AM
    Monday, September 3, 2018 12:13 PM
  • I am using Excel 2016 - Also, the registry editing is not an option as I will be deploying my addin to a few people within my organisation and editing the registry for other users is not an option. And if MS knows this as an issue since Excel 2007, why didnt they correct the issue with the later versions of MS Excel? Seems to be a simple fix by the look of it.

    Monday, September 3, 2018 12:17 PM
  • I  forgot about use with a modal form though you hadn't mentioned that in your OP.

    What are the 'issues if a user browses to another workbook' with a modeless form, anything that can't be handled by trapping application events to let you know when and which workbook has been activated...?

     
    Monday, September 3, 2018 6:32 PM
    Moderator
  • Sorry about not mentioning the Modal form earlier. Missed it.

    The form I am working on gets the users to update and interact with pivots and their base data, and I want to restrict users to remain in the same workbook during the operation of the code. As suggested by you, I can run a lot of code to ensure that the users remain within the same workbook, but its not an i deal or elegant solution since there seems to exist a control which can be used without much trouble (I guess)

    And although I searched, I couldnt find information on the web about the refEdit controls being used within the inbuilt Excel tools which seem to be so simply and perfectly executed. Hence my question here, hoping someone would know more about the same so that I can increase my knowledge since I plan to develop other such tools.

    Tuesday, September 4, 2018 4:23 AM
  • I can understand your frustration and why you're looking for what appears to work with the pivot table wizard.

    Or does it, even if you could get it? I just tried showing the PT wizard on a modal form and the range picker tool, whatever it is, doesn't seem to offer any additional keyboard functionality compared to an inputbox. Only if shown on a modeless form do the keyboard shortcuts work, as they also do with an inputbox.

    This won't solve the keyboard problem with a modal form but FYI a wrapper to handle getting the range with an inputbox, in this pair of workarounds to solve a different bug in pre-2007; see 'Method-2' 

    https://www.jkp-ads.com/articles/SelectARange.asp

    Tuesday, September 4, 2018 8:14 AM
    Moderator
  • Thanks a lot Peter. I had already looked at the site you have mentioned but it seemed a lot of code and trouble to go to to get the user to select a range, hence I asked my question in this forum. Guess there isn't a simple answer to the query. 

    However, now you have gotten me interested in how you "Showed the PT wizard on a modal form?" Can you share the code here or did I miss something obvious?

    Wednesday, September 12, 2018 5:15 AM
  • how you "Showed the PT wizard on a modal form?" 

    Application.Dialogs(xlDialogPivotTableWizard).Show

    Wednesday, September 12, 2018 10:42 AM
    Moderator
  • Thanks peter... Learnt something new - Lets see when and how I may be able to use it. :)

    I think we can close the discussion now.

    Thursday, September 13, 2018 5:58 AM