Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform | Excel Programming | |||
Access from add_in userform to main template userform.... | Excel Programming | |||
Linking userform to userform in Excel 2003 | Excel Programming | |||
Userform inside another userform | Excel Programming | |||
userform | Excel Programming |