inputbox arab name problem when father, not when mother or both

littlepete

littlepete

Well-known Member
Joined
Mar 26, 2015
Messages
507
Office Version
  1. 365
Platform
  1. Windows
hello :)

i have a perfectly well working inputbox asking if you would like to see the parents / mother / father of the person in the activerow.
no problems with option 2 and 3 and no problem with choosing mother in option 1.
is an inputbox keeping some kind of memory that could make the choice to see only the father ('s row) a problem?
each row is a person: the name is in column B. mother's name in CP; father's name in CQ. if it is a name in non latin letters,
the transcription is in CS and CT.
the inputbox finds the column of the activerow for mother/father's name then search for that name (identical) in column B.
all is working well, except for choosing father's name when both are available.

thank you for your opinion and advice !!!
this is the code:


VBA Code: 
Sub vindouders() ' ------------------------------------------------------------------------------------------------- [ F4 ]
Dim keuze As Variant
Dim zoekmam As Range
Dim zoekpap As Range
Dim zoekma As Variant
Dim zoekpa As Variant
Dim dezerij As Long
Dim aantalcontacten As Variant
aantalcontacten = Worksheets("gegevens").Cells.SpecialCells(xlCellTypeLastCell).Row - 4
dezerij = ActiveCell.Row
zoekma = "*" & Range("cp" & dezerij).Value & "*"
Set zoekmam = Range("rngpersoon").Find(zoekma, , xlValues, xlWhole)
zoekpa = "*" & Range("cq" & dezerij).Value & "*"
Set zoekpap = Range("rngpersoon").Find(zoekpa, , xlValues, xlWhole)
' ========================================================== geen ma geen pa
If zoekmam Is Nothing And zoekpap Is Nothing Then
MsgBox Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value) & Chr(10) & _
"heeft geen ouders staan in deze lijst. "
Exit Sub
End If
' ========================================================== wel ma wel pa
If Range("cp" & dezerij).Value <> "" And Range("cq" & dezerij).Value <> "" Then
If Range("cs" & dezerij).Value <> "" Or Range("ct" & dezerij).Value <> "" Then
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De ouders bekijken van " & Chr(10) & Range("p" & dezerij).Value & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("cs" & dezerij).Value & " te gaan" & Chr(10) & _
    "tik 2 om naar " & Range("ct" & dezerij).Value & " te gaan" & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value), , 11000, 10000)
Else
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De ouders bekijken van " & Chr(10) & Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value) & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("cp" & dezerij).Value & " te gaan" & Chr(10) & _
    "tik 2 om naar " & Range("cq" & dezerij).Value & " te gaan" & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value), , 11000, 10000)
End If
Select Case keuze
    Case 0
    GoTo einde
    Case 1
        Range(zoekmam.Address).Select
        Exit Sub
    Case 2
        Range(zoekpap.Address).Select ' ==================================================== hier zit altijd een fout ==================
        Exit Sub
End Select
End If
[...]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Range(zoekmam.Address).Select = zoekmam.select ....

you start with searching "zoekmam" & "zoekpap" (with the content of CP and CQ), then ask to choice with the content of other cells and finally go to 1 of the 2 cells you already knew.

Can't you say, something like 1. goto zoekpap.value, also known as "blablabla" or 2. goto zoekmam.value, also known as "another blablabla".

If you didn't find a zoekpap or a zoekmam, or neither then there is no choice, go without asking.

Make your macro less complex, easier.
 
Upvote 0
hello :)

an inputbox cannot write arab, so before that i need to verify if mom or dad are arab, then transcribe the names if necessary...
the original was going directly and that works perfectly, only: on the inputbox you only see ??????? ...
that is solved this way, only in case of 1 of the three choices and being arab: it stops...
 
Upvote 0
anyone an idea why option 2 and 3 are ok and option one choice one ok but choice 2 never working?
the zoekpap (search dad) always gives an error when name is not in latin ...
 
