ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Userform Help!!!!Please (https://www.excelbanter.com/excel-programming/328068-userform-help-please.html)

Jennifer
Userform Help!!!!Please
 
Please Help, I am in dier need of some help.
I am using an example in a book and it says to add
<LastRow = FindLastRow to
the UserForm_Initialize event. As you can see I did, BUT I keep getting a
"Variable not defined" rigth there. Help! Yes, I know it is probably very
basic.
Thank you!
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

Bob Phillips[_6_]
Userform Help!!!!Please
 
Where is your FindLastRow function?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Please Help, I am in dier need of some help.
I am using an example in a book and it says to add
<LastRow = FindLastRow to
the UserForm_Initialize event. As you can see I did, BUT I keep getting a
"Variable not defined" rigth there. Help! Yes, I know it is probably very
basic.
Thank you!
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

OfficeHacker
Userform Help!!!!Please
 
Hi Jennifer,

Nowhere in the code does it indicate what either LastRow or FindLastRow are.
It appears that FindLastRow is the name of a function. If it is where is it?
It also appear that LastRow is the name of a variable. Where is this variable
declared?

The error message variable not defined is telling you that VBA does not
think this variable has been declared yet.

By the way, the code you are using to populate the combo boxes can be
achieved in one line like this:

cboVend.List = wksLookupLists.Range("VendorList").Value

Good Luck.

Patrick Molloy
Userform Help!!!!Please
 
do you actually need that lastrow variable? its not in the sub..uless its
used elesewhere in the form's code page?
"Jennifer" wrote in message
...
Please Help, I am in dier need of some help.
I am using an example in a book and it says to add
<LastRow = FindLastRow to
the UserForm_Initialize event. As you can see I did, BUT I keep getting a
"Variable not defined" rigth there. Help! Yes, I know it is probably very
basic.
Thank you!
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer



Jennifer
Userform Help!!!!Please
 
Sorry I didn't give you enough information. This is some more of what I am
working with: You'll notice they have LastRow in them. Thanks guys!

Private Sub cmdFirst_Click()
'Command button to find the first data row of data in the database
RowNumber.Text = "2"
End Sub

Private Sub cmdLast_Click()
Public LastRow As Long
'Comand button to find the last row in the database
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer




Bob Phillips[_6_]
Userform Help!!!!Please
 
so where is Firstrow?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Sorry I didn't give you enough information. This is some more of what I am
working with: You'll notice they have LastRow in them. Thanks guys!

Private Sub cmdFirst_Click()
'Command button to find the first data row of data in the database
RowNumber.Text = "2"
End Sub

Private Sub cmdLast_Click()
Public LastRow As Long
'Comand button to find the last row in the database
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

Jennifer
Userform Help!!!!Please
 
Sorry Bob,
Bear with me I really new at this. Not sure what you are asking. What should
I be looking for in my code. Would it be best if I just put the whole thing
in here? Jennifer

"Bob Phillips" wrote:

so where is Firstrow?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Sorry I didn't give you enough information. This is some more of what I am
working with: You'll notice they have LastRow in them. Thanks guys!

Private Sub cmdFirst_Click()
'Command button to find the first data row of data in the database
RowNumber.Text = "2"
End Sub

Private Sub cmdLast_Click()
Public LastRow As Long
'Comand button to find the last row in the database
LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

Jennifer
Userform Help!!!!Please
 
Does this help?
Private Sub GetData()
Public LastRow As Long
'shows the getdata routine
Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If

If r 1 And r <= LastRow Then
txtInvoice.Text = Cells(r, 2)
txtDate.Text = Cells(r, 3)
cboVend.Text = Cells(r, 4)
cboRan.Text = Cells(r, 5)
txtPallet.Text = Cells(r, 7)
txtQty.Text = Cells(r, 8)
txtPrice.Text = Cells(r, 10)
txtFrt.Text = Cells(r, 11)
txtRepakHrs.Text = Cells(r, 13)
txtRepakQty.Text = Cells(r, 14)
DisableSave
ElseIf r = 1 Then
ClearData

Else
ClearData
MsgBox "invalid row number"
End If

End Sub
Private Sub ClearData()

txtInvoice.Text = ""
txtDate.Text = ""
cboVend.Text = "None"
cboRan.Text = "None"
txtPallet.Text = ""
txtQty.Text = ""
txtPrice.Text = ""
txtFrt.Text = ""
txtRepakHrs.Text = ""
txtRepakQty.Text = ""
End Sub
Private Sub DisableSave()

cmdSave.Enabled = False
btnClose.Enabled = False
End Sub
Private Sub UserForm_Initialize()

GetData
LastRow = FindLastRow
Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
Private Function FindLastRow()

'Find the last row in the database
Dim r As Long

r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) 0
r = r + 1

