[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 numeric keypad within a userform

Posted on 2012-08-15
27
Medium Priority
?
2,709 Views
Last Modified: 2012-08-29
Hello experts,

I am trying to build an excel userform to emulate a numeric keyboard, so that if I click the tab key as seen on the soft keyboard, the focus will shift to the next txtbox, and I can proceed to enter the next amount.  I can’t get the tab key to work, or have figured out how to enter the next value in the next txtbox.  The attached file shows what I’ve gotten so far, but need help finishing this up.

Thanks,
Ray
TestKeypad.xlsm
0
Comment
Question by:biker9
  • 14
  • 12
27 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38299676
The problem you have, and will have to overcome, is that once you click the tab button it becomes the ActiveControl and so anything you do (tab etc) will behave as if it was pressed while on the tab button.
I am certain there is a way round this, but it is not simple.
If this is on a touch screen, is it enough to have the operator just touch the next TextBox as they wish to move to it and ignore Tab?
Or possibly a small button between the TextBoxes to shift Focus with a '>' icon?
0
 

Author Comment

by:biker9
ID: 38300690
Thanks for your reply Barman,

I was hoping for both solutions actually, so what needs to be changed, ignoring the tab, to get it to work with a touchscreen?

And to emulate a tab key, I was thinking code would be a line or two that would move setfocus  to the next sequential tab index value? If you think it will require more than a few lines, I can perhaps post the query on a developer site and see if someone would like to tackle it as a project.

Thanks again,
Ray
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38300765
I've attached a modified userform that works.
Marty.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:biker9
ID: 38300867
The tab works perfectly Marty,
what do i need to change in the "process a number" portion of the code so the a value can be entered into the text box that the setfocus is upon?
Thanks,
Ray
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38300880
I'm sorry but I don't understand. Can you explain again please?
0
 

Author Comment

by:biker9
ID: 38300936
The tab key works perfectly when I click on it then enter a value using my computer's numeric keypad, click and then enter a subsequent value etc.

However when using the buttons on the userform, the setfocus doesn't shift to the next txt box due I suspect to the code in the  " Sub process_a_number ".  

Ray
newbie vba
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38301000
Try this version.
Marty.xlsm
0
 

Author Comment

by:biker9
ID: 38301665
I think its almost there, when i only test the tab function without entering any values from the softkeyboard or the computer's numeric pad, the setfocus moves from the Startage box through all the data entry boxes, but then it continues onto the number buttons.

If I enter a value with the softkeyboard and then tab, the setfocus sometimes remains with the Startage and will not move upon tabbing with the softkey, sometimes if enter a value such as 77 with a softkey, then the setfocus appears to move to the numeric pad number button 8, i'm guessing it's something to do with the setfocus not limited to cycling within the 6 data entry boxes.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38301812
In your post ID: 38300867 you said "The tab works perfectly" so I assumed you wanted to tab between all the controls on the userform, but now you say "i'm guessing [the problem has] to do with the setfocus not limited to cycling within the 6 data entry boxes, so do you want to limit the userform Tab key to the textboxes or allow it to be used on all the controls?
0
 

Author Comment

by:biker9
ID: 38301844
Limited to the 6 data entry boxes would be best, sorry should have been more specific.
Thanks
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38301962
Okay, try this.
Marty.xlsm
0
 

Author Closing Comment

by:biker9
ID: 38302445
Marty this works great, I'm very appreciative of the time you have spent on this, much above the call of duty!
Ray
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38302488
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38303048
As a thank you for the points I've attached a version that allows you to go backward through the textboxes if the Alt key is pressed while pressing the Tab button.
Marty.xlsm
0
 

Author Comment

by:biker9
ID: 38305030
Thanks Marty,
I tried the alt-tab and a small rectangular windows pops up which displays and cycles through any open programs I have on my desktop. Could be a Windows 7 thing that protects the key combo. I appreciate the thought tho.

I've just posted a related question to the userform & on-screen keyboards, hopefully you can help with that one also.
Ray
0
 

Author Comment

by:biker9
ID: 38342097
Marty,
a bit late in mentioning this, so don't know if I should re-open the question or post a new one, of if this comment will even get through to you, but i just noticed that upon entering a value in the index field from the userform keypad, that it doesn't format the result properly (not dividing the value by 100 and formatting into a percentage upon hitting the userform tab button, whereas if the value is entered via hardware keyboard and either the Tab or Enter keys are used, the value is properly entered. Please advise, Thanks,
Ray
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38342105
I'll take a look at it.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38342161
Just to make sure we're looking at the same code, please attach your current workbook.

Also please tell me exactly what should happen step by step. For example

1.    Enter 21 in (top) 'Age'
2.    Press userform-tab key
3.    Enter 100 in (top) 'Amount'
4.    Enter …

And make sure to tell me what should be formatted and when it should happen.
0
 

Author Comment

by:biker9
ID: 38342258
Hey Marty,

1.    Enter with userform keys 21 in (top) 'Age'
2.    Press userform-tab key and tab to (top) 'Months'
3.    Press userform-tab key and tab to (top) 'Amount'
4.    Enter 10000 in (top) 'Amount'
5.    Press userform-tab key and tab (bottom) 'Age'
6.    Enter with userform keys  31 in (bottom) 'Age'
7.    Press userform-tab key and tab to (bottom) 'Months'
8.    Press userform-tab key and tab to 'Index by'

9.    Enter with userform keys 25

10.  Either a) Press userform-tab key and tab to (top) 'Age'
           or   b) Press userform-'Okay' key

