Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking characters
RI've a Userform where a textbox picks its value as a proposal from a
worksheet cell when initiating. This cell may contain invalid filename characters like %&/. I want the user alerted so they may change the characters in the textbox before proceeding. The corrected textbox string will be saved in another cell. regards Mats |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking characters
Try this Textbox1 Exit event...code...You can add more invalid characters to
the variable strInvalid... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim strInvalid As String, intTemp As Integer strInvalid = "%&/." For intTemp = 1 To Len(strInvalid) If InStr(Me.TextBox1.Text, Mid(strInvalid, intTemp, 1)) 0 Then MsgBox "Invalid character present " & strInvalid Cancel = True End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Mats Samson" wrote: RI've a Userform where a textbox picks its value as a proposal from a worksheet cell when initiating. This cell may contain invalid filename characters like %&/. I want the user alerted so they may change the characters in the textbox before proceeding. The corrected textbox string will be saved in another cell. regards Mats |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking characters
Thank you Jakob,
it worked fine! Cheers Mats "Jacob Skaria" wrote: Try this Textbox1 Exit event...code...You can add more invalid characters to the variable strInvalid... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim strInvalid As String, intTemp As Integer strInvalid = "%&/." For intTemp = 1 To Len(strInvalid) If InStr(Me.TextBox1.Text, Mid(strInvalid, intTemp, 1)) 0 Then MsgBox "Invalid character present " & strInvalid Cancel = True End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Mats Samson" wrote: RI've a Userform where a textbox picks its value as a proposal from a worksheet cell when initiating. This cell may contain invalid filename characters like %&/. I want the user alerted so they may change the characters in the textbox before proceeding. The corrected textbox string will be saved in another cell. regards Mats |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking characters
You don't need to loop through all the characters in the TextBox to do this;
you can make use of the Like operator instead... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Me.TextBox1.Text Like "*[%&/.]*" Then MsgBox "One of these Invalid character are present: %&/." Cancel = True End If End Sub However, for a general solution, I think there are too many invalid characters to list. Using the valid character list at this link... http://support.microsoft.com/kb/177506 Here is a function that can be called to test if a text string is composed of valid characters or not... Function IsValidFileName(FileName As String) As Boolean IsValidFileName = InStr(FileName, "]") 0 Or Not FileName _ Like "*[! 0-9A-Za-z^&'@{}[,$=!#()%.+~_-]*" End Function The OP can then use this function like this... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsValidFileName(Me.TextBox1.Text) Then MsgBox "Invalid character present!" Cancel = True End If End Sub -- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... Try this Textbox1 Exit event...code...You can add more invalid characters to the variable strInvalid... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim strInvalid As String, intTemp As Integer strInvalid = "%&/." For intTemp = 1 To Len(strInvalid) If InStr(Me.TextBox1.Text, Mid(strInvalid, intTemp, 1)) 0 Then MsgBox "Invalid character present " & strInvalid Cancel = True End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Mats Samson" wrote: RI've a Userform where a textbox picks its value as a proposal from a worksheet cell when initiating. This cell may contain invalid filename characters like %&/. I want the user alerted so they may change the characters in the textbox before proceeding. The corrected textbox string will be saved in another cell. regards Mats |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking characters
Thank you Rick,
that works well too. It's no big deal in my case. The value picked to the textbox is a customer order number that will be used later on in the filename for reference. In most cases the invalid character is a : or a /. Will keep your solutions in mind, new things happens all the time! Regards Mats "Rick Rothstein" wrote: You don't need to loop through all the characters in the TextBox to do this; you can make use of the Like operator instead... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Me.TextBox1.Text Like "*[%&/.]*" Then MsgBox "One of these Invalid character are present: %&/." Cancel = True End If End Sub However, for a general solution, I think there are too many invalid characters to list. Using the valid character list at this link... http://support.microsoft.com/kb/177506 Here is a function that can be called to test if a text string is composed of valid characters or not... Function IsValidFileName(FileName As String) As Boolean IsValidFileName = InStr(FileName, "]") 0 Or Not FileName _ Like "*[! 0-9A-Za-z^&'@{}[,$=!#()%.+~_-]*" End Function The OP can then use this function like this... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsValidFileName(Me.TextBox1.Text) Then MsgBox "Invalid character present!" Cancel = True End If End Sub -- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... Try this Textbox1 Exit event...code...You can add more invalid characters to the variable strInvalid... Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim strInvalid As String, intTemp As Integer strInvalid = "%&/." For intTemp = 1 To Len(strInvalid) If InStr(Me.TextBox1.Text, Mid(strInvalid, intTemp, 1)) 0 Then MsgBox "Invalid character present " & strInvalid Cancel = True End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Mats Samson" wrote: RI've a Userform where a textbox picks its value as a proposal from a worksheet cell when initiating. This cell may contain invalid filename characters like %&/. I want the user alerted so they may change the characters in the textbox before proceeding. The corrected textbox string will be saved in another cell. regards Mats |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking characters , non-printable etc. | Excel Discussion (Misc queries) | |||
Checking for special characters in a string | Excel Programming | |||
Checking a cell for only Alphanumerical characters | Excel Worksheet Functions | |||
Checking for Invalid filename characters | Excel Programming | |||
Checking characters using VBA | Excel Programming |