[RESOLVED] Problems with KeyAscii and Selectalltext, and one more

[MichaelWB]

I'm having problems trying to limit the input into the textbox's using the KeyAscii case.
I used this same line in a program last week and it worked, so I'm puzzled why it's not following the rules of the case that is specified.
Also, again I used the SelectAllText, in a program, and followed the same code modifying the textbox names to the new program and it still is not working.

Lastly, If someone could help me with the best way to provide some I guess data validation for the Calculate button, so that it would not run if not input is put in, or only run the first line (of the 4 different lines) depending on how much info has been inputted, that way the program can still work and not be an all or none kinda thing.

If I can get this problems straightened out, I'm going to start looking next into outputting the results to an Excel Worksheet, look forward to learning more!!!

[VBFnewcomer]

Welcome Michaelwb

I'm having problems trying to limit the input into the textbox's using the KeyAscii case

a lot of people have.

I used this same line in a program

what line

Also, again I used the SelectAllText, in a program

You need to post some code and give a clear description of your problem. Like Hack would say 'tell us how much you have reached till now' and what are u trying to do.

[VBFnewcomer]

oops I missed your attach..sorry! :sob:

[VBFnewcomer]

I'm having problems trying to limit the input into the textbox's using the KeyAscii case

set the keypreview property of form as True
see if u like the shorter code

If KeyAscii = 8 Then Exit Sub
    If InStr("1234567890", Chr(KeyAscii)) = 0 Then
        KeyAscii = 0
    End If

[VBFnewcomer]

validation for the Calculate button

Private Sub cmd_calc_Click()
If IsNumeric(Trim(Me.txt_end_odom)) And IsNumeric(Trim(Me.txt_begin_odom)) Then
    Dim sngMilesDriven As Single
    Dim sngGallonsConsumed As Single
    
    sngMilesDriven = txt_end_odom.Text - txt_begin_odom.Text
    txt_miles_driven.Text = sngMilesDriven
    
    sngGallonsConsumed = txt_total_cost.Text / txt_price_per_gal.Text
    txt_gal_consumed.Text = Format(sngGallonsConsumed, "Fixed")
    
    txt_mpg.Text = Format(sngMilesDriven / sngGallonsConsumed, "Fixed")
    
    txt_gas_cost_mile.Text = Format(txt_total_cost.Text / sngMilesDriven, "Currency")
Else
    MsgBox "Please enter a valid odometer reading", vbInformation, App.Title
End If
End Sub

[MichaelWB]

Woohoo, the keypreview property was it!! thank you... thats the first part down. Thanks for the alternative style also, I think I'm gonna stick with the other way as it was how I learned in my book from class.

[VBFnewcomer]

validation for calculate is concerned it solely depends on what are the things you want not to happen. In the above code I just added a validation to ensure that the values are only numeric. You can have more but you need to specify what are the validations you need so members can help you.

[VBFnewcomer]

forget you textbooks. If you want real coding check with our members. Incidentally if you are wondering how to use it -
InStr(here type the chars you want to allow even space, Chr(KeyAscii)) = 0 Then
you'll love it in long run

[MichaelWB]

Oh man, you work quick..thanks!! looks good,
I'm sure it would be easy, but I would to break the cmd_calculate_click into different parts per-say, their are 4 different lines that are run from that event, and I would like to stagger them, so If only Begin and Ending Odometer is entered the event will still run and not bomb out because of the missing info in Total Cost, and Price per gallon.

The Data Validation you provided was simple enough it seems, with the limiting of only Numbers by KeyAscii Case, it should work as long as their is something inputed.

[VBFnewcomer]

Oh man, you work quick

You call that quick...Yu havent seen our members answering very complicated posts. The replies come in tons. Ok now to your problem:
As I stated earlier you need to decide which data/info is compulsory and which is optional. If compulsory is missed no process should take place. The optional portion should be coded within the portion where compulsory part is passed true. In the amend I made for you I ensured that begin & end is there you can continue for other values as well

[MichaelWB]

Alright, I got it working!! woohoo

Here's my code.. is there a different way to do it or more clean?
'Calculate all available data
Private Sub cmd_calc_Click()
If IsNumeric(Trim(Me.txt_end_odom)) And IsNumeric(Trim(Me.txt_begin_odom)) Then
Dim sngMilesDriven As Single
Dim sngGallonsConsumed As Single

sngMilesDriven = txt_end_odom.Text - txt_begin_odom.Text
txt_miles_driven.Text = sngMilesDriven
Else
MsgBox "Please enter a valid odometer reading", vbInformation, App.Title
End If
If IsNumeric(Trim(Me.txt_total_cost)) And IsNumeric(Trim(Me.txt_price_per_gal)) Then
sngGallonsConsumed = txt_total_cost.Text / txt_price_per_gal.Text
txt_gal_consumed.Text = Format(sngGallonsConsumed, "Fixed")

