Poliv
04-09-2020, 11:38 PM
Hello Everyone,
I am a newbie here and would like to thank you all for your valuable input that I read in this forum.
Unfortunately, I couldn't find my answer within the forum, therefore I would like to ask this to all of you.
I have a User Form to update my database.
This "Update Entry" Userform calls the information related to an Order number.
This Order number as always been a number.
But now, our Order numbers integrate letters.
The problem is that my formula doesn't work anymore and I do not know why ...
This is what I wrote (where ComboBox4 is the dropdown list of the Order numbers stored in my DataBase) :
Private Sub ComboBox4_Change()
If Me.ComboBox4.Value <> "" Then
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DataMaster")
Dim n As Integer
n = Application.Match(VBA.CLng(Me.ComboBox4.Value), sh.Range("A:A"), 0)
Me.TextBox1.Value = sh.Range("B" & n).Value
Me.TextBox2.Value = sh.Range("C" & n).Value
Me.DTPicker1.Value = sh.Range("D" & n).Value
If sh.Range("E" & n).Value = "eMail" Then Me.OptionButton1 = True
If sh.Range("E" & n).Value = "Phone" Then Me.OptionButton4 = True
If sh.Range("E" & n).Value = "Customer Thermometer" Then Me.OptionButton3 = True
Me.TextBox3.Value = sh.Range("F" & n).Value
Me.TextBox4.Value = sh.Range("G" & n).Value
Me.ComboBox1.Value = sh.Range("H" & n).Value
Me.ComboBox2.Value = sh.Range("I" & n).Value
Me.ComboBox3.Value = sh.Range("J" & n).Value
Me.TextBox5.Value = sh.Range("K" & n).Value
End If
End Sub
It is working like a charm for all "Numerical Only" Order numbers, but it gives me an :
"Error 13 : Mismatch" for all Order numbers containing a Letter. With an Highlight on the following part :
n = Application.Match(VBA.CLng(Me.ComboBox4.Value), sh.Range("A:A"), 0)
I tried to do the followings :
Change the variable from Integer to Long => No result
To declare the VBA.Clng to Val => No result
The result is that I am stuck ...
If anyone got an idea thank you for sharing.
And thank you in advance for taking the time to read this ... for those who do :biggrin:
Cheers,
I am a newbie here and would like to thank you all for your valuable input that I read in this forum.
Unfortunately, I couldn't find my answer within the forum, therefore I would like to ask this to all of you.
I have a User Form to update my database.
This "Update Entry" Userform calls the information related to an Order number.
This Order number as always been a number.
But now, our Order numbers integrate letters.
The problem is that my formula doesn't work anymore and I do not know why ...
This is what I wrote (where ComboBox4 is the dropdown list of the Order numbers stored in my DataBase) :
Private Sub ComboBox4_Change()
If Me.ComboBox4.Value <> "" Then
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("DataMaster")
Dim n As Integer
n = Application.Match(VBA.CLng(Me.ComboBox4.Value), sh.Range("A:A"), 0)
Me.TextBox1.Value = sh.Range("B" & n).Value
Me.TextBox2.Value = sh.Range("C" & n).Value
Me.DTPicker1.Value = sh.Range("D" & n).Value
If sh.Range("E" & n).Value = "eMail" Then Me.OptionButton1 = True
If sh.Range("E" & n).Value = "Phone" Then Me.OptionButton4 = True
If sh.Range("E" & n).Value = "Customer Thermometer" Then Me.OptionButton3 = True
Me.TextBox3.Value = sh.Range("F" & n).Value
Me.TextBox4.Value = sh.Range("G" & n).Value
Me.ComboBox1.Value = sh.Range("H" & n).Value
Me.ComboBox2.Value = sh.Range("I" & n).Value
Me.ComboBox3.Value = sh.Range("J" & n).Value
Me.TextBox5.Value = sh.Range("K" & n).Value
End If
End Sub
It is working like a charm for all "Numerical Only" Order numbers, but it gives me an :
"Error 13 : Mismatch" for all Order numbers containing a Letter. With an Highlight on the following part :
n = Application.Match(VBA.CLng(Me.ComboBox4.Value), sh.Range("A:A"), 0)
I tried to do the followings :
Change the variable from Integer to Long => No result
To declare the VBA.Clng to Val => No result
The result is that I am stuck ...
If anyone got an idea thank you for sharing.
And thank you in advance for taking the time to read this ... for those who do :biggrin:
Cheers,