Upvote 0
can someone pleazzzzze help me i'm stuck, it's sunday there is nothing to do and i cannot do anything on excel now either :(
thank you...
 
Upvote 0
As it was also a lazy sunday, i googled a little bit and i found this.

[SOLVED] Arabic characters gives ASCII code 63

Dear all, I have imported some Arabic text into excel. When I convert them to ASCII, I get the value 63 for all characters. What happened? Do I need to install any language packs? Regards, Julian
www.excelforum.com
But ..., i could apply it !
so, sorry
 
Upvote 0
BSALV said:
As it was also a lazy sunday, i googled a little bit and i found this.

[SOLVED] Arabic characters gives ASCII code 63

Dear all, I have imported some Arabic text into excel. When I convert them to ASCII, I get the value 63 for all characters. What happened? Do I need to install any language packs? Regards, Julian
www.excelforum.com
But ..., i could apply it !
so, sorry
Click to expand...
what did you apply ??? my problem is not with the arab... because kids and mom in an arab family are arab too...
i have no idea what goes wrong...
 
Upvote 0
is CQx for pap empty or not, it's without sense to search "**"
VBA Code: 
     zoekpa = "*" & Range("cq" & dezerij).Value & "*"
     If Len(zoekpa) > 2 Then Set zoekpap = Range("rngpersoon").Find(zoekpa, , xlValues, xlWhole)
later you check if zoekpap was found
VBA Code: 
   Case 2
                    If zoekpap Is Nothing Then
                         MsgBox "niets gevonden voor pap"
                    Else
                         zoekpap.Select                         ' ==================================================== hier zit altijd een fout ==================
                    End If
                    Exit Sub
 
Upvote 0
BSALV said:
is CQx for pap empty or not, it's without sense to search "**"
VBA Code: 
     zoekpa = "*" & Range("cq" & dezerij).Value & "*"
     If Len(zoekpa) > 2 Then Set zoekpap = Range("rngpersoon").Find(zoekpa, , xlValues, xlWhole)
later you check if zoekpap was found
VBA Code: 
   Case 2
                    If zoekpap Is Nothing Then
                         MsgBox "niets gevonden voor pap"
                    Else
                         zoekpap.Select                         ' ==================================================== hier zit altijd een fout ==================
                    End If
                    Exit Sub
Click to expand...

hello :)

thank you for your answer
my inputbox vba starts with two questions: is there a mom and and, and are there non latin names...
do i need to ask that question again in the case 1 case 2 ?
i find it strange that the parts with only a mom ; only a dad ; and choosing mom out of the two do work well...
i'll put the complete inputbox vba here, so you can check :

VBA Code: 
Sub vindouders() ' ------------------------------------------------------------------------------------------------- [ F4 ]
Dim keuze As Variant
Dim zoekmam As Variant
Dim zoekpap As Variant
Dim zoekma As Variant
Dim zoekpa As Variant
Dim dezerij As Long
Dim kind As String
Dim aantalcontacten As Variant
aantalcontacten = Worksheets("gegevens").Cells.SpecialCells(xlCellTypeLastCell).Row - 4
dezerij = ActiveCell.Row
If Range("p" & dezerij).Value <> "" Then
    kind = Range("p" & dezerij).Value
Else
    kind = Range("m" & dezerij).Value & " " & Range("n" & dezerij).Value
End If
zoekma = "*" & Range("cp" & dezerij).Value & "*"
Set zoekmam = Range("rngpersoon").Find(zoekma, , xlValues, xlWhole)
zoekpa = "*" & Range("cq" & dezerij).Value & "*"
Set zoekpap = Range("rngpersoon").Find(zoekpa, , xlValues, xlWhole)
On Error GoTo einde
' ========================================================================= geen ma geen pa
If zoekmam Is Nothing And zoekpap Is Nothing Then
MsgBox Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value) & Chr(10) & _
"heeft geen ouders staan in deze lijst. "
Exit Sub
End If
' ========================================================================= wel ma wel pa
If Range("cp" & dezerij).Value <> "" And Range("cq" & dezerij).Value <> "" Then
' ========================================================== ander alfabet
If Range("cs" & dezerij).Value <> "" Or Range("ct" & dezerij).Value <> "" Then
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De ouders bekijken van " & Chr(10) & Range("p" & dezerij).Value & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("cs" & dezerij).Value & " te gaan" & Chr(10) & _
    "tik 2 om naar " & Range("ct" & dezerij).Value & " te gaan" & Chr(10) & Chr(10) & _
    "om nadien " & kind & " terug zelf te selecteren, druk op [ F2 ]." & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value), , 11000, 10000)
Else
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De ouders bekijken van " & Chr(10) & Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value) & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("cp" & dezerij).Value & " te gaan" & Chr(10) & _
    "tik 2 om naar " & Range("cq" & dezerij).Value & " te gaan" & Chr(10) & Chr(10) & _
    "om nadien " & kind & " terug zelf te selecteren, druk op [ F2 ]." & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value), , 11000, 10000)
End If
Select Case keuze
    Case 0
    GoTo einde
    Case 1
        Range(zoekmam.Address).Select
        Exit Sub
    Case 2
        Range(zoekpap.Address).Select
        Exit Sub
 End Select
End If
' ========================================================================= alleen moeder
If Range("cp" & dezerij).Value <> "" And Range("cq" & dezerij).Value = "" Then
' ========================================================== ander alfabet
If Range("cs" & dezerij).Value <> "" Then
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De moeder bekijken van " & Chr(10) & kind & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("cs" & dezerij).Value & " te gaan" & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & kind, , 11000, 10000)
Else
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De moeder bekijken van " & Chr(10) & kind & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("cp" & dezerij).Value & " te gaan" & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & kind, , 11000, 10000)
End If
Select Case keuze
    Case 0
    GoTo einde
    Case 1
        Range(zoekmam.Address).Select
        Exit Sub
