Solved: Listbox not loading in Userform

[coliervile]

In my workbook when a user clicks "Submit Leave Request Form" on the "Dashboard" (worksheet) it is suppose to initialize the userform "frmRequest" (which it does) and fill listbox1 with all of the information from the Leave Request worksheet (which it DOESN'T). I run into a Compile Error- Sub or Funtion not Defined here: xlLastRow

Best regards,

Charlie

Private Sub UserForm_Initialize()
'Set properties of listbox1
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = 5
.ColumnHeads = True
.TextColumn = True
.RowSource = "Leave Request!A2:E" & xlLastRow("Leave Request")
.ListStyle = fmListStyleOption
.ListIndex = 0
End With

cboName.Value = ""
With cboName
.AddItem "AA"
.AddItem "BB"
.AddItem "CC"
.AddItem "DD"
.AddItem "EE"
.AddItem "FF"
.AddItem "GG"
End With
cboType.Value = ""
With cboType
.AddItem "Annual"
.AddItem "Prime Time"
.AddItem "Credit Used"
.AddItem "Sick"
.AddItem "LWOP"
.AddItem "FEMLA"
.AddItem "FEFLA"
End With
cboType.Value = ""
txtStart.Value = ""
txtEnd.Value = ""

End Sub

[coliervile]

Here's the workbook.....

Charlie

[Bob Phillips]

It is looking for a function that finds the last used row, which doesn't exist.

the function would be something like




Function xlLastRow(sh As String)

With ThisWorkbook.Worksheets(sh)

xlLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
End Function


although it would be better to cater for variable columns

[coliervile]

Good morning "xld". That solved that problem,but now I have this one:

SourceRange = Range("Leave Request!A2:E2")

Best regards,

Charlie

Private Sub ListBox1_Change()
Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5 As String
If (ListBox1.RowSource <> vbNullString) Then
'Get Range that the ListBox is bound to
Set SourceRange = Range(ListBox1.RowSource)
Else
'Get first data row
Set SourceRange = Range("Leave Request!A2:E2")
Exit Sub
End If

Val1 = ListBox1.Value
'Get the value of the second column
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
'Get the value of the third column
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value
'Get the value of the fourth column
Val4 = SourceRange.Offset(ListBox1.ListIndex, 3).Resize(1, 1).Value
'Get the value of the fifth column
Val5 = SourceRange.Offset(ListBox1.ListIndex, 4).Resize(1, 1).Value
'Concatenate the five values together and display them in Label1
Label6.Caption = "Requested Leave: " & vbNewLine & Val1 & " " & Val2 & " " & Val3 & " " & Val4 & " " & Val5

'Clean Up
Set SourceRange = Nothing
End Sub

[Norie]

Since you have a space you need single quotes.

Range("'Leave Request'!A2:E2")

Though I wouldn't actually recommend that syntax.

Worksheets("Leave Request").Range("A2:E2")

[coliervile]

Norie thanks for your suggestion. I'm now running in to this problem of opening the userform "frmRequest"

I put your coding here:

Else
'Get first data row
Set SourceRange = Worksheets("Leave Request").Range("A2:E2")
Exit Sub
End If

and got this error: Run-time error '380'
Could not set the RowSource property. Invalid property value.

when i clicked on the Debug button I got this reference:

Sub showform()
frmRequest.Show (this was highlighted)
End Sub

I've attached my workbook with your suggestion in it.

Best regards,

Charlie

[Norie]

Charlie

You haven't made the alteration in that attachment.

And again the problem is the space in the worksheet name.

Try this.

.RowSource = "'Leave Request'!A2:E" & xlLastRow("Leave Request")


PS In the future could you make sure you make the changes suggested to the workbook you attach.:)

[coliervile]

Okay if I understand what you're saying...any place in the codings that I have:

.RowSource = "Leave Request!A2:E2"

I need this:

.RowSource = "'Leave Request'!A2:E2"

Best regards,

Charlie

[coliervile]

