Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > userinput is wrong (check in vba)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Calculated Field (wrong Qty*Price = wrong Amount) Edmund Excel Discussion (Misc queries) 8 October 4th 07 12:13 PM
Increase size of a Forms Check Box (click on to enter check mark) 718Satoshi Excel Discussion (Misc queries) 0 August 17th 07 01:52 AM
Check if Conditional Format is True or False / Check cell Color Kevin McCartney Excel Worksheet Functions 5 June 29th 07 11:12 AM
Spell check: advise and advice are used in the wrong context. Fussy Lady Excel Discussion (Misc queries) 1 March 28th 06 04:23 PM
Check or a file exist give wrong result Gerritvans Excel Programming 5 May 3rd 04 08:09 PM


All times are GMT +1. The time now is 02:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement