Error Help - Method "Range" of object "_Worksheet" failed.

[Alan Smith]

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

 

[Jay]

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")

 

[Alan Smith]

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

 

[Alan Smith]

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