 |
|
03-20-2003, 03:33 AM
|
 |
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
2 minor problems
|
Hello
There are two minor issues arising in my userform project.
In the date box I can only enter numbers and "." and "/", which is fine. But if I type in a 3 or more digit number the following error message appears:
Error 13: Types mismatch. This is obvious as I only allow dates and they can never have 3 digits, only xx.yy.zz or xx.yy.zzzz. I could do "on error" but then the macro is finished anyway. I like to add something to the Keypress Event: After max. 2 numbers, there has to follow a "." or "/", then type in another max of 2 numbers, followed by "." or "/", at the end a maximum of 4 digits should be allowed.
After I click on continue, the action is carried out, the userform stays open and the ID Number field is cleared, but the cursor is nowhere to be found. The ID Number field should be active, the cursor should be in there, so user can enter another number straight away without having to click on the field again.
Thank you!
|
|
03-20-2003, 06:19 AM
|
|
Just another Excel nerd
Retired Moderator
* Guru *
|
|
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,624
|
|
|
For your second question, txtID.Setfocus should do it.
For your first question, you'll need to get a little more creative and you'll have to decide how many variations you want to allow the user to type.
|
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
|
03-20-2003, 07:05 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
Thank you
More creative? It should just be possible to enter xx.yy.zz and xx.yy.zzzz, which means after 2 numbers, the program waits for a ".", everything else is blocked.
My "boss" suggested the following today:
When the userform is opened the box "Today's Date" should be clicked on by default. I think I have to do OptionButton1.Value = True in the userform open event. Edit: Not open, but initialize:
Private Sub UserForm_Initialize()
HeutigesDatum.Value = True
End Sub
When a user types a different date into the "Different Date" textbox, then the chkbox "Different Date" should automatically be clicked on. I think I have to open the Sub DifferentDate.TextChange for that. Edit: True:
Private Sub AnderesDatumText_Change()
AnderesDatum.Value = True
End Sub
The ID Number field is already blocked for everything but numbers, this should also happen after 9 digits are entered, so no more than 9 digits are entered.
A similar thing should happen in Excel, if the value in A1 is longer than 9 digits and I run a macro, an error message should appear and exit sub. Edit: This can be done with the len Function. If len(IDnumber) >9 then stop.
For one macro I used an input box, here is the code
Code:
Sub Anzeigen()
Dim SuchZahl As Double
Dim i As Long
On Error GoTo errorhandler
SuchZahl = InputBox("Ident Nummer eingeben: ")
Sheets("Daten").Activate
For i = 3 To Range("A65536").End(xlUp).Row
If Cells(i, 1).Value = SuchZahl Then
MsgBox "Ident Nummer in Zeile: " & i
Sheets("Eintrag").Range("A2") = Sheets("Daten").Cells(i, 1)
For z = 1 To 11
Sheets("Eintrag").Cells(z + 4, 2) = Sheets("Daten").Cells(i, z + 1)
Next z
For y = 0 To 11
For x = 1 To 5
Worksheets("Eintrag").Cells(3 + y, x + 4) = Worksheets("Daten").Cells(i, (x + 12 + (y * 5)))
Next x
Next y
Sheets("Eintrag").Activate
Exit Sub
End If
Next i
MsgBox "Ident Nummer wurde nicht gefunden."
Sheets("Eintrag").Activate
Exit Sub
errorhandler:
MsgBox "Suchbegriff fehlerhaft."
End Sub
I introduced the "on error" in case the user enters nothing or letters. It would be good, if this is blocked to numbers and 9 digits as well.
There are two buttons: OK and ESC, a click on ESC also results in the "On Error" command to come on, that shouldn't happen, it should just quit. Thanks a lot.
It's cool to find out so many things myself.  Sorry for too much and too early asking. |
Last edited by Artifi; 03-20-2003 at 08:47 AM.
|
03-20-2003, 09:56 AM
|
|
Just another Excel nerd
Retired Moderator
* Guru *
|
|
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,624
|
|
I didn't realize how tricky that would be. The problem comes when the user uses editing keys (arrows, backspace, delete). To get around some of that, I've disabled the arrow keys.
I've tried to comment quite liberally so that you know which code is doing what
Code:
Option Explicit
Function CountCharsInString(StringToCount As String, StringToSearch As String) As Integer
Dim intCount As Integer
Dim X As Integer
X = 0
While InStr(X + 1, StringToSearch, StringToCount) > 0
intCount = intCount + 1
X = InStr(X + 1, StringToSearch, StringToCount)
Wend
CountCharsInString = intCount
End Function
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Disallow the arrow keys
If KeyCode >= 37 And KeyCode <= 40 Then
KeyCode = 0
End If
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim intCharCount As Integer 'The number of characters since the last separator
Dim intSeparatorCount As Integer 'The number of separators that have been entered
Dim intYearCount As Integer 'The current number of digits in the "year" part
Dim intLastSepPlace As Integer 'The place of the last separator
Dim strCurrent As String 'The current string in the textbox
strCurrent = TextBox1.Text
intLastSepPlace = InStrRev(strCurrent, ".")
If intLastSepPlace = 0 Then intLastSepPlace = InStrRev(strCurrent, "/")
intCharCount = Len(strCurrent) - intLastSepPlace
intSeparatorCount = CountCharsInString("/", strCurrent) + CountCharsInString(".", strCurrent)
'First, we only want to allow numbers, periods, or forward slashes (/)
If (KeyAscii < Asc("0") Or KeyAscii > Asc("9")) And KeyAscii <> Asc(".") And KeyAscii <> Asc("/") Then
KeyAscii = 0
Else
'If intSeparatorCount = 2, then don't allow any more separators
If intSeparatorCount = 2 Then
intYearCount = Len(strCurrent) - intLastSepPlace
'Plus, we only want to allow 4-digit years
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
KeyAscii = 0
ElseIf intYearCount = 4 Then
KeyAscii = 0
End If
Else
'Now, check intCharCount
'If intCharCount = 2, then only allow "." or "/"
If intCharCount = 2 Then
If KeyAscii <> Asc(".") And KeyAscii <> Asc("/") Then
KeyAscii = 0
End If
End If
End If
End If
End Sub
|
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
|
03-20-2003, 10:17 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
Wow! Heftig. I'll leave that for tomorrow.
I found a way solving the problem with IsDate
Code:
'Erster Teil: Datumsabfrage
If HeutigesDatum.Value = True Then
datum = Date
part1 = True
End If
If IsDate(AnderesDatumText.Text) = False Then
MsgBox ("Fehler: Datumsformat fehlerhaft.")
AnderesDatumText.SetFocus
Exit Sub
End If
If AnderesDatum.Value = True Then
datum = DateValue(AnderesDatumText.Text)
part1 = True
End If
If HeutigesDatum.Value = False And AnderesDatum.Value = False Then
MsgBox ("Kein Datum angekreuzt"), , "Fehler"
part1 = False
End If
This way, the program gives an error message if a user tries to type in 123, 123.12., 12.123., 12.12.20021 being the date, so that is good. It doesn't however if I forget the dot: 12.123 or 123.12, then it changes it to 1.12.123 and placing this date into a certain cell won't work anymore.
How do restrict the entry to 9 digits, the 10th digit would make KeyAscii = 0 in a userform and in an inputbox?
Edit: If Len(IDNummer.Text) > 8 Then KeyAscii = 0 'for a userform, what will it be for an input box? |
Last edited by Artifi; 03-21-2003 at 04:00 AM.
|
03-21-2003, 03:55 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
|
Hello
I tried your code but an error appears at this stage:
It says that it doesn't know this sub: "InStrRev", is this a command, because F1 couldn't find anything about it?
Edit: I have just heard that InStrRev only works from Office 2000, is there an alternative way in 97, otherwise I have to leave this possibility. Thanks
|
Last edited by Artifi; 03-21-2003 at 05:30 AM.
|
03-21-2003, 05:31 AM
|
 |
