+ Reply to Thread
Results 1 to 10 of 10

Input Box a range with keyboard within a Userform

Hybrid View

  1. #1
    Registered User
    Join Date
    12-30-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    36

    Input Box a range with keyboard within a Userform

    I have a macro that (after some data checks) calls a userform, which prompts a range input from the user with an input box. However, it seems the only way to enter data is by using the mouse, and the ctrl + shift + arrow combinations don't seem to work. I know I have used input boxes that allow for keyboard entry--for example, see below. Is there anything that will allow this functionality via a userform?

    Sub test()
        Set A = Application.InputBox(Prompt:="Input data",  Type:=8)
    End Sub

  2. #2
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Input Box a range with keyboard within a Userform

    Try this 2 step process.

    1. In the UserForm properties, find ShowModal, and change the property to false.
    2. After this code:
        Set A = Application.InputBox(Prompt:="Input data",  Type:=8)
    Add this line:
    ActiveSheet.Range("A1").Select
    Let me know if this works.

  3. #3
    Registered User
    Join Date
    12-30-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Input Box a range with keyboard within a Userform

    Thanks, but that didn't seem to work. Perhaps it's because I'm using a module to initiate the userform? For example, I have

    Sub Initiate()
    
    If Range("A1").value = "A" Then Call userform
    
    End Sub
    And then in the userform, I have the input box. Any other ideas?

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Input Box a range with keyboard within a Userform

    Do you have A declared as a range?

    On a side note, IMO its a bad idea to name variables strings or phrases that are used elsewhere. For instance A is a column, so naming a range variable A can be confusing.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Input Box a range with keyboard within a Userform

    Not exactly sure what you're trying to achieve, but for instance this will allow to use the mouse to select a range and put the range's address into a textbox on your userform:
    Private Sub CommandButton1_Click()
    Dim rngUserSelected As Range
    Set rngUserSelected = Application.InputBox(Prompt:="Input data", Type:=8)
    Me.TextBox1.Value = rngUserSelected.Address
    End Sub

  6. #6
    Registered User
    Join Date
    12-30-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Input Box a range with keyboard within a Userform

    Thanks, Solus. It is declared as a range. I'm just trying to make it easier to input the range- instead of scrolling down with the mouse for 3,000 rows, it's easier if I can just key in ctrl + shift + down. I am looking for something along the lines of what BigBas suggested, but that didn't work for me either. Any other suggestions are welcome.

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Input Box a range with keyboard within a Userform

    My mistake. I read the problem in your OP backwards.

  8. #8
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Input Box a range with keyboard within a Userform

    Open the attached file, click the command button to show the form, then click the command button to open the input box. Does it allow you to navigate the range with keyboard?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-30-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Input Box a range with keyboard within a Userform

    It does. Could it be that what I am trying to do applies to a chart? I am creating something that allows one to add custom data labels to a chart.

  10. #10
    Registered User
    Join Date
    12-30-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Input Box a range with keyboard within a Userform

    When I activated the chart, it worked.

    cht.Activate
    Thanks!

+ 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. fill input box range with keyboard selection
    By jfedorko in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2012, 10:22 AM
  2. Keyboard tab key - userform issue
    By tanktata in forum Excel General
    Replies: 3
    Last Post: 09-11-2011, 03:51 PM
  3. Activate userform check box with keyboard
    By Jogier505 in forum Excel General
    Replies: 4
    Last Post: 11-29-2009, 10:13 PM
  4. Disaple keyboard input in Combobox
    By nalfin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2009, 04:15 PM
  5. [SOLVED] Hide UserForm with Keyboard?
    By bw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2005, 06:05 PM

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