Error 381

  • Thread starter Thread starter wynnwilliamson
  • Start date Start date
W

wynnwilliamson

I am trying to use some code I found for a more advance dataentry
userform and I receive a 381 error when I try to show the userform. The
problem is from the .List under the initialization sub.

Any idea how to resolve this?


Private Sub UserForm_Initialize()
With Me.ListBox1
.List = Sheets("Hoja1").Range("A1").CurrentRegion.Resize(, 1).Value
.MultiSelect = 2
End With
End Sub



Private Sub CommandButton1_Click()
Dim i As Integer, a(), n As Integer
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
n = n + 1
a(n) = .List(i)
End If
Next
End With
If n > 0 Then
With Sheets("Hoja1").Range("a" & Rows.Count).End(xlUp).Offset(1)
.Resize(n).Value = WorksheetFunction.Transpose(a)
.Offset(, 1).Resize(n).Value = Me.TextBox1.Text
End With
End If
End Sub
 
This sounds as if the range being loaded is empty. Chek the data on that
sheet, that range.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Good call, that did resolve the error, but now I am getting an error 9
when it hits:

a(n) = .List(i)




Bob Phillips ha escrito:
 
You need to size the array

Private Sub CommandButton1_Click()
Dim i As Integer, a(), n As Integer
With Me.ListBox1
ReDim a(0)
For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve a(n)
a(n) = .List(i)
n = n + 1
End If
Next
End With
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Cool. Bob. Now it's working. the only downside is that the code doenst
really do what I want it to. I want the value entered into the textbox
to show up alongside the value in column A, not in a new row. I'll see
if I can play with this to get it to do what I want..if you have any
suggestion i would appreciate it. thank you very very much for the
help.


Bob Phillips ha escrito:
 
Dim rng As Range

Private Sub UserForm_Initialize()
Set rng = Sheets("Hoja1").Range("A1").CurrentRegion.Resize(, 1)
With Me.ListBox1
.List = rng.Value
.MultiSelect = fmMultiSelectMulti
End With
End Sub


Private Sub CommandButton1_Click()
Dim i As Integer
With Me.ListBox1
ReDim a(0)
For i = 0 To .ListCount - 1
If .Selected(i) Then
rng.Cells(i + 1, 1).Offset(0, 1).Value = Me.TextBox1.Text
End If
Next
End With
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Yep. That does it. excelente. thank you for the help!!!!

Bob Phillips ha escrito:
 
There's one quirk that I'm trying to work around...i can't choose a
column as the listbox source if there is another row with content to
it's left. that row's content is displayed instead. Is there a way to
work around this?


(e-mail address removed) ha escrito:
 
LOL. Always a catch!

Dim rng As Range

Private Sub UserForm_Initialize()
Set rng = Sheets("Hoja1").Range(Range("B1"), Range("B1").End(xlDown))
With Me.ListBox1
.List = rng.Value
.MultiSelect = fmMultiSelectMulti
End With
End Sub


Private Sub CommandButton1_Click()
Dim i As Integer
With Me.ListBox1
ReDim a(0)
For i = 0 To .ListCount - 1
If .Selected(i) Then
rng.Cells(i + 1, 1).Offset(0, 1).Value = Me.TextBox1.Text
End If
Next
End With
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Back
Top