Norie I've placed your suggestion of:

Worksheets("Leave Request").Range("A2:E2")

where you suggeted in my workbook and I'm still getting the error for some reason;


and got this error: Run-time error '380'
Could not set the RowSource property. Invalid property value.

when i clicked on the Debug button I got this reference:





VBA:


Sub showform() frmRequest.Show (this was highlighted) End Sub





I'm missing something :eek:


Best regards,

Charlie

[coliervile]

Okay I found my error from I had one more of these left;

RowSource = "Leave Request!A2:E2"

I've corrected this and now have an error message of;

Run-time Error '13':

Type mismatch

in my frmRequest but can't seem to locate where???

Best regards,

Charlie

[Norie]

Charlie

That's because you aren't using the correct syntax in the delete code.

Like in all other places you need to use something like this.

.RowSource = "'Leave Request'!A2:E" & xlLastRow("Leave Request")

By the way did the debugger not take you to this line in that sub, it did for me.

.RowSource = Worksheets("Leave Request").Range("A2:E2") & xlLastRow("Leave Request")

[coliervile]

I'm getting buggy eyed from looking at this coding and I'm not seeing what I'm missing. I've changed all of the coding to this:

.RowSource = Worksheets("Leave Request").Range("A2:E2")

I don't think I've missed any and it's still returning an error message "Type mismatch"......:banghead: :help :dunno

What am I missing!!!

Best regards,

Charlie

[Norie]

Charlie

I didn't say to change the code to that.

I meant, though admittedly I didn't spell it out, use this.


.RowSource = "'Leave Request'!A2:E" & xlLastRow("Leave Request")

The RowSource property expects a string, with this code you are trying to set to a range.


.RowSource = Worksheets("Leave Request").Range("A2:E2") & xlLastRow("Leave Request")

[coliervile]

Hurray :beerchug: :mbounce2: finally found it. In the following

coding I was missing this (in red):

Set SourceRange = Range("'Leave Request'!A2:E2")

Duh- no wonder the macro wouldn't run! Thanks for your patience Norie.

Best regards,

Charlie

[Norie]

Charlie

Glad you've got it sorted.:)

But I don't actually think that was the problem.

[ameritecc]

Strangely enough, I am getting the exact same error codes and have been following this Thread very closely. I have change my code as you stated and get the error "Method range of object global failed'. Could you advise on the following code. i feel the problem is in my userform initialize box, because if I comment it completely out the form will pop up, but nothing populates the list boxes. It won't pop up under current coding. I keep getting various errors identical to Norie's

Private Sub ListBox1_Change()
Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5 As String, _
Val6 As String, Val7 As String, Val8 As String, Val9 As String
If (ListBox1.RowSource <> vbNullString) Then
'Get Range that the ListBox is bound to
Set SourceRange = Range(ListBox1.RowSource)
Else
'Get first data row
'Set SourceRange = Range("Sheet32!A3:I3") OLD REPLACED LINE
Set SourceRange = Range("'Sheet32'!A3:I3")
Exit Sub
End If

Val1 = ListBox1.Value
'Get the value of the second column
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
'Get the value of the third column
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value
Val4 = SourceRange.Offset(ListBox1.ListIndex, 3).Resize(1, 1).Value
Val5 = SourceRange.Offset(ListBox1.ListIndex, 4).Resize(1, 1).Value
Val6 = SourceRange.Offset(ListBox1.ListIndex, 5).Resize(1, 1).Value
Val7 = SourceRange.Offset(ListBox1.ListIndex, 6).Resize(1, 1).Value
Val8 = SourceRange.Offset(ListBox1.ListIndex, 7).Resize(1, 1).Value
Val9 = SourceRange.Offset(ListBox1.ListIndex, 8).Resize(1, 1).Value
'Concatenate the three values together and display them in Label1
' Label1.Caption = "Selected Data: " & vbNewLine & Val1 & " " & Val2 & " " & Val3
' Label1.Caption = " Company Name: " & Val1 & vbNewLine & "Phone: " & Val2 & vbNewLine & "Fax: " & Val3
Label1.Caption = "Company Name: " & Val1 _
& vbNewLine & "Department: " & Val2 _
& vbNewLine & "Address: " & Val3 _
& vbNewLine & "City/State/Zip Code: " & Val4 _
& vbNewLine & "Phone Number: " & Val5 _
& vbNewLine & "Fax Number: " & Val6 _
& vbNewLine & "Cell Phone Number: " & Val7 _
& vbNewLine & "Contact Name: " & Val8 _
& vbNewLine & "Email Address: " & Val9 _


