"object required" error vlookup

[conception_native_0123]

i getting this error for no reason. my return variable a variant like microsoft says to do here

WorksheetFunction.VLookup method (Excel)

Office VBA reference topic

docs.microsoft.com

this is all my code on combo box. and my file attached. what am i doing wrong? thank you.

Private Sub Choose_Change()
Dim tChoice As String
Dim numPos As Long
Dim endResponse As Long
Dim lenResponse As Long
Dim nChoice As String
Dim iName As String
Dim rMessage As Variant
Dim sMessage As String
Const errMessage1 As String = "No name was entered.  Please try again."
Const response As String = "RESPONSE"
Const space As String = " "

tChoice = cmbChoose.Value
lenResponse = Len(response)
numPos = InStr(lenResponse, tChoice, " ") + 1
nChoice = Mid(tChoice, numPos, 1)
  If nChoice = "3" Then
InputName:
    iName = InputBox("Please enter the name of who you want to send the template to...", "Input")
      If iName = "" Then
        MsgBox errMessage1, vbCritical, "Error"
        GoTo InputName
      End If
  End If
    rMessage = WorksheetFunction.VLookup(tChoice, responses.Range("a:b"), 2, False)
      If Not IsError(rMessage) Then
        sMessage = CStr(rMessage)
        sMessage = Replace(sMessage, "[name]", StrConv(iName, vbProperCase))
        main.preview = sMessage
      Else
        GoTo err
      End If
        Exit Sub

err:
  MsgBox "An error occurred during the routine.  Details are as follows:" & Chr(10) & Chr(10) & _
         "Error Number: " & err.Number & Chr(10) & _
         "Description: " & err.Description, vbCritical, "Error"
End Sub

 

[moke123]

I'm not an excel person but I did notice one thing.

you had "tChoice = cmbchoose.Value" however your combobox was named "choose"
When I changed that line you then get a different error on this line

rMessage = WorksheetFunction.VLookup(tChoice, responses.Range("a:b"), 2, False)

it highlights "responses" as invalid qualifier. Should that be response?

 

[arnelgp]

try and test.

 

[conception_native_0123]

I'm not an excel person but I did notice one thing.

you had "tChoice = cmbchoose.Value" however your combobox was named "choose"
When I changed that line you then get a different error on this line

it highlights "responses" as invalid qualifier. Should that be response?

4oh wait! responses is sheet name. i forgot to make sheets collection as qualifier! oops. thank you.

by the way how did you know that responses was invalid qualifier? it just highlight line for me. it does say what is offend word. how did you get that?

 

[conception_native_0123]

try and test.

thank you arne. i will try it.

 

[moke123]

by the way how did you know that responses was invalid qualifier?

that was the error message i got.
I added option explicit to the declarations but i dont know if thats the reason i got the message.

 

[conception_native_0123]

is not option explicit only to require variable declaration?

 

[conception_native_0123]

i got it. it is fixed! i also had issue with not seeing all text in textbox. also seeing paragraph mark symbols instead of line break like i wanted. here is my code that is changed

    rMessage = WorksheetFunction.VLookup(tChoice, Sheets("responses").Range("a:b"), 2, False)
      If Not IsError(rMessage) Then
        sMessage = CStr(rMessage)
        sMessage = Replace(sMessage, "[name]", StrConv(iName, vbProperCase))
        main.txtPreview = sMessage
      Else
        GoTo err
      End If
        Exit Sub

and this is what output looks like. thank you!

 

[conception_native_0123]

this is article i used too

AutoSize, MultiLine, WordWrap, ScrollBars properties, TextBox control example

Office VBA reference topic

docs.microsoft.com