Get 2 Textboxes To Display Different Strings Based On Keypress
Mar 19, 2008
I have a Userform whose purpose it is to capture payment information, in particular Creditcard information. To this end it has (amongst other elements) 2 textboxes - textboxCardNr1 and TextboxCardnr2.
the User enters the cardnr in Textboxcardnr1 as, e.g. 4321098765432109. Through the code below I transform this into the more readable number
4321-0987-6543-2109. I capture the keypress instance to only allow numbers to be entered in this field and to simultaniously update TextboxCardNr2 with the keystrokes.
On exiting TextboxCardNr1 the content of TextboxCardNr2 is copied to the clipboard to be available for pasting on a secure website in the approriate field.
My problem is, that when the user makes a mistake and changes the number in TextboxCardNr1, those changes are not automatically mirrored in TextboxCardNr2.
I want the easy to read form of the card - with the dashes, to be saved. The website the user is pasting the content of TextboxCardnr2 to does not accept the number with dashes.
Private Sub TextBoxCardNr1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim taste As String
Dim wert As String
taste = VBA.Chr(KeyAscii)
wert = Me.TextBoxCardNr1.Text
KeyAscii = 0
If taste = "0" Or taste = "1" Or taste = "2" Or taste = "3" Or taste = "4" Or taste = "5" Or taste = "6" Or taste = "7" Or taste = "8" Or taste = "9" Then If Not Me.TextBoxCardNr2.Text Like "################" Then
Me.TextBoxCardNr2.Text = Me.TextBoxCardNr2.Text & taste............................
View 9 Replies
ADVERTISEMENT
May 10, 2014
I would like to ask if there is a possible way for a Label to display information that are based on multiple textboxes? All the information are based on the textboxes and listboxes that are in the userform and will be displayed on the Label (label16)
userform.png
**Cross reference link: [URL]
View 14 Replies
View Related
Apr 17, 2014
Currently I am working on a data entry form for CRM database using Excel. Now i am stuck at filtering and displaying the data from the spread sheet to the user form.
There are three text boxes to key in the filter criteria and a button which will filter the data from the spreadsheet based on the criteria in the three text boxes. Then the whole row where the filtered data resides will be displayed on text boxes on the user form.
View 5 Replies
View Related
Jan 8, 2013
I have a UserForm that Dead Head Miles will be entered into one text box. The Trip Miles will be entered into the second text box I would like for the user form to show the answer after the first entries are made something like this:
1)Dead Head Miles 20 +
2) Trip Miles 500
----times---$2.00
3)Projected Rate $1040 --show this after the DH and Trip is entered and Multiplied by $2.00
View 9 Replies
View Related
Dec 23, 2013
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
View 4 Replies
View Related
Sep 13, 2009
I've got a small - almost "cosmetic" problem. I have a listBox on a userForm and I'm checking for a keystroke combination to see if the user wants to edit related data. That's working fine. However, when I return to the userForm, it beeps at me. Impertinent! I think it's because Excel doesn't know what to do with the keyStroke that I used - Alt U. I've attached code. I think that I just have to set the keycode to something that will be ignored by Excel.
View 2 Replies
View Related
May 26, 2009
Iv found some code that will limit my textbox entry to numbers only, i dont quite understand how it works and i need to change it so it limits the textbox keypress entry to Text Values only.
View 3 Replies
View Related
Aug 26, 2009
How to setfocus on a cell, when user press enter, key up, or tab ?
examp. :
i want setfocus to cell A5, when i press enter or key up on cell A2.
View 2 Replies
View Related
Nov 22, 2006
I have three textboxes (16,17,18) that need to be disabled if a certain value ("Regular Hours") is in combobox1. Would I use an If statement? Also, is there a way to "gray out" the textboxes to show they are disabled?
View 6 Replies
View Related
Oct 1, 2009
I have got some code that is duplacated a few times. It allows only number values to be entered into a textbox.
Is there a way to make a function out of this to cut out the duplication of code.
View 7 Replies
View Related
Apr 1, 2014
creating macro dynamiccally on keypress and execute it
i have 3 excel sheet sheet1, sheet2, MasterSheet
MasterSheet conatins the following
COLUMN A COLUMN B COLUMN C
Colorcode FLAG SKEYS
RGB(121,223,214) A Ctrl+a
RGB(125,228,114) B Ctrl+b
[code]....
I have 3 columns column 1 contains colorcode in RGB format column 2 Contains FLAG and column 3 contains SHORTCUT KEYS
i have a macro in sheet1 & sheet2 for coloring the backgrouf color of the selected rows in sheet1 or sheet2
Code:
Sub Macro_color()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(234, 241, 221)
.PatternTintAndShade = 0
End With
what i need is when th user presses say Ctrl+c , RGB(233,129,220) from the MasterSheet needs to be copied in the macro as .Color = RGB(233,129,220) instead of RGB(234, 241, 221) and the selected row in sheet1 shld be colored. how can it be done
View 1 Replies
View Related
Jun 14, 2013
I have an excel file (2003 version) with one sheet called sheet1.
On sheet1 I have multiple text boxes, however each text box has the same text box number "Text Box 1244" (this number appears in the top left-hand side in excel when I click the textbox).
I was wondering if it's possible to rename the text boxes based on their location on the sheet.
Eg. If i had a sheet with 5 rows of text boxes and 3 columns of text boxes (15 text boxes in total).
The top left-hand box gets renamed to "Text Box 1", then the text box below that gets renamed to "Text Box 2" and so on to the bottom of the sheet to "Text Box 5". Then the vba script would move to the text box that was to the right of the first text box (1st row again but 2nd column), and rename all the text boxes in that column ("Text Box 6" onward).
The outcome would look something like:
TB1 TB6 TB11
TB2 TB7 TB12
TB3 TB8 TB13
TB4 TB9 TB14
TB5 TB10 TB15
I'm stuck on this problem a while now and cannot find any scripts to solve it. My VBA is non-existent, I usually get by on bits of code I find on the web.
I hope each textbox has a hidden co-ordinate associated with it, then it might be possible to loop through all the textboxes based on their positions and rename them.
View 3 Replies
View Related
Dec 3, 2009
i have 2 userforms one with textboxes and the other one with listbox and textboxes.
Everytime user input their data(ie:first name, last name, address etc) in the first form the data's going to be saved in Worksheet("customerSheet") and later on to be displayed in the second form. using the listbox you can select the customer's name and the customer info will be displayed in the textboxes.
here's my code in second form
Private Sub UserForm_Initialize()
Worksheets("CustomerSheet").Activate
Range("A1").Select
'ActiveCell.CurrentRegion.Name = "Database"
'Selection.End(xlDown).Select
'ActiveCell.address(False, False)
viewCustomerBox.RowSource = "A2:A15"
End Sub
View 9 Replies
View Related
Mar 24, 2014
when i choose material from my combobox Options (cboTM), i wanted, only the textboxes regarding to the sheet material unlocked, and the others locked with the color of the form, and the same for the other options like worklabor and equipments. i could blocked for material with this code :
[Code] .....
The prob is, worklabor and equipments will be blocked too, and i dont know how to put correct info on the textboxes.
Attached File : teste1.zip
View 5 Replies
View Related
Dec 20, 2012
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
View 9 Replies
View Related
Feb 19, 2014
I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,
View 1 Replies
View Related
Jul 8, 2014
I have created a UserForm that has a ComboBox and depending on the number selected I want it to show that number of Labels/TextBoxes...
So if I select "0" nothing is shown, if I select "1" one set of Labels/TextBoxes is shown, select "2" and two sets of Labels/TextBoxes are shown... but also if I have selected "2" and then select "1" I want the second set to be hidden again...
Also I know I should have renamed the Label/TexBoxes to make it easer but I was adding things and making it up as I went along...
I'm using Excel 2010 on windows 7.
Code:
Sub UnHide_NewRoutings()
If (Engineering.ComboBox2.value) = "0" Then
Engineering.Label4.Visible = False
Engineering.TextBox5.Visible = False
Engineering.Label9.Visible = False
Engineering.TextBox9.Visible = False
[Code] ..........
View 3 Replies
View Related
Feb 7, 2014
Am trying to use index match to return value in column B based on matching 2 first chars on the left in column A.
Col A Col B
POS Majalt
POS Minalt
POS Instinctive
NB Viral
NB Sierra
NB Pierce
CLM Team1
CLM Team2
OS Tr1
OS Tr2
HR Tetra
HR Pentagon
IT penelope
Cell C1 : NBA MyFormula : {=INDEX(B:B,MATCH(LEFT(C1,2),"*"&$A:$A&"*",0))} <<== I need to find the match of NB only in range A:A to return the related values in Col B.
Expected Result:
NB Viral
NB Sierra
NB Pierce
Other than formula stated above, I've also tried with Isnumber(search), etc but all return errors.
View 14 Replies
View Related
Jan 1, 1970
This doesn't work
View 13 Replies
View Related
Dec 16, 2013
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
View 9 Replies
View Related
Jul 29, 2013
I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
View 14 Replies
View Related
Sep 17, 2012
I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)
File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.
File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work
Code:
Option Explicit
Sub get_data_from_2()
Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range
[Code] .......
the bolded part gives me an error.
View 3 Replies
View Related
Feb 16, 2014
Actual
Result
london#123;new york#34;
london;
new york;
delhi#145;chennai#54;
delhi;
chennai;
hyderabad#748;
hyderabad;
[code]....
removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.
View 9 Replies
View Related
Feb 20, 2007
Is there any way to "Inscribe" a cell? I would like to run a macro on Enter keypress, that would execute different code depending on that "inscription" that would be invisible to user. I could use some properties of . Validation property like this:
Private Sub EnterPressed
'following code to ensure proper functioning of Enter in any other Worksheet
If ActiveSheet <> mySheet 'MySheet is global Variable then
ActiveCell.Offset(1,0).Select
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
ActiveCell.Offset(0,1).Select
Else
'something else
End If
End Sub
The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.
View 8 Replies
View Related
Jan 11, 2012
how would you do the following in excel 2007:
"SUM numbers within a cell based on unique strings in another cell".
For example, how would i use formula to SUM the following numbers (and only the numbers for david and sam only once), 700+454+50+40+2+129+16
700david
700 david
454john
50buch
40daniel
2sam
2sam
129mike
16steve
View 9 Replies
View Related
Dec 28, 2011
I am trying to display some text based on another value
This is what i am trying to achieve
Conditional formatting (Refers to range(O3-P6)
=U3="A/L" (Then Display A/L in the cell)
=U3="SICK" (Then Display SICK in the cell)
=U3="OTHER" (Then Display OTHER in the cell)
View 6 Replies
View Related
Nov 6, 2009
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course.
The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
View 9 Replies
View Related
Oct 4, 2006
I have a workbook with 3 worksheets the first one is a login screen, however how do I disable the tabs at the bottom of the screen?
View 8 Replies
View Related
Aug 17, 2009
We are working on having the spreadsheet display the correct options, depending on what criteria are chosen. There are three choices in the first criteria and two choices in the second criteria. These choices are presented using data validation so they will always be formatted correctly. Worked on conditional formatting to display results but did not make much progress. Started working on "if" statements to display results, looks like this may not be efficient. Spent a lot of time reading, trying to find a solution.
View 3 Replies
View Related
Jan 12, 2010
I have two bitmap images, and I would like to show only one image at a time, based on the content of another cell's pull-down list. If the user selects "AIR", then display the AIR image. If the user selects "SERVO", then display the SERVO image.
View 2 Replies
View Related