'Clean Up
Set SourceRange = Nothing
End Sub

VBA Code in my userform initialize
Private Sub UserForm_Initialize()
'Clean data range
'DeleteBlankRows
'DeleteBlankColumns

'Set properties of listbox1
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = 1
.ColumnHeads = False
.TextColumn = True
'.RowSource = "Sheet32!A3:I" & xlLastRow("Sheet32") Old Line replaced
.RowSource = "'Sheet32'!A2:E" & xlLastRow("Sheet32")
.ListStyle = fmListStyleOption
.ListIndex = 0

End With
End Sub

[coliervile]

Ameritecc take a look at this thread on VBA Epress it's a follow-up...
http://vbaexpress.com/forum/showthread.php?t=18126

The coding was solved by "XLD".

[ameritecc]

Thanks I will take a look right now. This is not being solved, and I have followed this thread and tried all the answers provided to you and I am still getting the same error messages as you.
Thanks again.

[ameritecc]

I did take a look but the one you gave me is looking for different answers than what I was wanting. In this thread it looked like you found the answer through Norie, but when I applied to mine I still got error messages. My request is for proper set of the source range and rowsource, which are underlined in my code placed in this thread yesterday. They are underlined for quick review. But thanks for trying.

[coliervile]

Here's what is in my final product....

Private Sub TextBox1_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpDatesStart As Range
Dim mpDatesEnd As Range
Dim mpTestDate As Date
Dim mpMessage As String
Dim LastRowPrintout As Long
Dim i As Long

With Worksheets("Leave Request")
.Range("A:E").Sort Key1:=.Range("B2"), Order1:=xlAscending, _
Key2:=.Range("D2"), Order2:=xlAscending, _
Header:=xlYes

mpTestDate = CDate(Me.TextBox1.Text)
mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set mpDatesStart = .Range("D1").Resize(mpLastRow)
Set mpDatesEnd = .Range("E1").Resize(mpLastRow)
Set mpNames = .Range("A1").Resize(mpLastRow)

mpRows = .Evaluate("IF((" & mpDatesStart.Address & _
"<=" & CLng(mpTestDate) & ")*" & _
"(" & mpDatesEnd.Address & ">=" & _
CLng(mpTestDate) & ")," & _
"ROW(" & mpNames.Address & "))")

If Not IsArrayAllocated(mpRows) Then

MsgBox "No Leave Request For This Date", vbOKOnly + vbInformation
Else

For i = LBound(mpRows) To UBound(mpRows)

If mpRows(i, 1) <> False Then

mpMessage = mpMessage & "Requested" & " " & mpTestDate & " " & _
"Off- " & " " & mpNames.Cells(i, 1).Value & _
" (Leave type: " & mpNames.Cells(i, 3).Value & _
", Requested on: " & mpNames.Cells(i, 2).Text & ")" & vbNewLine & vbNewLine

LastRowPrintout = Worksheets("Printout").Range("A" & Rows.Count).End(xlUp).Row
.Rows(i).Copy Worksheets("Printout").Range("A" & LastRowPrintout + 1)
End If
Next i

If mpMessage <> "" Then

MsgBox mpMessage, vbOKOnly + vbInformation
Else

MsgBox "No Leave Request For This Date", vbOKOnly + vbInformation
End If
End If

.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

Sheets("Leave Request").Visible = False
End With
End Sub