none
assign special keys like Umlaut in VBA RRS feed

  • Question

  • Hello,

    I have an Userform with inputbox , when I give some text contain (umlaut letters like "ä" and space) in this box i get an error message from Microsoft excel :

    "Please only letters without umlauts, numbers and _ use! Characters deleted!"

    how can I stop this message and try to replace the umlaut letters and space with "_" with this code:

    Public Function Umlaut(Anything As Variant) As Variant
       Dim i        As Long
       Dim Ch       As String * 1
       Dim Ch1      As String * 1
       Dim Res      As String
       Dim IsUpCase As Boolean
     
       If IsNull(Anything) Then Umlaut = Null: Exit Function
     
       For i = 1 To Len(Anything)
          Ch = Mid$(Anything, i, 1)
          Ch1 = IIf(i < Len(Anything), Mid$(Anything, i + 1, 1), " ")
          
          IsUpCase = CBool((Asc(Ch1) = Asc(UCase(Ch1))))
          Select Case Asc(Ch)
             Case Asc("Ä"): Res = Res & IIf(IsUpCase, "AE", "Ae")
             Case Asc("Ö"): Res = Res & IIf(IsUpCase, "OE", "Oe")
             Case Asc("Ü"): Res = Res & IIf(IsUpCase, "UE", "Ue")
             Case Asc("ä"): Res = Res & "ae"
             Case Asc("ö"): Res = Res & "oe"
             Case Asc("ü"): Res = Res & "ue"
             Case Asc("ß"): Res = Res & "ss"
    		 Case Asc(" "): Res = Res & "_"
             Case Else:     Res = Res & Ch
          End Select
       Next
       Umlaut = Res
    End Function

    how can I call this code in my userform ?

    Best regards,

    Neven


    • Edited by Req_En Wednesday, June 27, 2018 9:46 AM
    Wednesday, June 27, 2018 9:43 AM

Answers

  • You can use the After Update event of the text box to remove umlauts. For a text box named TextBox1:

    Private Sub TextBox1_AfterUpdate()
        Me.TextBox1.Value = Umlaut(Me.TextBox1.Value)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Req_En Thursday, June 28, 2018 8:34 AM
    Wednesday, June 27, 2018 1:02 PM

All replies

  • This function does not display a message, so the error message must be caused by another part of your code.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 27, 2018 10:09 AM
  • Hello Hans ,

    thank u for your reply , i did not say that this message from this code come , I want to use this code to prevent this message , but i do not know how should i call it from userform


    • Edited by Req_En Wednesday, June 27, 2018 10:52 AM
    Wednesday, June 27, 2018 10:44 AM
  • You can use the After Update event of the text box to remove umlauts. For a text box named TextBox1:

    Private Sub TextBox1_AfterUpdate()
        Me.TextBox1.Value = Umlaut(Me.TextBox1.Value)
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Req_En Thursday, June 28, 2018 8:34 AM
    Wednesday, June 27, 2018 1:02 PM
  • Hello Hans,

    thank u for your reply.

    Best regards,

    Neven

    Thursday, June 28, 2018 8:34 AM