Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer 2nd edition
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 26th, 2016, 09:19 AM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Entering "e" as numeric input in Textbook

I've created a Userform which allows numeric data entry into a Textbox via either an input file or manually on the Userform. The manual option allows the user to change a data entry already loaded from the input file. The Textbox value also varies according to choice of units (SI or Imperial) via a ComboBox. The problem I'm having is how to allow the user to manually enter a number with "e" in it, e.g. 3.12e06 or 5.93e-07. I've shown a small section of my code below. It is possible for numeric values with "e" in them to be entered via the input file into Sheet("Rock Data") and hence to appear in the TextBox on the UserForm, but it is not possible to manually enter a value into the TextBox if that value contains an "e" in it. Any help or advice would be greatly appreciated.
code
Private Sub txbYM_Change()

'Allow user to change set input value if "User input" has been selected.

txbYM.Locked = True
If cbxRockType.ListIndex = 7 Then
txbYM.Locked = False
If cbxYMUni.Value = Sheets("Rock Data").Range("G2").Value Then
Sheets("Rock Data").Range("D65").Value = txbYM.Value / GPa_psi
Sheets("Rock Data").Range("F65").Value = txbYM.Value
ElseIf cbxYMUni.Value = Sheets("Rock Data").Range("E2").Value Then
Sheets("Rock Data").Range("D65").Value = txbYM.Value
Sheets("Rock Data").Range("F65").Value = txbYM.Value * GPa_psi
End If
End If

End Sub
/code
Reply With Quote
  #2 (permalink)  
Old September 27th, 2016, 07:45 PM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Well, a couple things...

First, you wrote "e.g. 3.12e06" when really it is expressed in Excel's scientific format as 3.12E+06.

In any case, the letter e (or E) is not regarded as a text letter but a portion of the underlying scientific notation. If you try to manually enter a scientific expression into a textbox that is validated programmatically for numbers only, the entry will be rejected, as the textbox will regard the string as alphanumeric.

What you can do is enter the actual number but format the textbox content with a Change (or some other event) to show it in a scientific format.