txt_mpg.Text = Format(sngMilesDriven / sngGallonsConsumed, "Fixed")

txt_gas_cost_mile.Text = Format(txt_total_cost.Text / sngMilesDriven, "Currency")
Else
txt_miles_driven.Text = sngMilesDriven
End If
End Sub


Thanks, any suggestions on making the SelectAllText work, or a simplier way to make all text be selected when the textbox has focus?

[anhn]

set the keypreview property of form as True
see if u like the shorter code

If KeyAscii = 8 Then Exit Sub
    If InStr("1234567890", Chr(KeyAscii)) = 0 Then
        KeyAscii = 0
    End If

forget you textbooks. If you want real coding check with our members. Incidentally if you are wondering how to use it -
InStr(here type the chars you want to allow even space, Chr(KeyAscii)) = 0 Then
you'll love it in long run

The above code works but not a good way.

Try below:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   '-- allow only digits input
   Select Case Chr(KeyAscii)
      Case "0" To "9", vbBack 
         'do nothing
      Case Else
         KeyAscii = 0
   End Select
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   '-- allow only letters A-Z, also convert a-z to uppercase
   Select Case KeyAscii
      Case Asc("A") To Asc("Z"), vbBack
         'do nothing
      Case Asc("a") To Asc("z")
         KeyAscii = Asc(UCase(Chr(KeyAscii)))
      Case Else
         KeyAscii = 0
   End Select
End Sub

[MichaelWB]

Thanks Anhn, I'm going to snip that and save it, especially the second one about alpha input, the first one is pretty close to the way I was already doing it.

Anyone have any suggestions on the SelectAllText?
Here's part of the code, I have been using.. I put one of these for each textbox (8 total)

Private Sub txt_gas_cost_mile_Gotfocus()
SelectAllText txt_gas_cost_mile
End Sub

And then this...

' Select All text in textbox
Private Sub SelectAllText(tb As TextBox)
tb.SelStart = 0
tb.SelLength = Len(tb.Text)
End Sub

*EDIT* FIXED!!! I was referencing the wrong textbox's ( the output ones that are locked, and with no focus instead of the input set) and using the Change instead of Gotfocus...woohoo!!

Anyone wanna help point me towards a way to output the data from all of the textbox's into a new spreadsheet with the label's being the corresponding headers in the spreadsheet. I would like to do this at the same time the Calculate Click is done.

[MichaelWB]

*EDIT* FIXED, and here's the working code if anyone wants to comment.

'Calculate all available data
Private Sub cmd_calc_Click()

Dim sngMilesDriven As Single
Dim sngGallonsConsumed As Single

If IsNumeric(Trim(Me.txt_end_odom)) And IsNumeric(Trim(Me.txt_begin_odom)) Then
    sngMilesDriven = txt_end_odom.Text - txt_begin_odom.Text
    txt_miles_driven.Text = sngMilesDriven
Else
    MsgBox "Please enter a valid odometer reading", vbInformation, App.Title
End If

If IsNumeric(Trim(Me.txt_total_cost)) Then
    txt_gas_cost_mile.Text = Format(txt_total_cost.Text / sngMilesDriven, "Currency")
Else
End If

If IsNumeric(Trim(Me.txt_price_per_gal)) Then
    sngGallonsConsumed = txt_total_cost.Text / txt_price_per_gal.Text
    txt_gal_consumed.Text = Format(sngGallonsConsumed, "Fixed")

    txt_mpg.Text = Format(sngMilesDriven / sngGallonsConsumed, "Fixed")
Else
End If

End Sub

I'm going to try and play with excel some now, thanks everyone!

[VBFnewcomer]

Dear Anhn

The above code works but not a good way

can you explain a lil bit

[anhn]

Dear VBF,
I said it "works but not a good way" because in general when you deal with uppercase and lowercase letters your Instr() cannot handle and I don't want to type a long string with 26 characters from "A" to "Z" or 36 chararcters if digits are included, and more when you want to do different things with different keystrokes then use Select Case would be easier.

[VBFnewcomer]

I personally feel

type a long string with 26 characters from "A" to "Z" or 36 chararcters if digits are included

is more easier than writing multiple select cases. Also note if the chars to be allowed is not contiguous it might call for more lines of code and not to mention the keyascii=0s.

when you want to do different things

no problem put in the next instr(). Even then the coding would be less. Let me know if this coding style may have any disastrous results. If it is only coding styles (sticking to tradional/standard) then I cannot have anything to say each has his own liking.