Solved: ListBox, Option, MultiSelect

So every problem solved allows me to bury myself even deeper!
What fun.

I have a userform that contains a MultiPage control.
On it is a ListBox
ListStyle is set to "1-fmListStyleOption"
MultiSelect is set to "0-fmMultiSelectSingle"

What I would like to do is have the user click on an option button and have the form switch pages and display that line of information in separate text boxes for editing.

My first issue is that I have no clue how to get the items from the list separated out.

Also, is there a way to get the columns formatted in the listbox? My $ figures just show up as numbers and I have a column of booleans that show up as 0's and -1's?

I have included my UserForm_Initialize() code:

[vba]Private Sub UserForm_Initialize()
Dim Rng As Range
' Dim arySize As Long

With ThisWorkbook.Worksheets("stockRef")
Label8.Caption = .Range("A1").Value
Label9.Caption = .Range("B1").Value
Label10.Caption = .Range("C1").Value
Label11.Caption = .Range("D1").Value
Label12.Caption = .Range("E1").Value
Label13.Caption = .Range("F1").Value
Label14.Caption = .Range("G1").Value
Label15.Caption = .Range("H1").Value
End With

With ThisWorkbook.Worksheets("stockRef")
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 8)
' arySize = Rng.Rows.Count * Rng.Columns.Count
End With
' MsgBox (arySize)
ListBox_ItemsS.List() = Rng.Value

End Sub[/vba]
Thanks in advance (yet again)
:bow:

You mean you want to read the list of values in a listbox? If so, you would need to extract them from List

[vba]

Dim i As Long

With Me.ListBox1

For i = 1 To .ListCount

MsgBox .List(i - 1)
Next i
End With
[/vba]

If you want the Listbox data formatted, you have to format the data if picked from a range, or format each item if you load individually

Alright, I get what you mean about extracting them.
In this case I only want to extract the one that the user has chosen and I want to do it when they choose it.
(ListStyle is Option and MultiSelect is set to Single)

:think:

That is just

[vba]

ListBox1.Value
[/vba]

Either I am searching with the wrong terms or I can't actually do what I would like to as I don't come up with anything like this. (That should tell me something eh?)

So If I add a button to my form I can get this to display in a MsgBox... sort of.

Listbox1.Value....
It only displays the first cell.

The trick is (actually a couple of them) is that I want all 8 cells (the row in question) to display. My ListBox has 8 columns in it and the snippet above displays the first entry. I was assuming that the listbox item contained the whole row. The code below is part of the initialization for the userform.
[vba]Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 8)[/vba]
The listbox itself contains a range. Is there a way to get to the individual locations in the range?
I would also like to have some of this info transfered to 6 textboxes and 1 checkbox on page 2 of my form. Some of this information will also need to be saved in variables.
And to be even more difficult I would like to avoid triggering the action with a button and have it happen when the user selects the option button for the item.

In a nutshell, I have a long list of items each containing 8 pieces of information. Page one of my form just views the list. I would like to allow the user to view the list and when they have checked the radio button for any row, have that rows information transfered to the second page for editing.

: pray2:

Review file attached.

"If it can be done, it can be done in Excel."





[VBA]
Dim var1 As Integer 'declare variable across UserForm1
'Be sure no other UserForms or worksheets are open by the same name from other workbooks.

Private Sub UserForm_Initialize()
'I presume you have ListBox1.ColumnCount set to 8
'and the columnswidths set: (20,18,24,,18,25,,10)
UserForm1.Caption = "UserForm Tips for Database Management"
ListBox1.RowSource = Sheet1.Name & "!A2:H" & Sheet1.Range("A1").CurrentRegion.Rows.Count
ComboBox1.RowSource = Sheet2.Name & "!A2:A" & Sheet2.Range("A1").CurrentRegion.Rows.Count
CommandButton1.Caption = "Modify & Return"
CommandButton2.Caption = "Add to Bottom"
CommandButton3.Caption = "Delete & Return"
CommandButton4.Caption = "Exit"
TextBox1 = Now()
TextBox2 = "wuzup"
End Sub