For example, using your aforementioned number of 3.12E+06, in a cell (let's say cell A1) enter its equivalent in full of 3120000 or its equivalent in scientifcally-defined exponential format of 3.12*10^6. Import that number to the textbox with your UserForm's Initialize event. In whatever fashion you have your source number in a cell, you can format it to look like any other number format, such as Scientific, as (or after) that value is placed in the textbox.

Example:

Private Sub UserForm_Initialize()
TextBox1.Text = Format(Range("A1").Value, "0.00E+00")
End Sub

The textbox on your userform will display 3.12E+06 although the number in cell A1 looks like, and is, 3120000 or whatever way you have it numerically formatted.
Reply With Quote
  #3 (permalink)  
Old September 28th, 2016, 04:20 AM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Entering "e" as numeric input in TextBox

Tom, thank you for your reply. My problem is not showing a number (that is drawn from a cell in the Worksheet) in the TextBox with "E" in it, e.g. 3.12E06. The UserForm is already set up to enable this, for example using the construction below:
code
txbYM.Value = Format(Sheets("Rock Data").Range("F2").Value, "scientific")
/code

My problem is allowing a user to manually enter into the TextBox on the UserForm a number such as 3.12e06 (or 3.12E06 if that is all that would be possible) or 3.12e-06 (or 3.12E-06, again if that is all that would be possible). In other parts of my code I use the following to limit any manual entry to numeric input, but (1) have not yet managed to adapt this to allow "e" (or "E" instead!) entry as well as numeric entry and (2) this is for a KeyPress event, whereas the sub-routine that I'm having the difficulty with is a Change event.
code
Private Sub txbCorDep_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

'Restrict entry to numeric and supporting character data.
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc("-")
If InStr(1, Me.txbCorDep.Text, "-") > 0 Or Me.txbCorDep.SelStart > 0 Then
KeyAscii = 0
End If
Case Asc(".")
If InStr(1, Me.txbCorDep.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select

End Sub
/code
I would appreciate any further advice. Best regards, Colin
Reply With Quote
  #4 (permalink)  
Old September 29th, 2016, 05:38 PM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Your KeyPress event can be changed to this to achieve the effect you are after.


Private Sub txbCorDep_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii

'Plus sign, one is allowed.
Case 43
If InStr(1, txbCorDep.Text, "+", 1) > 0 Then
KeyAscii = 0
MsgBox "There already is a plus sign.", 48, "Only one plus sign is allowed."
Exit Sub
End If

'Minus sign, one is allowed.
Case 45
If InStr(1, txbCorDep.Text, "-", 1) > 0 Then
KeyAscii = 0
MsgBox "There already is a negative sign.", 48, "Only one negative sign is allowed."
Exit Sub
End If

'Decimal period dot, one is allowed.
Case 46
If InStr(1, txbCorDep.Text, ".", 1) > 0 Then
KeyAscii = 0
MsgBox "There already is a decimal period.", 48, "Only one decimal period is allowed."
Exit Sub
End If

'Numbers from 0 to 9 are OK.
Case 48 To 57
Exit Sub

'"E", one is allowed.
Case 69
If InStr(1, txbCorDep.Text, "E", 1) > 0 Then
KeyAscii = 0
MsgBox "There already is an ''E''.", 48, "Only one ''E'' is allowed."
Exit Sub
End If

'Nothing else is acceptable.
Case Else
KeyAscii = 0
MsgBox "Only numeric-representative strings are allowed.", 48, "Numbers only please."
Exit Sub

End Select

End Sub
Reply With Quote
  #5 (permalink)  
Old September 30th, 2016, 11:30 AM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Entering "e" as numeric input in Textbox

Tom,

Thanks again. The lines beginning with "If Instr(1, " in the code you provided seem to be giving a "Compile error: Syntax error". I could look into this or is there an obvious reason?

However, my original problem relates to a Change event rather than a KeyPress event, i.e. to a sub-routine starting with "Private Sub txbYM_Change()". Your solution won't work with such a Change event, I don't believe? Is there an option to achieve this same effect with such a Change event?

Best regards, Colin
Reply With Quote
  #6 (permalink)  
Old September 30th, 2016, 02:10 PM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

I tested the code before I posted it so I am sure it works.

I don't understand why you are using both a Change event and a KeyPress event. If the Change event is only for the manual entry of the TextBox to help validate Scientific or numeric entry then disable that Change event (comment it out), and only use the KeyPress event I posted.
Reply With Quote
  #7 (permalink)  
Old September 30th, 2016, 02:30 PM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Entering "e" as numeric input in TextBox

Tom,

The "KeyPress" and "Change" sub-routines are separate parts of my code and refer to entirely different TextBoxes. My difficulty relates to the "Change" event sub-routine.

I won't have access to the VBA program until Monday but will investigate then why the "KeyPress" code you provided isn't working for me when it is for you.

If you could possibly advise regarding how I could implement what you provided in the "Change" event sub-routine it would be much appreciated.

Regards,

Colin
Reply With Quote
  #8 (permalink)  
Old October 5th, 2016, 12:10 PM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Entering "e" as numeric input in Textbox

Tom,

Thanks again. The code worked once I'd reformatted it, e.g.

code
'Minus sign, not at the beginning.
Case 45
If Me.txbCorDep.SelStart = 0 Then
KeyAscii = 0
MsgBox "A negative depth is disallowed.", 48, "Negative number disallowed."
Exit Sub
End If

'Minus sign, one is allowed.
Case 45
If InStr(1, Me.txbCorDep.Text, "-", 1) > 0 Then
KeyAscii = 0
MsgBox "There already is a negative sign.", 48, "Only one negative sign is allowed."
Exit Sub
End If
/code

Could you explain the significance of the numbers after Case, as in "Case 45", "Case 46", etc.?

Also, I've realised that I'm going to have to rethink the TextBox Change event code.

Regards, Colin
Reply With Quote
  #9 (permalink)  
Old October 5th, 2016, 12:40 PM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Cracked it! The significance of the numbers as in "Case 45" is that the number is a decimal ASCII value equivalent to a character, e.g. "43" represents "+", "45" represents "-" and so on.
Reply With Quote
  #10 (permalink)  
Old October 5th, 2016, 08:13 PM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

For future reference, you can list the 255 ascii characters on a sheet by entering this function in a cell in row 1, such as cell A1, and copying it down to cell A255:

=CHAR(ROW())
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
class="x" to input control in Sell page doen't work? torr6420 BOOK: Beginning ASP.NET Web Pages with WebMatrix 2 December 5th, 2011 05:50 AM
MSXSL gives error message for "for" inside "select" ilyaz XSLT 1 December 9th, 2010 05:02 PM
<input type="Checkbox" question??? RinoDM HTML Code Clinic 1 October 26th, 2009 06:00 AM
How to theme the "Browse" button of "FileUpload" control? varunbwj BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 2 October 14th, 2009 01:22 AM
Add a CheckBox DataColumn to my DataGridView, Null format: "" or "True" but Error: F ismailc C# 2005 0 September 25th, 2009 04:56 AM



All times are GMT -4. The time now is 06:47 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.