11.  The value in the userform 'Index by' should now = 2.50%

That would fix it.
Ray

ps. do you do contract work ie: rentacoder or similar??
Marty-with-Percentage-fix.xlsm
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38342275
Let me answer your last question first. No, I just answer people's questions for the fame and glory:)
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38342343
Try this update.

If this works what some people do to reward someone with extra points is to open a new "question" and post the URL here. No problem at all if you don't want to do that.
Marty-with-Percentage-fix.xlsm
0
 

Author Comment

by:biker9
ID: 38342432
Marty, its returning an incorrect value,
hope i did this right;

http://www.experts-exchange.com/askQuestion.jsp?taid=604

Ray
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38342874
The URL you posted takes me to the page where you can ask a new question. What you should do (if you want) is after we're done with this question, create a complete new "question" with a title something like 'For Marty' (or anything) with anything you like in the body, complete the entry and then copy and paste the URL of that question here.

But first what happens with the change I made. In other words what do you mean exactly when you say "its returning an incorrect value"?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38343006
Okay I think I see the problem.

Remove the txtIndex.Text = Format(txtIndex, "##,##0.00%") line from the CmdTab_MouseDown event and then add it to the CmdTab_MouseUp event.
0
 

Author Comment

by:biker9
ID: 38343594
I must be blind, i can't find CmdTab_MouseUp,
and entering 25 returns 2,500% rather than 2.5%.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38343738
Each control has several events for which you can provide code, but you won't find the cmdTab_MouseUp event unless you create one. You do that by selecting cmdTab here
Left sideand then choosing MouseUp here
Right

Here's a spreadsheet that I think does what you want, but there are a couple of problems.

(1) I had to remove the "$" form the formatting because if you try to format it again and it contains the '$' it will cause an error.

(2) In order to format 25 as 2.5 you need to divide it by 10 (which I did), but I think a much better approach would be to provide a decimal point command button so that a user can enter "2.5". I would also suggest removing all Format-ing of the numbers from the textboxes and do whatever formatting you want after the user is done.
Marty2.xlsm
0
 

Author Comment

by:biker9
ID: 38346750
Marty,  with your comments and some futzing about, I've been able to get this to work.
I changed the Cmd_OK and the Case 6 instructions to include new formatting instruction '  & "%"   ', and then realized the "after_update" instruction doesn't apply to the userform keyboard and changed it around. I've attached it for your reference.

Appreciate your input once again, and if you follow the link I'd be happy to kick in some more points, fame,glory & significant karma :) .

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27846824.html
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question