MsgBox Text Based on Value entered in UserForm (Vlookup?)

**silentcarl** · 03-17-2015, 09:10 AM

Hello all,

New user here. I have been searching far and wide for a solution to this problem. I am new to macros, but have been gaining knowledge from searching forums like this one. This is the first time I have felt completely stuck.

I am attempting to use VLookup to place a value in a msgbox based on the value entered in a user form.

Here is what I have so far: (I have tried 100's of different variations of this. sub is ran by a command button)

Sub EditAdd()

MsgBox ("There are currently " & Application.WorksheetFunction.VLookup(TextBox1.Value, Sheets("Inventory").Range("a3:z1000"), 2, False) & " in stock")

End Sub

I get only errors.

My end goal is to have an If statement so that if the item typed in does not exist I get:

Else
            MsgBox "Record not found!", vbExclamation
                End If

My Item Number is in column A and the quantity is in column B.

Thanks in advance for your help!

**Kaper** · 03-17-2015, 09:39 AM

in which module is this code and where is textbox1 this si my first question
and second pretty importatnt is are ItemNumbers real numbers ? text box value is text type so vlookup "number" in numbers will fail. so may be ...VLookup(val(TextBox1.Value), ... would do

**silentcarl** · 03-17-2015, 10:02 AM

Thanks,

I tried:

MsgBox ("There are currently " & Application.WorksheetFunction.VLookup(Val(TextBox1.Value), Sheets("Inventory").Range("a3:z1000"), 2, False) & " in stock")

With no luck.

Sub EditAdd() is located on a new module I inserted.
TextBox1 is located on UserForm1

Regards,

Carl

**silentcarl** · 03-17-2015, 10:50 AM

Here is a simple version of my file. I have only one record in the "Inventory" Sheet.

**silentcarl** · 03-17-2015, 12:59 PM

This is mind blowing to me. It seems so simple. I must be missing something...

I got closer, but no cigar:

Sub EditAdd()

Dim x As Long

x = Application.WorksheetFunction.VLookup(Val(TextBox1.Value), Sheets("Inventory").Range("a3:g1000"), 2, False)

MsgBox ("There are currently " & x & " in stock")

End Sub

Thanks for any additional help.

**Kaper** · 03-17-2015, 02:23 PM

That was exactly my first part of question - as your Sub is in standard module, it knows nothing about such object as TextBox1.
So either let vba know where to look for textbox1:

MsgBox ("There are currently " & Application.WorksheetFunction.VLookup(Val(UserForm1.TextBox1.Value), Sheets("Inventory").Range("a3:z1000"), 2, False) & " in stock")

or (suggested solution) move your whole Sub EditAdd() to userform1 code (where for instance your Private Sub CommandButton5_Click() resides)

PS. Working with vlookup this way can easily cause execution time errors (when no itemnumber is found in column A) - so some error handling would be wise idea like (version to be inserted in UserForm code (for standard module remember about Userform1. prefix)

Sub EditAdd()
On Error Resume Next
MsgBox ("There are currently " & Application.WorksheetFunction.VLookup(Val(TextBox1.Value), Sheets("Inventory").Range("a3:z1000"), 2, False) & " in stock")
If Error <> 0 Then MsgBox TextBox1.Value & " not found", vbCritical
On Error GoTo 0
End Sub

**silentcarl** · 03-17-2015, 02:47 PM

Inventory Control.xlsm

So very close.

THANK YOU!!!

It is working and returns the msgbox how I want.

However, it is giving me the error that it doesn't exist after I accept the msgbox.

How can I fix this? (See attachment)

Regards,

Carl

**silentcarl** · 03-18-2015, 08:04 AM

Closer Still, I will figure this out.
\
Thanks for the help!

Private Sub CommandButton5_Click()

On Error Resume Next
MsgBox ("There are currently " & Application.WorksheetFunction.VLookup(Val(TextBox1.Value), Sheets("Inventory").Range("a3:z1000"), 2, False) & " in stock")
Exit Sub
If Error <> 0 Then MsgBox TextBox1.Value & " not found", vbCritical

On Error GoTo 0



End Sub

**silentcarl** · 03-18-2015, 08:14 AM

Private Sub CommandButton5_Click()

On Error GoTo 3
MsgBox ("There are currently " & Application.WorksheetFunction.VLookup(Val(TextBox1.Value), Sheets("Inventory").Range("a3:z1000"), 2, False) & " in stock")
Exit Sub
3 If Error <> 0 Then MsgBox TextBox1.Value & " not found", vbCritical

On Error GoTo 0



End Sub

That did it.

MsgBox Text Based on Value entered in UserForm (Vlookup?)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Re: MsgBox Text Based on Value entered in UserForm (Vlookup?)

Thread Information

Users Browsing this Thread

Similar Threads

Display msgbox when specific text entered in specific range

[SOLVED] Validate Userform Text Box against data already entered

Code to perform task based on data entered in userform

Concatenate text and checkbox data entered in userform

Vlookup macro based on 2 values returning msgbox

Bookmarks

Bookmarks

Posting Permissions