0

So I'm currently getting an "Expected end of statement" error upon running the provided code. I know that the problem lies in the way that Sub Searched function is written. I'm not really sure where to go from here but you should be able to get a feel for what I need to do from the code provided. Sub Searched works as a standalone just not with the initialize function.

Initialize

Private Sub UserForm_Initialize()
TextBox1.Text = Selection.Value
TextBox2.Text = Searched Sheets("CashHour1").Range("B2:E60"), Selection
End Sub

Command to autofill textbox in initialize function

Sub Searched(Rnge As Range, E_name As String)
On Error Resume Next
Sal = Application.WorksheetFunction.VLookup(E_name, Rnge, 2, False)
Sal1 = Application.WorksheetFunction.VLookup(E_name, Rnge, 3, False)

If Len(E_name) = 0 Then
MsgBox "Select an employee"

ElseIf Len(Sal) < 1 Then
Hours = "OFF"

Else
Hours = Sal & " - " & Sal1
End If


End Sub
ryano
  • 231
  • 1
  • 5
  • 18
  • Change `Searched` to a Function, and then do `TextBox2.Text = Searched(Sheets("CashHour1").Range("B2:E60"), Selection.Value) – David Zemens May 14 '14 at 15:02
  • @DavidZemens Made the changes. I'm not getting an error anymore but it's not filling any information into TextBox2. – ryano May 14 '14 at 15:11
  • because your function isn't returning anything. See my answer below I think it will be better than using a `Function` for this, just modify the sub to accept the `TextBox` directly and manipulate it inside the `Searched` subroutine. – David Zemens May 14 '14 at 15:12

1 Answers1

1

Searched is a Subroutine, not a function, so it can't return a value.

There are two ways you could handle this, one would be to change Searched to a function, but I would probably just modify the Searched subroutine to also accept a TextBox argument.

Searched Sheets("CashHour1").Range("B2:E60"), Selection.Value, TextBox2

So modify the sub to accept a textbox argument:

Searched(Rnge As Range, E_name As String, tb as MSForms.TextBox)
On Error Resume Next
Sal = Application.WorksheetFunction.VLookup(E_name, Rnge, 2, False)
Sal1 = Application.WorksheetFunction.VLookup(E_name, Rnge, 3, False)

If Len(E_name) = 0 Then
MsgBox "Select an employee"

ElseIf Len(Sal) < 1 Then
Hours = "OFF"

Else
Hours = Sal & " - " & Sal1
End If

'## Write to the textbox:
tb.Text = Hours
End Sub

NOTE You have a lot of undeclared variables and your error-handling is non-existent, so this might still raise some problems, for example if Len(E_Name) = 0 then the TextBox1.Text will be 0...

See also this answer, about why you should NOT use Selection in your code:

How to avoid using Select in Excel VBA macros

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • I plan to expand the code to handle specific errors that can arise but I just needed to get that working before I expand on the code any more. Thanks for the selection tip but for this particular case I'm not sure I'll be able to avoid it. The user is essentially selecting a name from a list of about 60 names and then running this code to return the relevant data. – ryano May 14 '14 at 15:27
  • 1
    You can always avoid it, and it is always a good idea to do so. – Michael Blaustein May 14 '14 at 17:36
  • Avoid it with an Inputbox, or if absolutely necessary to use `Selection` as a way of capturing user input, you should assign the `Selection.Value` (or `Selection.Address`, etc.) to an appropriately typed variable. – David Zemens May 14 '14 at 17:59