VLookUp ComboBox input to update TextBox values

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have the code below to get the lookup amount of the selected client from sheet5, but I don't know what I did wrong since it's not showing the amount on the "txtTotal, can you please help me to get the amount of the selected client?

Note: the Amounts on column "D" contains formulas

Code:
Private Sub cmdContact_Click()
On Error Resume Next
If WorksheetFunction.CountIf(Sheet5.Range("B:B"), Me.cboName.Text) = 0 Then
MsgBox "This client is not exist"
Me.cboName.Value = "Cash Client"
Exit Sub
End If
With Me
.txtTotal.Value = Application.WorksheetFunction.VLookup(Me.cboName.Text, Sheet5.Range("A1").CurrentRegion, 4, 0)
End With
End Sub


ABCD
1IDNamePhoneAmount
2100Cash Client2000
3101Nadim3000
4102Rami4000

<tbody>
</tbody>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
the right for .txtTotal.Value is
Code:
Code:
.txtTotal.Value = Application.WorksheetFunction.VLookup(Me.cboName.Text, Sheet5.Range("A1").CurrentRegion.[SIZE=2][COLOR=#ff0000]Offset(,1)[/COLOR][/SIZE], 4, 0)

 
Last edited:
Upvote 0
Hi NDMDRB,

I usually avoid using excel functions within VBA, since VBA has its own ways on handling such cases, namely using FIND method.

For example:

Code:
Private Sub cmdContact_Click()

On Error Resume Next


With Sheet1.Range("B2:B4")
    Set fvalue = .Find(What:=cmdContact.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
End With


If fvalue Is Nothing Then
    MsgBox "This client does not exist"
    Me.cmdContact.Value = "Cash Client"
    Exit Sub
End If


With Me
    .txtTotal.Value = fvalue.Offset(, 2).Value
End With


End Sub

Hope this will help you.

Br
pella88
 
Upvote 0
Thank you so much Salim Hasan,
Can you please add a format for the "txtTotal to show the amount as it is in cells column "D"

Cells in Column "D" formats looks like "2,000 L.L."
Here is the exact format: _-* #,##0 [$L.L.‏-ar-LB]_-;-* #,##0 [$L.L.‏-ar-LB]_-;_-* "-" [$L.L.‏-ar-LB]_-;_-@_-
 
Upvote 0
Thank you Salim Hasan,

I think that I didn't explain well,

I have the cells format already, but what I need is to add this format to the textbox "txtTotal" to be shown in the userform as "2,000 L.L." or if this is not possible just make it "2,000"
 
Upvote 0
Code:
dim t,x
x=[COLOR=#333333]" #.00  L.L; - #.00  L.L;""Zero"" L.L"[/COLOR]
t[COLOR=#333333].txtTotal.Value
[/COLOR]t=t.NumberFormat(x)
[COLOR=#333333]txtTotal.Value=t[/COLOR]
 
Upvote 0
try this
Code:
Sub test()
Dim t, x
x = " #.00  L.L; - #.00  L.L;""Zero"" L.L"
t = Format$([COLOR=#333333]txtTotal.Value[/COLOR], x)
[COLOR=#333333]txtTotal[/COLOR][COLOR=#333333].Value[/COLOR] = t
End Sub
 
Last edited:
Upvote 0
Can you please let me know in which subroutine should I put this code
 
Upvote 0
the complete macro
Code:
Private Sub cmdContact_Click()
On Error Resume Next
If WorksheetFunction.CountIf(Sheet5.Range("B:B"), Me.cboName.Text) = 0 Then
MsgBox "This client is not exist"
Me.cboName.Value = "Cash Client"
Exit Sub
End If
With Me

.txtTotal.Value = Application.WorksheetFunction.VLookup(Me.cboName.Text, Sheet5.Range("A1").CurrentRegion, 4, 0)
End With

Dim t, x
x = " #.00  L.L; - #.00  L.L;""Zero"" L.L"
t = Format$([COLOR=#333333]txtTotal.Value[/COLOR], x)
[COLOR=#333333]txtTotal[/COLOR][COLOR=#333333].Value[/COLOR] = tEnd Sub</pre>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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