Loop
FindLastRow = r
End Function

"Bob Phillips" wrote:

Where is your FindLastRow function?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Please Help, I am in dier need of some help.
I am using an example in a book and it says to add
<LastRow = FindLastRow to
the UserForm_Initialize event. As you can see I did, BUT I keep getting a
"Variable not defined" rigth there. Help! Yes, I know it is probably very
basic.
Thank you!
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

Tom Ogilvy
Userform Help!!!!Please
 
Make it Public. Make it simpler and faster with:

Public Function FindLastRow()
'Find the last row in the database
FindLastRow = Cells(rows.count,1).End(xlup).row
End Function

--
Regards,
Tom Ogilvy


"Jennifer" wrote in message
...
Does this help?
Private Sub GetData()
Public LastRow As Long
'shows the getdata routine
Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If

If r 1 And r <= LastRow Then
txtInvoice.Text = Cells(r, 2)
txtDate.Text = Cells(r, 3)
cboVend.Text = Cells(r, 4)
cboRan.Text = Cells(r, 5)
txtPallet.Text = Cells(r, 7)
txtQty.Text = Cells(r, 8)
txtPrice.Text = Cells(r, 10)
txtFrt.Text = Cells(r, 11)
txtRepakHrs.Text = Cells(r, 13)
txtRepakQty.Text = Cells(r, 14)
DisableSave
ElseIf r = 1 Then
ClearData

Else
ClearData
MsgBox "invalid row number"
End If

End Sub
Private Sub ClearData()

txtInvoice.Text = ""
txtDate.Text = ""
cboVend.Text = "None"
cboRan.Text = "None"
txtPallet.Text = ""
txtQty.Text = ""
txtPrice.Text = ""
txtFrt.Text = ""
txtRepakHrs.Text = ""
txtRepakQty.Text = ""
End Sub
Private Sub DisableSave()

cmdSave.Enabled = False
btnClose.Enabled = False
End Sub
Private Sub UserForm_Initialize()

GetData
LastRow = FindLastRow
Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
Private Function FindLastRow()

'Find the last row in the database
Dim r As Long

r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) 0
r = r + 1

Loop
FindLastRow = r
End Function

"Bob Phillips" wrote:

Where is your FindLastRow function?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Please Help, I am in dier need of some help.
I am using an example in a book and it says to add
<LastRow = FindLastRow to
the UserForm_Initialize event. As you can see I did, BUT I keep
getting a
"Variable not defined" rigth there. Help! Yes, I know it is probably
very
basic.
Thank you!
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

Tom Ogilvy
Userform Help!!!!Please
 
' Place at the top of a general module outside any routine
Public LastRow as Long

Private Sub UserForm_Initialize
Dim LastRow as Long

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Remove any other declaration for LastRow
Such as In GetData
--
Regards,
Tom Ogilvy


"Jennifer" wrote in message
...
Does this help?
Private Sub GetData()
Public LastRow As Long
'shows the getdata routine
Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If

If r 1 And r <= LastRow Then
txtInvoice.Text = Cells(r, 2)
txtDate.Text = Cells(r, 3)
cboVend.Text = Cells(r, 4)
cboRan.Text = Cells(r, 5)
txtPallet.Text = Cells(r, 7)
txtQty.Text = Cells(r, 8)
txtPrice.Text = Cells(r, 10)
txtFrt.Text = Cells(r, 11)
txtRepakHrs.Text = Cells(r, 13)
txtRepakQty.Text = Cells(r, 14)
DisableSave
ElseIf r = 1 Then
ClearData

Else
ClearData
MsgBox "invalid row number"
End If

End Sub
Private Sub ClearData()

