ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   userinput is wrong (check in vba) (https://www.excelbanter.com/excel-programming/339188-userinput-wrong-check-vba.html)

Pierre via OfficeKB.com
userinput is wrong (check in vba)
 
Hi,

I have a number of txt_boxes in a userform where the user has to put in a
number like 2,5 (two and a half) or 25,7 for example.
If the user puts in 2.5 the application wille generate an error message.

I have two questions:

1. How can i make sure that the numer is always put in, using a "," instead
of a "." ?
if the user puts in eg 2.5 i need it to correct to 2,5 or set the
focus back to the field with an error message.
2. How can i make sure all the 10 fields are filled and if one is not filled,
to generate an error message and go to the first field that is not filled?

Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

Dave Peterson
userinput is wrong (check in vba)
 
I created a userform with 10 textboxes, 1 label and 2 commandbuttons.

This is the code that's behind the userform:

Option Explicit
Private Sub UserForm_Initialize()
Me.Label1.Caption = ""
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim OkToContinue As Boolean

OkToContinue = CheckTextBoxes

If OkToContinue = False Then
Exit Sub
End If

MsgBox "do the real work here"

End Sub
Private Sub TextBox1_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(1, keyascii)
End Sub
Private Sub TextBox2_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(2, keyascii)
End Sub
Private Sub TextBox3_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(3, keyascii)
End Sub
Private Sub TextBox4_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(4, keyascii)
End Sub
Private Sub TextBox5_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(5, keyascii)
End Sub
Private Sub TextBox6_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(6, keyascii)
End Sub
Private Sub TextBox7_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(7, keyascii)
End Sub
Private Sub TextBox8_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(8, keyascii)
End Sub
Private Sub TextBox9_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(9, keyascii)
End Sub
Private Sub TextBox10_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(10, keyascii)
End Sub

Sub CheckKeys(TBNumber, keyascii)
Dim myTB As MSForms.TextBox
Dim OkChar As Boolean

Set myTB = Me.Controls("textbox" & TBNumber)

OkChar = True
Select Case keyascii
Case Asc("0") To Asc("9")
'ok
Case Is = Asc(",")
If Len(myTB.Value) - _
Len(Application.Substitute(myTB.Value, ",", "")) 0 Then
'too many decimals
OkChar = False
End If
Case Else
OkChar = False
End Select

If OkChar = False Then
keyascii = 0
Beep
End If
End Sub
Function CheckTextBoxes() As Boolean
Dim iCtr As Long
Dim myTB As MSForms.TextBox
Dim AllOk As Boolean

AllOk = True
For iCtr = 1 To 10
Set myTB = Me.Controls("Textbox" & iCtr)
If Len(Trim(myTB)) = 0 Then
myTB.SetFocus
AllOk = False
Exit For
End If
Next iCtr

If AllOk = True Then
Me.Label1.Caption = ""
Else
Me.Label1.Caption = "Please fill in all the textboxes!"
End If

CheckTextBoxes = AllOk

End Function




"Pierre via OfficeKB.com" wrote:

Hi,

I have a number of txt_boxes in a userform where the user has to put in a
number like 2,5 (two and a half) or 25,7 for example.
If the user puts in 2.5 the application wille generate an error message.

I have two questions:

1. How can i make sure that the numer is always put in, using a "," instead
of a "." ?
if the user puts in eg 2.5 i need it to correct to 2,5 or set the
focus back to the field with an error message.
2. How can i make sure all the 10 fields are filled and if one is not filled,
to generate an error message and go to the first field that is not filled?

Thanks,
Pierre

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

--

Dave Peterson

Jean-Pierre D via OfficeKB.com
userinput is wrong (check in vba)
 
Hi Dave,

Thanks for your input.
Can you explain the Me.Label1.Caption = "" part of your code please ?
Thanks,
Pierre



Dave Peterson wrote:
I created a userform with 10 textboxes, 1 label and 2 commandbuttons.

This is the code that's behind the userform:

Option Explicit
Private Sub UserForm_Initialize()
Me.Label1.Caption = ""
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim OkToContinue As Boolean

OkToContinue = CheckTextBoxes

If OkToContinue = False Then
Exit Sub
End If

MsgBox "do the real work here"

End Sub
Private Sub TextBox1_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(1, keyascii)
End Sub
Private Sub TextBox2_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(2, keyascii)
End Sub
Private Sub TextBox3_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(3, keyascii)
End Sub
Private Sub TextBox4_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(4, keyascii)
End Sub
Private Sub TextBox5_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(5, keyascii)
End Sub
Private Sub TextBox6_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(6, keyascii)
End Sub
Private Sub TextBox7_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(7, keyascii)
End Sub
Private Sub TextBox8_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(8, keyascii)
End Sub
Private Sub TextBox9_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(9, keyascii)
End Sub
Private Sub TextBox10_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(10, keyascii)
End Sub

Sub CheckKeys(TBNumber, keyascii)
Dim myTB As MSForms.TextBox
Dim OkChar As Boolean

Set myTB = Me.Controls("textbox" & TBNumber)

OkChar = True
Select Case keyascii
Case Asc("0") To Asc("9")
'ok
Case Is = Asc(",")
If Len(myTB.Value) - _
Len(Application.Substitute(myTB.Value, ",", "")) 0 Then
'too many decimals
OkChar = False
End If
Case Else
OkChar = False
End Select

If OkChar = False Then
keyascii = 0
Beep
End If
End Sub
Function CheckTextBoxes() As Boolean
Dim iCtr As Long
Dim myTB As MSForms.TextBox
Dim AllOk As Boolean

AllOk = True
For iCtr = 1 To 10
Set myTB = Me.Controls("Textbox" & iCtr)
If Len(Trim(myTB)) = 0 Then
myTB.SetFocus
AllOk = False
Exit For
End If
Next iCtr

If AllOk = True Then
Me.Label1.Caption = ""
Else
Me.Label1.Caption = "Please fill in all the textboxes!"
End If

CheckTextBoxes = AllOk

End Function

Hi,

[quoted text clipped - 13 lines]
Thanks,
Pierre



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

Dave Peterson
userinput is wrong (check in vba)
 
Instead of using a message box to tell the user what's going on, I used a label
on the userform.

I want that label "hidden" when the userform starts--so I just make the caption
blank ("").



"Jean-Pierre D via OfficeKB.com" wrote:

Hi Dave,

Thanks for your input.
Can you explain the Me.Label1.Caption = "" part of your code please ?
Thanks,
Pierre

Dave Peterson wrote:
I created a userform with 10 textboxes, 1 label and 2 commandbuttons.

This is the code that's behind the userform:

Option Explicit
Private Sub UserForm_Initialize()
Me.Label1.Caption = ""
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim OkToContinue As Boolean

OkToContinue = CheckTextBoxes

If OkToContinue = False Then
Exit Sub
End If

MsgBox "do the real work here"

End Sub
Private Sub TextBox1_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(1, keyascii)
End Sub
Private Sub TextBox2_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(2, keyascii)
End Sub
Private Sub TextBox3_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(3, keyascii)
End Sub
Private Sub TextBox4_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(4, keyascii)
End Sub
Private Sub TextBox5_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(5, keyascii)
End Sub
Private Sub TextBox6_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(6, keyascii)
End Sub
Private Sub TextBox7_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(7, keyascii)
End Sub
Private Sub TextBox8_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(8, keyascii)
End Sub
Private Sub TextBox9_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(9, keyascii)
End Sub
Private Sub TextBox10_KeyPress(ByVal keyascii As MSForms.ReturnInteger)
Call CheckKeys(10, keyascii)
End Sub

Sub CheckKeys(TBNumber, keyascii)
Dim myTB As MSForms.TextBox
Dim OkChar As Boolean

Set myTB = Me.Controls("textbox" & TBNumber)

OkChar = True
Select Case keyascii
Case Asc("0") To Asc("9")
'ok
Case Is = Asc(",")
If Len(myTB.Value) - _
Len(Application.Substitute(myTB.Value, ",", "")) 0 Then
'too many decimals
OkChar = False
End If
Case Else
OkChar = False
End Select

If OkChar = False Then
keyascii = 0
Beep
End If
End Sub
Function CheckTextBoxes() As Boolean
Dim iCtr As Long
Dim myTB As MSForms.TextBox
Dim AllOk As Boolean

AllOk = True
For iCtr = 1 To 10
Set myTB = Me.Controls("Textbox" & iCtr)
If Len(Trim(myTB)) = 0 Then
myTB.SetFocus
AllOk = False
Exit For
End If
Next iCtr

If AllOk = True Then
Me.Label1.Caption = ""
Else
Me.Label1.Caption = "Please fill in all the textboxes!"
End If

CheckTextBoxes = AllOk

End Function

Hi,

[quoted text clipped - 13 lines]
Thanks,
Pierre


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

--

Dave Peterson


All times are GMT +1. The time now is 05:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com