ASCII Key Mapping In VB Userform
Jan 4, 2007
Private Sub Form_KeyPress(KeyAscii As Integer)
If KeyAscii = Chr(vbKeyA) Then
If sngXFactor 1 Then
sngXFactor = -1
sngYFactor = 0
End If
ElseIf KeyAscii = Chr(vbKeyW) Then
If sngYFactor -1 Then
sngXFactor = 0
sngYFactor = -1
End If
ElseIf KeyAscii = Chr(vbKeyD) Then
If sngXFactor -1 Then
sngXFactor = 1
sngYFactor = 0
End If
ElseIf KeyAscii = Chr(vbKeyS) Then
If sngYFactor 1 Then
sngXFactor = 0
sngYFactor = -1
End If
End If
End Sub
That is what I have right now, basically I just want to map W, A, S, and D to change variables. What am I doing wrong?
Keep in mind that this is a userform and not an excel worksheet.
View 9 Replies
ADVERTISEMENT
May 20, 2009
Find the ASCII character code for the characters in cells A3-D3? I have a problem importing data into the database because of these cells. They have some weird properties: ISBLANK would return them FALSE; however if I leave blank space in "Find and Replace" it will find and replaces those cells together with cells which are genuinely blank. So I could theoretically go and replace all blank cells to some character (for example ^), then do the opposite and replace ^ into blank and it would solve the problem, but I am curious as to what exactly this character is.
View 14 Replies
View Related
Nov 13, 2006
I have a sheet A1:R456 provided from an outside source, with many large text entries. In some of them appear various ascii strings such as, but not exclusively: •
It appears these have replaced apostrophe's, dashes, and other types of punctuation and formatting (bullets, etc).
I have used the Find (Ctrl-F) to locate some, but it only works if you know what you're looking for. According to Excel HELP! :o Clean() only strips the lower 32 ascii codes, and I'm pretty sure these are at the high end of the ascii chart.
I don't want to remove them, I want to replace them, but I'm not sure if it should be an apostrophe, a dash, or whatever. So I'd be happy to find them and I'll fix it as best I can.
Any VBA code to locate all cells in a range that contain any high-order ascii (say above 127) and fill with yellow?
View 9 Replies
View Related
Jun 9, 2014
Is there an easy way to convert ascii characters to their equivalent hex value, such as 0 = 30?
View 6 Replies
View Related
Apr 17, 2002
I know you can create a DEC2HEX formula. I wanted to convert Hex to Ascii.
When I use HEX2DEC, it puts the ASCII number instead of the actual character.
For instance, if I put the HEX number 4A in Cell A1, I want Cell A2 to display a capital J instead of the number 74 which is J in ASCII.
View 4 Replies
View Related
Aug 31, 2009
I am importing multiple ascii files into one workbook, each file into a single sheet of the workbook. When I manually import an ascii file, everything works and formats fine. When I import the file or files with my macro, the formatting of the values in the ascii file isnt working correctly, that is, if a value is for instance 51,442 (, being the decimal seperator), it is input to the cell as 51,442, as in fifty one thousand, four hundred forty two..My import function is below, and I have tried various combinations of using the decimal seperator command and thousands seperator command, all to no avail..
Lastly, in case it matters, this is on a PC with regional settings set to Germany as well.
Below an example of the import which I found online earlier..
x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=False, DecimalSeparator:=","
x = x + 1
View 9 Replies
View Related
Nov 18, 2009
Currently I am using this ......
View 9 Replies
View Related
Apr 20, 2014
I want to write code so that at the push of a button you can browse and find an ascii file. Select it. and then it will go into the sheet of a excel file that you chose in the code.
View 2 Replies
View Related
Apr 3, 2008
I have some dates that I want to reformat. The file is an ascii text file, and I have imported this into excel. There are 7 and 8 character lengths
3122007
10122007
I want the dates to be all 8 characters (with a zero in front of the 7 character dates), and also I want to reverse the format, so it will read 20071203
I have a number of methods but nothing has worked yet.
After this I plan to export the file as a comma separated ascii txt file. I managed to import it, but I'm not sure how to export it.
View 9 Replies
View Related
Dec 3, 2009
I've written a program to read data into a custom table from ASCII
files. The ASCII files are created from a VB script I've added to an Excel
spreadsheet. The VB script in Excel is the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim str_fileName As String
Dim int_fileNum As Integer
Dim str_fileName2 As String
Dim int_fileNum2 As Integer
MsgBox "Testing"
str_fileName = "\EPCOR02COLLECTSinto.txt"
str_fileName2 = "\EPCOR02COLLECTDisa.txt"
Application.EnableEvents = False
If (Target.Address = "$D$2") Then
'Write to the Sinto file
On Error Goto Err_CreateOutputFile
int_fileNum = FreeFile...............................
View 2 Replies
View Related
Oct 29, 2009
basically I got 2 sheets - 'Sheet1' and 'Codes'. In sheet 1 I have many columns of info(irrelevant here), we focus on one Column N, there are codes in this column and depending on the code I need to enter an Asset Class in Column AG. The Sheet (Codes) - has 2 columns with the code(column A) and its corresponding asset class in Column B.
A lot of the codes belong to the same asset class so dont be confused if u see the same asset class category twice. I need the macro to copy the correct asset class value from Column B(Sheet 'Codes) to Column AG of Sheet1.
View 2 Replies
View Related
May 8, 2014
See attached file for a better understanding.
I would like to use a formula in Column 1 (highlighted) which tells me if it relates to Fund 1 or Fund 2 using the Mapping table in column H.
Using the mapping table would be nice but no need to.
Excel Question.xlsx
View 5 Replies
View Related
Jan 22, 2009
I am using the Formula of Index here as i have a sheet names with Details and mapping i have a company name for which i have to map the policy no from the details sheet. The Thing is i will not use Vlookup i need a formula of Index which maps the data from the backwards range. Attached a excel sheet.
View 4 Replies
View Related
Jan 28, 2009
I need to count a list of corporates claims settled as i have multiple Claims Status as outstanding, In process,Cheque prepared and Etc.
Basically i cannot use countif formula for the condition as it has only a range & criteria but here i have to check the list in the validation tab and map with the details in the side spread sheet given which are settled and count them so such criteria is so unknown to me to use .
View 11 Replies
View Related
Nov 11, 2010
I am trying to produce an Excel file filled with data from the XML file.
Some of the cells need to have the same element data. However the Microsoft Excel is not allowing to map an XML element to more than one cells.
View 3 Replies
View Related
Feb 15, 2007
mapping data. Attached is a sample of what I need help with.
I have a Two Worksheets with Data and a third containing the template with which I want the first to to combine into.
The first sheet looks like this:
WWID | YEAR | LITER | MAKE | MODEL | SUBMODEL
The 2nd sheet:
HOUSE_ID | YEAR | LITER | MAKE | MODEL | SUBMODEL | Part_No
I want to map these two sheets together into the 3rd that looks like this
WWID | HOUSE_ID | Part_No
by using the similar data found in the first two sheets (year, liter, make, model, submodel)
I've attached a sample of what I need..
View 4 Replies
View Related
Jan 23, 2013
I created a sort of FORM worksheet that uses lookups to pull a bunch of information (so the user does not have to key it all in,) and transfers it to a second worksheet (database worksheet), when the user clicks a "Submit" button.
The functionality is fine when used by one individual, but I am now going to have 9 different users working within this Shared file.
I seem to have worked out any of the other kinks related to the Shared file, but I'm having one problem that I should have foreseen.
The code below is looking for the next available empty row in the database sheet and copying the data there.
My problem is that the file is not updated in real-time, so if entries are submitted in a narrow time-frame, the data is fighting for the same row, and deleting one row-item or another.
My question is, is there a way to modify what I already wrote to fix this problem?
If not--the solution that I am thinking of is creating NINE separate worksheets and mapping the data into those individualy.
So... IF we have Sally, John, Scott, Dave, Jenn They each have their own worksheet.
From looking at the below, I am thinking the best option would be obviously mirroring the template for each user, and modifying this portion:
VB:
Option Explicit
[U]Dim wsData As Worksheet
[/U]Dim wsForm As Worksheet
Dim wsPCD As Worksheet
Dim rNextCl As Range
Dim rNextC2 As Range
[Code]....
Will that work transitioning into the rest of the code? I'll assume that the above needs revised as I'm new to this all.
VB:
Option Explicit
Dim wsData As Worksheet
Dim wsForm As Worksheet
Dim wsPCD As Worksheet
Dim rNextCl As Range
Dim rNextC2 As Range
[Code]....
View 2 Replies
View Related
Jan 31, 2008
I have a user whose links in his spreadsheets are in UNC format. He changes them to drive letter mappings, but when he opens the workbook again, the UNC format returns. how to change the links so that they remain as F: etc.. rather than \servernamesharename The user has MS Excel 2000 and Windows Xp Professional
View 2 Replies
View Related
Dec 31, 2013
I have a macro that pulls specific data from a CSV, then I copy that extract to my master excel doc (setup by year), so I can put the data into pivot tables and charts. This is used to create "management" style reports.
This data has IP addresses in it. Both internal and external. Trying to find a formula that will look at Cell A, where the IP address is and input either External or Internal into column AA, based on the number.
Internal would be numbers between 10.0.0.0 and 10.255.255.255, 172.16.0.0 and 172.31.255.255, and 192.168.0.0 and 192.168.255.255. Anything not in those three ranges would show as External.
View 6 Replies
View Related
Aug 21, 2009
I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
View 9 Replies
View Related
Jul 16, 2012
I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes
This is the code
Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")
[Code] ......
View 1 Replies
View Related
Jun 15, 2009
This is weird - if you delete a sheet that contained a control then
a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End
b. public variables lose their value
These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
View 9 Replies
View Related
Oct 1, 2009
I have one userform that loads combobox values upon userform Initialize. Though through a second userform changes can be made to anotherworkbook this workbook is saves any changes. when i close the second userform i need to rerun the 1st userform Initialize event to update the combobox's incase changes have been made.
View 5 Replies
View Related
May 3, 2012
I have several non-modal userforms in my App, some of them have date-fields that require manual entry typing of dd/mm/yy etc (No single userform has more than one date-box in it, this I think may be pivotally useful)
Now the Userform 'Calendar' that is built on the class of the same (cCalendar) name, has the write value line 'ActiveCell.value = theCal.value'
I'm looking to change this to refer to the correct userform.Textbox value, depending on which form is open.
I would imagine I could simply have a global string, whose value is set (or re-set) whenever a Userform is initialized (some sort of 'ActiveUF.value = Me.Name), where I get lost is referring to the components by name, so as to have a case statement by where I go:
Code:
Private Sub theCal_AfterUpdate()
Select Case ActiveUF
Case "AddForm"
application.vbe.components("AddForm").controls("AddFormDatePicker").value = theCal.value
Case "EditForm"
'.... etc
end select
end sub
better way of doing this (instead of passing around the userform name as a variable) - or proper syntax for referring to controls outside of the 'active' userform (but an open userform nonetheless)?
Every time I have to do this particular thing with userforms, I completely forget how, and the object browser always leads me on an infinite loop of Application.vbe.activevbproject.vbcomponents.vbe.active....
PS - there may be one slight complication to the process - one of the forms, has a 2-tab page in it, each page having similar (but named differently) fields. So I may need to be able to throw in 'Activepage' or whatnot
View 5 Replies
View Related
Apr 26, 2014
I Have a Userform which Have My Data i Print User Form Using Print Command Button And My Code Is
[Code] ......
But Its Printout Whole Form I only Wants To Print contents of Text Box's Or only Text From Userform TextBox. How To Print out Only Content of User Form Not The Whole Form ...
View 2 Replies
View Related
Oct 29, 2009
I'm trying to do: Let's say I have a simple userform with labels as follows:
(Label1) John's birthday: (Label2) 12.10.1974
(Label3) Mary's birthday: (Label4) 15.03.1978
(Button1) Change Birthdays
Let's say want to change the birthdays I see, so I press the button and enter the new values through another userform with inputboxes.
How do I make that change permanent, so that next time I start the macro in the labels 2 and 4 I have the last entered values for the birthdays???
View 4 Replies
View Related
Jul 4, 2012
What I would like to do is to be able to open a second workbook from a user form, preform some work on it then save and come back to the same place in the original user form.
So in steps:
1 : user clicks command button to open user form
2: user then clicks on command button on userform that opens 2nd workbook via a yes / no message box, but closes userform on 1st workbook (would be ideal if this could stay open, but hidden)
3: user then does work on 2nd workbook,
4: userform on 2nd workbook saves then activates the 1st workbook and reopens the userform
This is where no matter what I try I cant get the command button on the 1st userform to be clicked automatically so the yes / no message box appears.
This is part of the code in the 2nd sheet commandbutton that saves / closes / opens
Code:
Unload Me
ActiveWorkbook.save
Windows("ABC.xlsm").Activate
Sheets("Request Sheet").Activate
Call Sheets("Request Sheet").ForceClickOnBouttonXYZ
Call UserForm1.CommandButton6_Click 'this is where I cant get it to work!!
Windows("xyz.xlsm").Close
ABC is the 1st workbook
xyz is the 2nd workbook
This is the code on the 1st workbook I use to call on the 2nd workbook
Code:
Public Sub ForceClickOnBouttonXYZ() Call CommandButton1_Click End Sub
View 7 Replies
View Related
Jun 8, 2009
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Application.ScreenUpdating = True
Unload Me
RestrictedOptions.Show
Else
Cancel = True
End If
End Sub
Trying to use above code to unload active userform and show new userform when red X (close) selected by operator. With the code as is, the Unload Me leaves the form on the screen and displays the RestrictedOptions form. If i remove the Restrictedoptions.Show, the Unload Me does remove the original form.
View 9 Replies
View Related
Jul 3, 2004
When I run the userform initialize procedure to reset the values in text boxes and the like instead of resetting like it should, now it closes the userform completely and then won't allow me to show it again, what could be the problem?
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
TextBox3.Value = ActiveWorkbook. Sheets("Sales Invoice"). Range("G15").Value
Dim hWndForm As Long
Dim hMenu As Long
hWndForm = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
hMenu = GetSystemMenu(hWndForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub
The Dim stuff down is to gray out the x button, there are also some module level declarations to go with that...
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Private Const SC_CLOSE As Long = &HF060
the userform shows when the workbook opens and is then hidden when the user has finished entering values, then when the user goes through the process of being asked to print and save, they are then asked if they would like to create a new record, if yes then it shows the userform again, but the userform is still filled with all the stuff previously entered. I tried using the unload me instead of hiding and that wouldn't work at all, didn't give errors just didn't show the userform either, this at least shows the userform, but now when the user goes to clear the information by initializing the userform again, it simply closes the userform and then it can't be shown again either.
[url]
This is the link to the ZIP, and here are some instructions for setting it up to work.
The contents of this need to be unzipped into a folder called SyntheticShield that is placed in the C: drive that way all paths begin with C:SyntheticShield and then the other folders or files will be referenced correctly. The template I'm having problems with is the SyntheticShieldInvoiceMaker template, when you open it you'll be prompted whether you want to create a new invoice, number is final blah blah blah, say yes, then it will ask if you are importing from the Quotemaker which you aren't say no, it should then show the userform as it should. Then you can go through that process and by pressing either the create invoice or go to template directly whatever, you can hide the userform. Then you press the command button (red) a userform is brought up asking if you'd like to save without emailing, save with emailing or close controls, as for right now, I'm just getting the save without emailing to work the rest is all the same just a few tidbits of code. So click save without emailing, it should then prompt an are you sure message box, click yes it will do some things, then it will ask if you want to print, click no, then it will ask do you want to create a new invoice, click yes, this should then start the process all over again by calling the workbook_open procedure, however, when you go through everything, and click no to the import from quotemaker part it won't show the userform. And at one point it would, but then I couldn't initialize the userform without it disappearing and not being allowed to be shown...I tried putting a command button on the template to show the userform, but it wouldn't do it either.
View 6 Replies
View Related
Jul 25, 2006
how do i show a userform in Excel in the spreadsheet environment? I used to "run macro" but unable to run userform code "private sub".
View 2 Replies
View Related