End Select
End If
' ========================================================================= alleen vader
If Range("cp" & dezerij).Value = "" And Range("cq" & dezerij).Value <> "" Then
' ========================================================== ander alfabet
If Range("ct" & dezerij).Value <> "" Then
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De vader bekijken van " & Chr(10) & kind & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("ct" & dezerij).Value & " te gaan" & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & kind, , 11000, 10000)
Else
keuze = InputBox(Chr(10) & _
    "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
    "De vader bekijken van " & Chr(10) & kind & " : " & Chr(10) & _
     "______________________________________________________" & Chr(10) & _
    "tik 0 om dit scherm te verlaten." & Chr(10) & _
    "tik 1 om naar " & Range("cq" & dezerij).Value & " te gaan" & Chr(10) & _
    Chr(10), "OUDERS BEKIJKEN VAN " & kind, , 11000, 10000)
End If
Select Case keuze
    Case 0
    GoTo einde
    Case 1
        Range(zoekpap.Address).Select
        Exit Sub
End Select
End If
' ========================================================== einde inputbox
einde:
'Cells(dezerij, "b").Select
End Sub
 
Upvote 0
"on error ..." makes it difficult to find errors, prevent them !
now, only the case with both parents is worth to examine.
VBA Code: 
Sub vindouders()                                                ' ------------------------------------------------------------------------------------------------- [ F4 ]
     Dim keuze, zoekmam, zoekpap, zoekma, zoekpa, aantalcontacten     ' is the same as Variant for all of them
     Dim dezerij As Long, kind As String

     aantalcontacten = Worksheets("gegevens").Cells.SpecialCells(xlCellTypeLastCell).Row - 4
     dezerij = ActiveCell.Row
     If Range("p" & dezerij).Value <> "" Then
          kind = Range("p" & dezerij).Value
     Else
          kind = Range("m" & dezerij).Value & " " & Range("n" & dezerij).Value
     End If
     zoekma = Range("cp" & dezerij).Value                       'without "*"
     If Len(zoekma) > 0 Then Set zoekmam = Range("rngpersoon").Find("*" & zoekma & "*", , xlValues, xlWhole)
     zoekpa = Range("cq" & dezerij).Value
     If Len(zoekpa) > 0 Then Set zoekpap = Range("rngpersoon").Find("*" & zoekpa & "*", , xlValues, xlWhole)
      
      If zoekmam Is Nothing And zoekpap Is Nothing Then
          MsgBox Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value) & Chr(10) & "heeft geen ouders staan in deze lijst. "
          Exit Sub
     End If

     If zoekmam Is Nothing Then Application.Goto zoekpap.Offset(, 1 - zoekpap.Column), 1     'just daddy, without asking goto 1e cell van de rij van pap
     If zoekpap Is Nothing Then Application.Goto zoekmam.Offset(, 1 - zoekmam.Column), 1     'just mammy, ithout asking goto 1st cell van de rij van pap

     MsgBox "you still have mammy and daddy", vbInformation

     ' ========================================================== ander alfabet
     If Range("cs" & dezerij).Value <> "" Then zoekpa = Range("cs" & dezerij).Value
     If Range("ct" & dezerij).Value <> "" Then zoekma = Range("ct" & dezerij).Value

     keuze = InputBox(Chr(10) & _
                      "Deze lijst bevat " & aantalcontacten & " contacten." & Chr(10) & Chr(10) & _
                      "De ouders bekijken van " & Chr(10) & Range("p" & dezerij).Value & " : " & Chr(10) & _
                      "______________________________________________________" & Chr(10) & _
                      "tik 0 om dit scherm te verlaten." & Chr(10) & _
                      "tik 1 om naar " & zoekpa & " te gaan" & Chr(10) & _
                      "tik 2 om naar " & zoekma & " te gaan" & Chr(10) & Chr(10) & _
                      "om nadien " & kind & " terug zelf te selecteren, druk op [ F2 ]." & Chr(10) & _
                      Chr(10), "OUDERS BEKIJKEN VAN " & Range("m" & dezerij).Value & " " & UCase(Range("n" & dezerij).Value), , 11000, 10000)

     Select Case keuze
          Case 0: MsgBox "einde"
          Case 1: zoekmam.Select
          Case 2: zoekpap.Select
          Case Else: MsgBox "this was not an option", vbCritical
     End Select

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

littlepete
  • Solved
putting new data in all columns in new row
Replies
2
Views
412
littlepete
littlepete
littlepete
  • Question Question
inputbox not showing value of cel to adjust and does show when in next cel and going back
Replies
2
Views
443
Fluff
Fluff
littlepete
  • Solved
column header in inputbox title
Replies
7
Views
609
Micron
M

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top