txtInvoice.Text = ""
txtDate.Text = ""
cboVend.Text = "None"
cboRan.Text = "None"
txtPallet.Text = ""
txtQty.Text = ""
txtPrice.Text = ""
txtFrt.Text = ""
txtRepakHrs.Text = ""
txtRepakQty.Text = ""
End Sub
Private Sub DisableSave()

cmdSave.Enabled = False
btnClose.Enabled = False
End Sub
Private Sub UserForm_Initialize()

GetData
LastRow = FindLastRow
Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
Private Function FindLastRow()

'Find the last row in the database
Dim r As Long

r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) 0
r = r + 1

Loop
FindLastRow = r
End Function

"Bob Phillips" wrote:

Where is your FindLastRow function?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Please Help, I am in dier need of some help.
I am using an example in a book and it says to add
<LastRow = FindLastRow to
the UserForm_Initialize event. As you can see I did, BUT I keep
getting a
"Variable not defined" rigth there. Help! Yes, I know it is probably
very
basic.
Thank you!
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

Jennifer
Userform Help!!!!Please
 
Tom,
You said to place
Public LastRow as Long
in a general module outside any routine. Anytime I place it in one it puts a
line and puts it up in the module above. I am stuck. I have been at this to
long. It should be that hard should it?

"Tom Ogilvy" wrote:

' Place at the top of a general module outside any routine
Public LastRow as Long

Private Sub UserForm_Initialize
Dim LastRow as Long

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Remove any other declaration for LastRow
Such as In GetData
--
Regards,
Tom Ogilvy


"Jennifer" wrote in message
...
Does this help?
Private Sub GetData()
Public LastRow As Long
'shows the getdata routine
Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If

If r 1 And r <= LastRow Then
txtInvoice.Text = Cells(r, 2)
txtDate.Text = Cells(r, 3)
cboVend.Text = Cells(r, 4)
cboRan.Text = Cells(r, 5)
txtPallet.Text = Cells(r, 7)
txtQty.Text = Cells(r, 8)
txtPrice.Text = Cells(r, 10)
txtFrt.Text = Cells(r, 11)
txtRepakHrs.Text = Cells(r, 13)
txtRepakQty.Text = Cells(r, 14)
DisableSave
ElseIf r = 1 Then
ClearData

Else
ClearData
MsgBox "invalid row number"
End If

End Sub
Private Sub ClearData()

txtInvoice.Text = ""
txtDate.Text = ""
cboVend.Text = "None"
cboRan.Text = "None"
txtPallet.Text = ""
txtQty.Text = ""
txtPrice.Text = ""
txtFrt.Text = ""
txtRepakHrs.Text = ""
txtRepakQty.Text = ""
End Sub
Private Sub DisableSave()

cmdSave.Enabled = False
btnClose.Enabled = False
End Sub
Private Sub UserForm_Initialize()

GetData
LastRow = FindLastRow
Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
Private Function FindLastRow()

'Find the last row in the database
Dim r As Long

r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) 0
r = r + 1

Loop
FindLastRow = r
End Function

"Bob Phillips" wrote:

Where is your FindLastRow function?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" wrote in message
...
Please Help, I am in dier need of some help.
I am using an example in a book and it says to add
<LastRow = FindLastRow to
the UserForm_Initialize event. As you can see I did, BUT I keep
getting a
"Variable not defined" rigth there. Help! Yes, I know it is probably
very
basic.
Thank you!
Private Sub UserForm_Initialize

GetData
LastRow = FindLastRow

Dim cItem As Range

With Me.cboVend
For Each cItem In wksLookupLists.Range("VendorList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboRan
For Each cItem In wksLookupLists.Range("RanchIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer



Expand AllCollapse All
--
Though daily learning, I LOVE EXCEL!
Jennifer

Vasant Nanavati
Userform Help!!!!Please
 
"Jennifer" wrote in message
...
Tom,
You said to place
Public LastRow as Long
in a general module outside any routine. Anytime I place it in one it puts
a
line and puts it up in the module above.

That is what it is supposed to do. It doesn't put it in the "module" above;
it puts it in the Declarations section of the module, which is at the top of
the module.

--

Vasant


All times are GMT +1. The time now is 09:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com