Private Sub ListBox1_Click()
'prevents unintended changes to ListBox1 during other code execution
If var1 = 1 Then Exit Sub
'0 is page1, 1 is page2
MultiPage1.Pages(1).Enabled = True
UserForm1.MultiPage1.Value = 1
MultiPage1.Pages(0).Enabled = False
ComboBox1 = Sheet1.Range("A2").Offset(ListBox1.ListIndex, 0).Value 'list
TextBox1 = Sheet1.Range("B2").Offset(ListBox1.ListIndex, 0).Value 'Date
TextBox2 = Sheet1.Range("C2").Offset(ListBox1.ListIndex, 0).Value 'text
End Sub

Private Sub TextBox1_Change()
Call EnableButton
End Sub

Private Sub TextBox2_Change()
Call EnableButton
End Sub

Private Sub ComboBox1_Change()
Call EnableButton
End Sub

Sub EnableButton()
If ComboBox1.ListIndex > -1 And IsDate(TextBox1) And TextBox2 <> "" Then 'IsNumber()...
CommandButton1.Enabled = True
CommandButton2.Enabled = True
CommandButton3.Enabled = True
Else
CommandButton1.Enabled = False
CommandButton2.Enabled = False
CommandButton3.Enabled = False
End If
End Sub

Private Sub CommandButton1_Click()
'Modify list and return to Page1
var1 = 1
Sheet1.Range("A2").Offset(ListBox1.ListIndex, 0).Value = ComboBox1
Sheet1.Range("B2").Offset(ListBox1.ListIndex, 0).Value = TextBox1
Sheet1.Range("C2").Offset(ListBox1.ListIndex, 0).Value = Val(TextBox2)
var1 = Empty
ListBox1.TopIndex = ListBox1.ListIndex 'returns to same place on ListBox1
ListBox1.ListIndex = -1 'turns off selection on ListBox1
MultiPage1.Pages(0).Enabled = True
UserForm1.MultiPage1.Value = 0
MultiPage1.Pages(1).Enabled = False
End Sub

Private Sub CommandButton2_Click()
'Add to Bottom
Sheet1.Range("A1:C1").Offset(Sheet1.Range("A1").CurrentRegion.Rows.Count, 0).Value = Array(ComboBox1, TextBox1, TextBox2)
ListBox1.RowSource = Sheet1.Name & "!A2:H" & Sheet1.Range("A1").CurrentRegion.Rows.Count
ListBox1.ListIndex = -1
ListBox1.TopIndex = ListBox1.ListCount 'scrolls to bottom of ListBox1
MultiPage1.Pages(0).Enabled = True
UserForm1.MultiPage1.Value = 0
MultiPage1.Pages(1).Enabled = False
End Sub

Private Sub CommandButton3_Click()
'Delete Row & Return
var2 = ListBox1.ListIndex
Sheet1.Rows(ListBox1.ListIndex + 2 & ":" & ListBox1.ListIndex + 2).Delete Shift:=xlUp
ListBox1.RowSource = Sheet1.Name & "!A2:H" & Sheet1.Range("A1").CurrentRegion.Rows.Count
ListBox1.TopIndex = var2
ListBox1.ListIndex = -1
MultiPage1.Pages(0).Enabled = True
UserForm1.MultiPage1.Value = 0
MultiPage1.Pages(1).Enabled = False
'Or delete the whole range (but not the header)
'Sheet1.Range("A1").CurrentRegion.Offset(1, 0).Clear 'then reset ListBox1.RowSource
End Sub

Private Sub CommandButton4_Click()
varOff = 1
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Can only close from 1st page
If UserForm1.MultiPage1.Value = 1 Then
'disable Close
If CloseMode = 0 Then Cancel = True 'CloseMode = 1 in Unload Me
Else
'require password to turn off UserForm
'Dim var1 As String
'var1 = InputBox("UserForm1 is protected.", "Password")
'If var1 <> "bark" Then Cancel = True
End If
End Sub


[/VBA]

Wow!
Can't wait to dig into this one. Well, going to sleep first then hit it in the morning.
Thanks Ryan!
(Like that tag line too)

Excel programmers don't require sleep!


BTW: Obviously I dont know the particulars of what you're creating, but it seems like keeping all these controls on one page would seem less jerky...

http://www.vbaexpress.com/forum/C:\D...rm1example.JPG

Ryan,
It might but I would like the user to just scroll down the list and pick what they want to edit or from the same form as they edit be able to add a row of data. (There are currently 57 items in the list with 8 pieces of information each)
Your code is awesome.
Got distracted with a bunch of user interface refinements so the next step is to apply your code to my mess.

Thanks so much!

Haven't we all been there!

Have fun!