how to get sql server express to accept a textbox value to a stored procedure

TRy this example:

' Format: Execute_Select_with_Para(Name of stored procedure, Column name, Param type, length of field, actual value to pass)

    Function Execute_Select_with_Param(ByRef ProcName As String, ByRef PName As String, ByRef PType As String, ByRef PLength As Short, ByRef PValue As String) As ADODB.Recordset
        Dim ureg As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim param As New ADODB.Parameter

        ureg.Open(UREGCONN)
        cmd.let_ActiveConnection(ureg)
        cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
        cmd.CommandText = "dbo." & ProcName
        param = cmd.CreateParameter(PName, CShort(PType), ADODB.ParameterDirectionEnum.adParamInput, PLength, PValue)
        cmd.CommandTimeout = 600
        cmd.Parameters.Append(param)
        Return cmd.Execute

    End Function

Sub MyRoutine
 Dim records as New ADODB.Recordset
 records = Execute_Select_with_Param("MYPROCEDURE", "CustomerNo", CStr(ADODB.DataTypeEnum.adChar), 10, CustNum)
End sub

I slighly changed the textbox1 but will still not return data.

Dim objMyConn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        objMyConn.CursorLocation = adOpenStatic
       
        Dim strSQL As String
objMyConn.Open "Provider=SQLOLEDB;Data Source=R9NY9X4;Initial Catalog=XRef_Master_TD;Trusted_connection=yes;"
        strSQL = "exec get_View_SAP_Listbox1 '" + UserForm2.TextBox1.Text + "'"

                With objMyRecordset
                 .ActiveConnection = objMyConn
                 .Open strSQL
                     If Not .EOF Then
                        MsgBox "Yep" 'has a result
                     Else
                        MsgBox "nope" 'doesn't have a result
                 End If
                .Close
             End With



in stored procedure: in sql sexpress


ALTER  PROCEDURE [dbo].[get_View_SAP_Listbox1](

@param1 nvarchar(30)                   --Input  

)
AS
BEGIN
SELECT * from xreftable where mfrnumber = @param1

does strSql look correct?

if you open a connection to Xref_Master_Td with your own account and paste in strSql do you get back results?

thanks