PDA

View Full Version : Edit cells from listbox with userform



Ethan
08-27-2011, 09:57 AM
Hello all,

I try to update cells via an userform that is called by selecting a row on a listbox. However at this stage it keeps on giving an error ut can't figure out why.
Perhaps one of you can help me out.

It's just in a beginning stage so please don't mind the formula errors and the names of the textboxes on the userform that don't match the names of tle listbox. Thi has no influence on the coding though.

I think the error is in the "initialize" part of the code:

Private Sub UserForm_Initialize()
Dim LB As MSForms.ListBox
Dim I As Long
Set LB = Worksheets("Sheet1").OLEObjects("Listbox1").Object
With LB
For I = 1 To .ColumnCount
Me.Controls("TextBox" & I).Text = .List(.ListIndex, I - 1)
Next I
End With

GTO
08-27-2011, 07:52 PM
...I think the error is in the "initialize" part of the code:

Private Sub UserForm_Initialize()
Dim LB As MSForms.ListBox
Dim I As Long
Set LB = Worksheets("Sheet1").OLEObjects("Listbox1").Object
With LB
For I = 1 To .ColumnCount
Me.Controls("TextBox" & I).Text = .List(.ListIndex, I - 1)
Next I
End With

The above will fail, as it will not be able to find an object when I >= 8, as there are only 7 textboxes (TextBox1 thru TextBox7).

Ethan
08-28-2011, 01:12 AM
Thanks for replying GTO.
I changed the number of textboxes to match the columncount of the listbox but it keeps on giving an error "Could not get the list property..."

I can't figure out what I am doing wrong.

GTO
08-28-2011, 04:10 PM
Hi Ethan,

I believe you are just forgetting to make a selection in the listbox, before pressing the Edit button. With nothing selected, the .ListIndex will return -1 and the code falls over.

I think an easy solution might be to have the .ListIndex set when opening.

In ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
Sheet1.ListBox1.ListIndex = 0
End Sub

As an aside, I would strongly suggest using Option Explicit. While in VBIDE, go to the menubar, Tools|Options and select the Editor tab. Tick the Require Variable Declaration checkbox. This will save you headaches down the road.

Hope that helps,

Mark

Ethan
08-29-2011, 12:48 AM
you were right i forgot to select an item :omg2:
Thank you for helping!

GTO
08-29-2011, 01:17 AM
It is a pleasure Ethan and thank you for the feedback :-)