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!

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.

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

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.

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


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

Wow! Heftig. I'll leave that for tomorrow.

I found a way solving the problem with IsDate


'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?

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

Artifi,


With Access you can't use the dot (.) character as a field separator for dates. You can use space and slash (/).


Regards,
Paul.

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.

Hmmm...


I use the UK version and it proves difficult there.

LOL - didn't realise you were using Excel! Appologies! ;)


Paul.

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.

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.

Artifi,


Are you using the Microsoft Masked Edit Control?


Paul.

I don't know what that is. I suppose not.

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.

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:


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

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?

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.

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...

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


If intCharCount > 2 Then
If KeyAscii <> Asc("." ) And KeyAscii <> Asc("/" ) Then
KeyAscii = 0


and


'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.

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:


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

Thank you, this works now.

However there a two minor issues. As you know the textbox where the user types in the date is already filled with text (dd.mm.yyyy) to remind the user how he has to type the date. If you highlight this text and press Del you delete that text so you can type in the date. It is usually also possible to delete highlited text by pressing any other key and then that key replaces that highlighted text. It doesn't here anymore. I know that is very minor...

The second thing: I can type in 12.12.123 and 12.12.1212, those are kinda valid dates, although this is long ago; the IsDate function returns it as a valid date, but at this line I get the error 1004:

Sheets("Daten").Cells(xcord, ycord) = datum

I don't get this with a "new" date like 12.12.95 or 12.12.1995.

Can't you just do error-checking when the user hits the Enter button (or whatever button)?

Alternatively, you could set up three textboxes - one for month, one for day, and one for year.

If you set the MaxLength = 2 and AutoTab = True, then you can reduce the error-checking that you have to do.

See my attachment for an example.

It's just the form portion, so you'll have to import it into your Excel file.