Consulting

Results 1 to 10 of 10

Thread: Solved: ListBox, Option, MultiSelect

  1. #1

    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)

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3

    Moving forward...

    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)


  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    That is just

    [vba]

    ListBox1.Value
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5

    still fumbling....

    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.


  6. #6

    Excel Programming for Userforms

    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]

  7. #7
    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)

  8. #8

    Sleep?

    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...


  9. #9
    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!

  10. #10
    Haven't we all been there!

    Have fun!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •