How to set up a sheet with single-keystroke data entry. I am writing a sheet to store golf scores. I need to be able to key in scores at a high rate of speed.
The allowed data are: 1, 2, 3, 4,5 6, 7, 8, 9. I want the operator to be able to press 3 (or Alt-3) and get:a) a 3 is typed into the cell.
b) the cursor moves to the right one cell.
Etc. for 1 thru 9.
Basicaly what I am trying to accomplish is the use of a single cell to enter the values 1 thru 60, so starting at e2 for instance, if I enter the number 1 then cell f2 will populate with a 1, if I were to enter the number 18 then the cell w2 would populate with an 18. So the range of cells that need to populate in this example would be (f2:BM2) for row 2. Furthermore if I were to enter a -1 or a -18 in cell e2 then those numbers would be eliminated from the appropriate cells on row 2. The next 299 rows would need to perform the same way for a total of 300 user input rows.
The way that I'm imagining e2 would behave in the example would be...type a 1 then enter....18 then enter....31 then enter and so on. Columns f thru bm would locked and unselectable....so a tab should send me back to a3.
Reasoning.... this is a simple scoring system based on 60 codes and data integrity is very important and I want to eliminate as much human error as possible. I will be setting autofilters and such later on.
I am attaching a screencapture as well.
I would like to be able to enter a number in a single column, and then Excel adds this number to a total column each time it is updated.
This is for entering via a PDA, so I need a simple entry method, ie to enter a figure, and then keep a running total every time a new figure is entered. This would only use the one column and overwrite the number each time, rather than adding up individual columns.
The attachment shows the items listed in column A, column C3 - C10 would be where I would like the number of items entered, and as I go round, I would just update the last figure. I would like the figures from C3 - C10 to be added to the Total column D3 - D10 for each item.
I want (using keystrokes only) to Save As my workbook with the data in a certain cell. However, when I select this cell, and then ctrl + c, it copies the cell (not just the data inside), and so it doesn't let me paste this as the name of the document. Does anyone know how to use keystrokes to highlight the data itself in a selected cell.
View 3 Replies View Relatedi have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
I am trying to limit the magitude of number entered into a single cell. For instance I want the highest number that can be entered into a cell to be 1500. How can I do this?
View 2 Replies View RelatedI have a worksheet in which i am asking a user to enter manually a number in cell E3. Suppose the user starts with 100, then the next time he is entering in E3 he shoudnt be able to enter 100 nor any number less than 100. I dont have a range for the numbers that the user is going to enter.Therefore countif function does not work. Is there any way that i an store the number taht the user enters first in cell E3 and then use that database to avoid the user from entering the same or a number lesser than that number.????
View 2 Replies View RelatedSince I practically never deliberately hit Control-2 through Control-5 -- and do hit them "more than never," I thought I'd grab them with the KeyPress event (or KeyDown?). Then it hit me that I'm not sure where it would go.
This is for every workbook so this would generically reside in personal.xls. ThisWorkbook doesn't seem like the right place; I want its scope to be ActiveWorkbook. Would it require a class module (actually, I already looked, and don't see any keyboard events under "App")?
I have the following in WorkBook and would like it executed when I hit enter after entering data into "Quarter":
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Set WatchRange = Range("Quarter")
Set IntersectRange = Intersect(Target, WatchRange)
If Not IntersectRange Is Nothing Then Application.Run "PERSONAL.XLSB!ChangeRangeNames"
End If
Application.EnableEvents = True
End Sub
I have a list of asset numbers that I am displaying to a user. I have a cell where the user is to type in the asset number. What I would like is to be able to display a list of matches in another group of cells.
I.e. - if the cell is populated with "D49", the list on the right will contain all asset numbers starting with "D49"...
I can do this easily enough if I wait for the "Change" event on the worksheet - but this necessitates the user typing some in, hitting enter, etcetera.
Is there any way to trigger an event per keystroke instead of on worksheet.change?
this it the row. Article No. is A1, but G04532 is A10073 because of sorting. I want to select A1 and do a for loop downwards to see if the next cell is the same. Ultimately i want to copy A1:A7 because they are the same....
View 9 Replies View RelatedI have a file containing data which is about 6000 rows deep by 15 columns
There is a column which has a number in each row which starts at 1 and going down to 100
I want to continue this down to 6000 and I know I can put the little cross at the bottom side and drag it down if I like
Any faster way to do this like a keystroke shortcut.
Is there a way a macro can be paused then resume running the macro by a keystroke, not a time delay.
View 3 Replies View RelatedI have a userform with 3 option buttons. Each buttin is set to display another userform.
Private Sub OptionButton1_Click()
Userform1.Show
End Sub
Private Sub OptionButton2_Click()
Userform2.show
End Sub
Private Sub OptionButton3_Click()
Userform3.Show
End Sub
Private Sub UserForm_Click()
End Sub
Right now the form is running perfectly. If I select an option with the mouse the correct form shows right up. The issue I have is that, the excel file will be running on a computer that will have no mouse. It will be using a barcode reader as its primary input source. What i would like to do is have the user swipe a 1,2 or 3 will the barcode reader or keypad.
I am using a bar code gun to enter numbers. There are two bar codes on each item that I am scanning requiring two columns for each item. Right now I squeeze the trigger and the number pops up into the currently active cell. Then I use the arrow key to move over one cell and squeeze the trigger for the next number. Then Down arrow following with a left arrow to move the highlighted cell down one line back to the first column. Kind of inefficient because although repetitive there is room for errors on my part.
Is there a way to make it that every time I press the space bar or some other key that excel highlights a cell following my desired pattern? At least for the time that I am using the bar code gun? Making it so pressing the space bar would advance the selected cell over, then down and left, then over, then down and left, alternating with each press following that pattern ad infinitum. I have to scan hundreds of these babies.
Would this be difficult? I’m not the programming type. Any help would be greatly appreciated. At least I no longer have to manually type each number in. That was a real bummer.
I'm building an Excel worksheet into a kind of dashboard with CommandButtons and TextBoxes to drive the functionality of the rest of the document.
I'm trying to recreate some of the functionality of a UserForm (e.g., tab order). I'm using the CommandButton's KeyDown event to respond if the user presses Enter:
I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg
In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)
How do I select one of these without having to double click with the mouse.
Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.
Excel 2010
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies View RelatedNow that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies View RelatedI want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.
1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
is there a way to lock certain cells to allow data entry but not allow data to be removed.
View 2 Replies View RelatedIs there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies View RelatedI used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
View 4 Replies View RelatedI have a spreadsheet which when I enter data into cell E15 (this cell is dropdown list (AL,ML,SDY,Toil)) it will copy data from another part of the spreacheet and past as value only.
So the aim is to select e15 pick from the dropdown this activates my VBA that copies data from cell A" and pastes special in A3 will this work on a dropdown and if so what code do i use. If it wont work what can i do instead.
Example I have used;
Sub ch()
If Range("F15") = "AL" Then
Range("A2").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Case Else
Exit Sub
End Select
End Sub
I've been searching but I can't find an answer that fits. Here's the closest I've come. Force Date Entry on Particular Condition. What I need is if A10 has any data in it then I want a box to pop up that instructs the user to input data into B10 they click OK and then enter the data. Once the user inputs data into B10 then the error should be cleared. If they don't enter data then the error should keep coming back.
View 3 Replies View RelatedI have a programme that will be accepting numeric data through a DDE link (Winwedge). My question is; is it possible through VBA to ensure data can ONLY be entered into a specified range from the DDE link?
View 2 Replies View RelatedI am building a rather good size userform. It is getting really lengthy code-wise because I want certain things to be enabled and disabled on the form as they click on certain options.
I know I can hard code the lines but so I am trying to come up with some coding elements where I will not have to add 20+ lines of code for everytime I want to add a new feature.
Here is one of the functions that I have, There is going to be several of these:
Private Sub chkMore2_Click()
If chkMore2 = True Then
cboProtocol2.BackStyle = fmBackStyleOpaque
cboApplication2.BackStyle = fmBackStyleOpaque
txtLowPort2.BackStyle = fmBackStyleOpaque
[Code] .......
So If I click chkMore2 ... all of the 2nd elements will be editable. If I click chkMore3 ... then all of the 3rd elements will be editable, etc. etc.
So I tried to play with this code example:
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Call Changeit(1, "yes")
Else
Call Changeit(1, "no")
[Code] ........
Excel 2007 does not like the .concatenate element.
I am trying to find away to split up data that is entered as text into a cell,and split that text data into 3 columns. Sample text enters in a cell can be:
10-3 or 1-3-1 or 12-11-0 or 1-6 or 8-8.
As you can see, the text entry can have two or three numbers in it. I would like to split this data so it looks like this: 10-3 would be cell A5=10, cell A6=3 and cell A7 is blank.
Another example would be 1-3-1 would be split A5=1, A6=3 and A7=1.
I know about the text to column function, but that is not the case as I want the data to split automatically with a formula.