Senior Contributor
|
|
Join Date: Apr 2002
Location: glbTheWorld.Europe.UK
Posts: 1,201
|
|
|
Artifi,
With Access you can't use the dot (.) character as a field separator for dates. You can use space and slash (/).
Regards,
Paul.
|
|
03-21-2003, 05:35 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
Quote:
|
Originally Posted by Mandelbrot
With Access you can't use the dot (.) character as a field separator for dates. You can use space and slash (/).
|
Thanks, but I am using Excel and there I can use the dot (.).
Does this count for every Access version, because in (the old )Europe we use dots to seperate dates, hence it should work in the european versions of Office. |
|
03-21-2003, 05:41 AM
|
|
Senior Contributor
|
|
Join Date: Apr 2002
Location: glbTheWorld.Europe.UK
Posts: 1,201
|
|
Hmmm...
I use the UK version and it proves difficult there.
LOL - didn't realise you were using Excel! Appologies! 
Paul. |
|
03-21-2003, 05:45 AM
|
|
Senior Contributor
|
|
Join Date: Apr 2002
Location: glbTheWorld.Europe.UK
Posts: 1,201
|
|
|
Artifi,
Have you tried adding the Date and Time picker control instead?
This object is already formatted for you, and presents a drop down so the user can pick from a list.
Regards,
Paul.
|
|
03-21-2003, 05:49 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
|
Someone told me about the date picker already but apparently it is only available from version 2000. If not, please tell me how to use it.
There is one alternative method I could use, something like this
If TextBox1 Format(TextBox1, "dd.mm.yyyy") And TextBox1 Format(TextBox1, "d.mm.yyyy") And TextBox1 Format(TextBox1, "dd.m.yyyy") And TextBox1 Format(TextBox1, "d.m.yyyy") And TextBox1 Format(TextBox1, "dd.mm.yy") And TextBox1 Format(TextBox1, "d.mm.yy") And TextBox1 Format(TextBox1, "dd.m.yy") And TextBox1 Format(TextBox1, "d.m.yy") Then
MsgBox "Fehler: Datumsformat"
Exit Sub
End If
But there are some mistakes in there.
|
|
03-21-2003, 05:58 AM
|
|
Senior Contributor
|
|
Join Date: Apr 2002
Location: glbTheWorld.Europe.UK
Posts: 1,201
|
|
|
Artifi,
Are you using the Microsoft Masked Edit Control?
Paul.
|
|
03-21-2003, 06:00 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
???
|
I don't know what that is. I suppose not.
|
|
03-21-2003, 06:19 AM
|
|
Senior Contributor
|
|
Join Date: Apr 2002
Location: glbTheWorld.Europe.UK
Posts: 1,201
|
|
Try it - you can specify formats for your input like '09.09.0099' where 0 is a required character, 9 is a non required character and (in this case) . is the field separator. You can get this by adding it to the toolbox (from the context menu) - "Microsoft Masked Edit Control".
Have a go - it's fun!
Paul. |
|
03-21-2003, 06:39 AM
|
|
Just another Excel nerd
Retired Moderator
* Guru *
|
|
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,624
|
|
Quote:
|
Originally Posted by Artifi
Edit: I have just heard that InStrRev only works from Office 2000, is there an alternative way in 97, otherwise I have to leave this possibility. Thanks
|
Try using this function instead:
Code:
Function MyInstrRev(strToFind As String, strToSearch As String) As Integer
Dim X As Integer
X = 0
While InStr(X + 1, strToSearch, strToFind) > 0
X = InStr(X + 1, strToSearch, strToFind)
Wend
MyInstrRev = X
End Function
|
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
|
03-21-2003, 07:01 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
Quote:
|
Originally Posted by Mill
Try using this function instead:
|
Thanks, where do I put this into your previous code and what do I take out instead; or how does the complete updated code look like? |
|
03-21-2003, 07:04 AM
|
|
Just another Excel nerd
Retired Moderator
* Guru *
|
|
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,624
|
|
Quote:
|
Originally Posted by Artifi
Thanks, where do I put this into your previous code and what do I take out instead; or how does the complete updated code look like?
|
Paste that code into a Code Module or into the same form module that your textboxes are in.
Just replace InstrRev with MyInstrRev so that it uses the function I just wrote instead of the original function. |
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
|
03-21-2003, 07:25 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
|
It half works. I can only put 22 and then ., but I can't just type 2. and then another number. I can also type 22.2222222222222 and 22.12.222222222222222...
Sorry, I forgot to say that, if someone writes in the 1. of January, then he should be allowed t write 1.1.2003 and should not be forced to write 01.01.2003
Naja...
|
|
03-21-2003, 09:16 AM
|
|
Centurion
|
|
Join Date: Mar 2003
Posts: 110
|
|
Sorry, but the restriction isn't only xx.yy.zzzz, they are as follows:
dd.mm.yyyy
d.mm.yyyy
dd.m.yyyy
d.m.yyyy
dd.mm.yy
d.mm.yy
dd.m.yy
d.m.yy
I think I could do this with
Code:
If intCharCount > 2 Then
If KeyAscii <> Asc("." ) And KeyAscii <> Asc("/" ) Then
KeyAscii = 0
and
Code:
'Plus, we only want to allow 2 or 4-digit years
If KeyAscii < Asc("0" ) Or KeyAscii > Asc("9" ) Then
KeyAscii = 0
ElseIf intYearCount = 4 or intYearCount = 2 Then
KeyAscii = 0
End If
Right? Edit: No, this way I can type 2.. or 222, you did this by counting. Bsss, to hard. Lets forget it unless you really want to.
But the problem with xx.yyyyyyyyyyyyyy and xx.yy.zzzzzzzzzzz has to be solved first.
--------------------------
I have shown code of a different method a few posts up, but <> was missing. Here is the complete code
If TextBox1 <> Format(TextBox1, "dd.mm.yyyy" ) And TextBox1 <> Format(TextBox1, "d.mm.yyyy" ) And TextBox1 <> Format(TextBox1, "dd.m.yyyy" ) And TextBox1 <> Format(TextBox1, "d.m.yyyy" ) And TextBox1 <> Format(TextBox1, "dd.mm.yy" ) And TextBox1 <> Format(TextBox1, "d.mm.yy" ) And TextBox1 <> Format(TextBox1, "dd.m.yy" ) And TextBox1 <> Format(TextBox1, "d.m.yy" ) Then
MsgBox "Fehler: Datumsformat"
Exit Sub
End If
where Textbox1 = AnderesDatumText.Text
It should have worked with this, but it doesn't. The message box appears anyway, and if I type in 11.11.2002 or 01.01.2000 then I get a debug error. |
Last edited by Artifi; 03-21-2003 at 09:25 AM.
|
03-21-2003, 11:18 AM
|
|
Just another Excel nerd
Retired Moderator
* Guru *
|
|
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,624
|
|
I figured it out. My function MyInstrRev had the arguments reversed from the VBA function InstrRev. Here is now my entire code. I believe it will do what you want:
Code:
Option Explicit
Function CountCharsInString(StringToCount As String, StringToSearch As String) As Integer
Dim intCount As Integer
Dim X As Integer
X = 0
While InStr(X + 1, StringToSearch, StringToCount) > 0
intCount = intCount + 1
X = InStr(X + 1, StringToSearch, StringToCount)
Wend
CountCharsInString = intCount
End Function
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Disallow the arrow keys
If KeyCode >= 37 And KeyCode <= 40 Then
KeyCode = 0
End If
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim intCharCount As Integer 'The number of characters since the last separator
Dim intSeparatorCount As Integer 'The number of separators that have been entered
Dim intYearCount As Integer 'The current number of digits in the "year" part
Dim intLastSepPlace As Integer 'The place of the last separator
Dim strCurrent As String 'The current string in the textbox
strCurrent = TextBox1.Text
intLastSepPlace = MyInstrRev(strCurrent, ".")
If intLastSepPlace = 0 Then intLastSepPlace = MyInstrRev(strCurrent, "/")
intCharCount = Len(strCurrent) - intLastSepPlace
intSeparatorCount = CountCharsInString("/", strCurrent) + CountCharsInString(".", strCurrent)
'First, we only want to allow numbers, periods, or forward slashes (/)
If (KeyAscii < Asc("0") Or KeyAscii > Asc("9")) And KeyAscii <> Asc(".") And KeyAscii <> Asc("/") Then
KeyAscii = 0
Else
'If the last character was a separator, then don't allow a separator again
If intCharCount = 0 Then
If KeyAscii = Asc(".") Or KeyAscii = Asc("/") Then
KeyAscii = 0
Exit Sub
End If
End If
'If intSeparatorCount = 2, then don't allow any more separators
If intSeparatorCount = 2 Then
intYearCount = Len(strCurrent) - intLastSepPlace
'Plus, we only want to allow 4-digit years
If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
KeyAscii = 0
ElseIf intYearCount = 4 Then
KeyAscii = 0
End If
Else
'Now, check intCharCount
'If intCharCount = 2, then only allow "." or "/"
If intCharCount = 2 Then
If KeyAscii <> Asc(".") And KeyAscii <> Asc("/") Then
KeyAscii = 0
End If
End If
End If
End If
End Sub
Function MyInstrRev(strToSearch As String, strToFind As String) As Integer
Dim X As Integer
If strToFind = "" Or strToSearch = "" Then Exit Function
X = 0
While InStr(X + 1, strToSearch, strToFind) > 0
X = InStr(X + 1, strToSearch, strToFind)
Wend
MyInstrRev = X
End Function
|
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
