Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Help - Method "Range" of object "_Worksheet" failed.
I am trying to design a user form to allow department managers to add new
employee details, or adjustments to existing employees. The form has a number of fields to be filled, either combo box or text box, but I'm getting the above error when I test it. I haven't used VB a lot, and am trying to build on some examples I found to create this. Here's the code - any ideas? Thanks, Alan Private Sub UserForm_Initialize() Dim cManager As Range Dim cRegion As Range Dim cUnit As Range Dim cHierarchy As Range Dim cExpense As Range Dim ws As Worksheet Set ws = Worksheets("LookupLists") For Each cManager In ws.Range("ManagerList") With Me.cboManager .AddItem cManager.Value .List(.ListCount - 1, 1) = cManager.Offset(0, 1).Value End With Next cManager Me.txtEmployee.Value = "Enter Name" For Each cRegion In ws.Range("RegionList") With Me.cboRegion .AddItem cRegion.Value .List(.ListCount - 1, 1) = cRegion.Offset(0, 1).Value End With Next cRegion For Each cUnit In ws.Range("UnitList") With Me.cboUnit .AddItem cUnit.Value .List(.ListCount - 1, 1) = cUnit.Offset(0, 1).Value End With Next cUnit For Each cHierarchy In ws.Range("HierarchyList") With Me.cboHierarchy .AddItem cHierarchy.Value .List(.ListCount - 1, 1) = cHierarchy.Offset(0, 1).Value End With Next cHierarchy Me.txtAllocation.Value = "Enter Percentage" For Each cExpense In ws.Range("ExpenseList") With Me.cboExpense .AddItem cExpense.Value .List(.ListCount - 1, 1) = cExpense.Offset(0, 1).Value End With Next cExpense Me.txtCurrency.Value = "Enter Currency" Me.txtAmount.Value = 1 Me.txtDate.Value = Format(txtDate, "mm/dd/yyyy") Me.txtComment.Value = "Enter Comments" Me.cboManager.SetFocus End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Help - Method "Range" of object "_Worksheet" failed.
Hi Alan -
Your code does not produce the error message when if all of the "lookup list" ranges are named and named properly. Double check that you've named all five and check for proper spelling. One other note. The Textbox "txtDate" is empty when the form initializes because the variable txtDate is not set anywhere in your procedure. Just a heads up that it needs to be set somewhere if you intend the date to be filled in when the form opens.... Me.txtDate.Value = Format(txtDate, "mm/dd/yyyy") -- Jay "Alan Smith" wrote: I am trying to design a user form to allow department managers to add new employee details, or adjustments to existing employees. The form has a number of fields to be filled, either combo box or text box, but I'm getting the above error when I test it. I haven't used VB a lot, and am trying to build on some examples I found to create this. Here's the code - any ideas? Thanks, Alan Private Sub UserForm_Initialize() Dim cManager As Range Dim cRegion As Range Dim cUnit As Range Dim cHierarchy As Range Dim cExpense As Range Dim ws As Worksheet Set ws = Worksheets("LookupLists") For Each cManager In ws.Range("ManagerList") With Me.cboManager .AddItem cManager.Value .List(.ListCount - 1, 1) = cManager.Offset(0, 1).Value End With Next cManager Me.txtEmployee.Value = "Enter Name" For Each cRegion In ws.Range("RegionList") With Me.cboRegion .AddItem cRegion.Value .List(.ListCount - 1, 1) = cRegion.Offset(0, 1).Value End With Next cRegion For Each cUnit In ws.Range("UnitList") With Me.cboUnit .AddItem cUnit.Value .List(.ListCount - 1, 1) = cUnit.Offset(0, 1).Value End With Next cUnit For Each cHierarchy In ws.Range("HierarchyList") With Me.cboHierarchy .AddItem cHierarchy.Value .List(.ListCount - 1, 1) = cHierarchy.Offset(0, 1).Value End With Next cHierarchy Me.txtAllocation.Value = "Enter Percentage" For Each cExpense In ws.Range("ExpenseList") With Me.cboExpense .AddItem cExpense.Value .List(.ListCount - 1, 1) = cExpense.Offset(0, 1).Value End With Next cExpense Me.txtCurrency.Value = "Enter Currency" Me.txtAmount.Value = 1 Me.txtDate.Value = Format(txtDate, "mm/dd/yyyy") Me.txtComment.Value = "Enter Comments" Me.cboManager.SetFocus End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Help - Method "Range" of object "_Worksheet" failed.
Thanks for the reply Jay. I guess the good news is that the code works, I
just have to find my problem elsewhere (there are some other bits of code related to the same form). I'll set the date variable as well - one less thing to worry about.. Cheers, Alan "Jay" wrote: Hi Alan - Your code does not produce the error message when if all of the "lookup list" ranges are named and named properly. Double check that you've named all five and check for proper spelling. One other note. The Textbox "txtDate" is empty when the form initializes because the variable txtDate is not set anywhere in your procedure. Just a heads up that it needs to be set somewhere if you intend the date to be filled in when the form opens.... Me.txtDate.Value = Format(txtDate, "mm/dd/yyyy") -- Jay "Alan Smith" wrote: I am trying to design a user form to allow department managers to add new employee details, or adjustments to existing employees. The form has a number of fields to be filled, either combo box or text box, but I'm getting the above error when I test it. I haven't used VB a lot, and am trying to build on some examples I found to create this. Here's the code - any ideas? Thanks, Alan Private Sub UserForm_Initialize() Dim cManager As Range Dim cRegion As Range Dim cUnit As Range Dim cHierarchy As Range Dim cExpense As Range Dim ws As Worksheet Set ws = Worksheets("LookupLists") For Each cManager In ws.Range("ManagerList") With Me.cboManager .AddItem cManager.Value .List(.ListCount - 1, 1) = cManager.Offset(0, 1).Value End With Next cManager Me.txtEmployee.Value = "Enter Name" For Each cRegion In ws.Range("RegionList") With Me.cboRegion .AddItem cRegion.Value .List(.ListCount - 1, 1) = cRegion.Offset(0, 1).Value End With Next cRegion For Each cUnit In ws.Range("UnitList") With Me.cboUnit .AddItem cUnit.Value .List(.ListCount - 1, 1) = cUnit.Offset(0, 1).Value End With Next cUnit For Each cHierarchy In ws.Range("HierarchyList") With Me.cboHierarchy .AddItem cHierarchy.Value .List(.ListCount - 1, 1) = cHierarchy.Offset(0, 1).Value End With Next cHierarchy Me.txtAllocation.Value = "Enter Percentage" For Each cExpense In ws.Range("ExpenseList") With Me.cboExpense .AddItem cExpense.Value .List(.ListCount - 1, 1) = cExpense.Offset(0, 1).Value End With Next cExpense Me.txtCurrency.Value = "Enter Currency" Me.txtAmount.Value = 1 Me.txtDate.Value = Format(txtDate, "mm/dd/yyyy") Me.txtComment.Value = "Enter Comments" Me.cboManager.SetFocus End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Help - Method "Range" of object "_Worksheet" failed.
Hi Jay,
I found the error - I had missed naming one of the lists (d'oh!), so it works now. Another quick question for you regarding the "percentage" input - how can I get it to show as say 100% and not 10000%? Thanks Alan "Jay" wrote: Hi Alan - Your code does not produce the error message when if all of the "lookup list" ranges are named and named properly. Double check that you've named all five and check for proper spelling. One other note. The Textbox "txtDate" is empty when the form initializes because the variable txtDate is not set anywhere in your procedure. Just a heads up that it needs to be set somewhere if you intend the date to be filled in when the form opens.... Me.txtDate.Value = Format(txtDate, "mm/dd/yyyy") -- Jay "Alan Smith" wrote: I am trying to design a user form to allow department managers to add new employee details, or adjustments to existing employees. The form has a number of fields to be filled, either combo box or text box, but I'm getting the above error when I test it. I haven't used VB a lot, and am trying to build on some examples I found to create this. Here's the code - any ideas? Thanks, Alan Private Sub UserForm_Initialize() Dim cManager As Range Dim cRegion As Range Dim cUnit As Range Dim cHierarchy As Range Dim cExpense As Range Dim ws As Worksheet Set ws = Worksheets("LookupLists") For Each cManager In ws.Range("ManagerList") With Me.cboManager .AddItem cManager.Value .List(.ListCount - 1, 1) = cManager.Offset(0, 1).Value End With Next cManager Me.txtEmployee.Value = "Enter Name" For Each cRegion In ws.Range("RegionList") With Me.cboRegion .AddItem cRegion.Value .List(.ListCount - 1, 1) = cRegion.Offset(0, 1).Value End With Next cRegion For Each cUnit In ws.Range("UnitList") With Me.cboUnit .AddItem cUnit.Value .List(.ListCount - 1, 1) = cUnit.Offset(0, 1).Value End With Next cUnit For Each cHierarchy In ws.Range("HierarchyList") With Me.cboHierarchy .AddItem cHierarchy.Value .List(.ListCount - 1, 1) = cHierarchy.Offset(0, 1).Value End With Next cHierarchy Me.txtAllocation.Value = "Enter Percentage" For Each cExpense In ws.Range("ExpenseList") With Me.cboExpense .AddItem cExpense.Value .List(.ListCount - 1, 1) = cExpense.Offset(0, 1).Value End With Next cExpense Me.txtCurrency.Value = "Enter Currency" Me.txtAmount.Value = 1 Me.txtDate.Value = Format(txtDate, "mm/dd/yyyy") Me.txtComment.Value = "Enter Comments" Me.cboManager.SetFocus End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error in Macro: "Method 'Paste' of object '_Worksheet' failed" | Excel Programming | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming | |||
"method 'Copy' of object '_Worksheet' failed" | Excel Programming | |||
"GoalSeek method of Range object failed" error message | Excel Programming | |||
METHOD "SELECT" OF OBJECT '_WORKSHEET' FAILED when opening a file | Excel Programming |