Using List box with input box RRS feed

  • Question

  • Hi, I would like to give the user a list box from which to choose various items. When an item is selected, I would like an input box to pop up allowing the user to input a #. Then I would like that # to be entered into a designated cell. I would like this to loop back around so they can continue select list box options/enter data or hit "finished".


    Thursday, June 2, 2016 2:44 PM

All replies

  • I would also like the existing data to pop up in the input box to allow the user to change that data or if data is already correct, they can hit "next" to move to the next section
    Thursday, June 2, 2016 6:42 PM
  • This requirement is so vague as to be impossible to define. What application is it to be run from?

    What is the relationship between the list box selection and the document etc that it is called from. What is the relationship between the list box and the #? What is the point of the #?  Is # a hash symbol or does it represent a number? If you want to make changes to the selection, why not use a combo box?

    Graham Mayor - Word MVP

    Friday, June 3, 2016 3:56 AM
  • Sorry- I WAS vague, let me start over.

    I am a beginner VBA for Excel user, so I may not get the terminology correct here....

    What I am trying to do is:

    First I have created a frame that pops up when the file is opened and gives three option buttons. Then I have code written that when the first button is selected, then an input box pops that says "what is the target for the month of (insert month name)" & allows user to enter a number.  The user may be entering the data for the first time or may be modifying existing data, so I wanted them to be able to view/edit data that is already there.

    So, my first question is, could the input box show the existing data that corresponds to the month and allow the user to edit it?


    Instead of having 12 months worth of input boxes that pop up one by one, could I use a list box to allow user to select the month that they would like to edit and then have the input box pop up (showing existing data, if applicable) and allow them to enter/edit the number?

    I created a list box in excel but am confused how to write this into the code so that it pops up. How do I refer to my list box in the code-how do I identify it?   


    Friday, June 3, 2016 2:13 PM
  • The short answer is that what you ask should be possible, but without knowing anything about your worksheet it is difficult to be precise. Can you post a link to the worksheet at an on-line resource, so that we can see what you are doing?

    It seems you need a userform to select a month (or a date) and view the data associated with that month/date, but what then do you want to do with the data associated with that date? Is it one or more records, and if more than one do you want to apply the process to all of them?

    Graham Mayor - Word MVP

    Saturday, June 4, 2016 4:38 AM

    I am hoping that you will be able to access via the link above.

    So I was able to move a little further with this, since my initial questions.  

    Here is where I am at:

    First issue:

    So, going from userform1 and selecting the first option, brings up userform2 and if selecting the first option-"initial chart set up" this will take them through each of the input boxes for each of the 12 months of the year".

    I am having an issue with the code if they select the second option instead - "chart is set...." and select next. I want userform3 to immediately pop up, but right now the input boxes are still coming up again and then userform3 pops up.  How do I fix that in the code?

    Second issue:

    Userform3 - I want them to be able to choose the month that they want to edit from the list box and then an input box or something would pop up and show them the data that is currently in the cell for that month and allow them to change the data, if needed.  So I attempted to write the code for July, if they select July from the list and then I want userform4Julytarget to pop up.  I am not sure how to write this.
    I need to add a previous screen button to this one too.

    All of these inputs are just dropping in to the main worksheet, which you will see in my file.

    thank you so much for your help!


    Thursday, June 9, 2016 3:24 PM
  • Frankly I don't see the point of all those input boxes, nor indeed of some of the userforms. You can do all of it with Userform 1 and Userform 3. The rest is just chaff. Had I been starting from scratch, I would have done it all on the one form, but we are where we are.

    I have posted an updated version at!812&authkey=!AAJBywL3AeFT9hw&ithint=file%2cxlsm which should help you along.

    Graham Mayor - Word MVP

    Friday, June 10, 2016 11:28 AM
  • Thanks so much! I see now that additional userforms were not needed.

    A few questions please :

    Sub TextTarget_Change()

    Dim i As Long, k As Long  'what is the purpose of the word "long" here?
        If Me.ListBox1.ListIndex > -1 Then
            k = Me.ListBox1.ListIndex
            If Not Me.TextTarget.Text = Me.ListBox1.Column(1) ThenI am getting a little lost here. can you explain 
                Range("D" & Me.ListBox1.ListIndex + 2) = Me.TextTarget.Value  the remaining lines of code?
                With Me.ListBox1
                    For i = 2 To 13
                        .List(i - 2, 0) = Range("A" & i)
                        .List(i - 2, 1) = Range("D" & i)
                    Next i
                    .ListIndex = k
                End With
            End If
        End If
    End Sub

    One question here: what is the significance of the 48 to 57 below?

    Private Function IsInteger(ByVal i As String) As Boolean
    Select Case i        'Checking to see if inside valid Ascii range for integers
            Case 48 To 57
                IsInteger = True
            Case Else
                IsInteger = False
        End Select
        Exit Function
    End Function


    Thursday, June 16, 2016 7:23 PM
  • The numeric variables used in the loops are declared as Long data types. In this instance Integer would have probably been more appropriate, but Long is acceptable

    Range("D" & Me.ListBox1.ListIndex + 2) = Me.TextTarget.Value

    The ListIndex is the index number of the selected item in the list box (starting at 0). Thus if July 2016 is selected that would be index number 0. In the sheet July 2016 is in row 2 so to correlate the two values, add 2 to the ListIndex.

    Case 48 to 57

    These are the ascii values of the numeric characters 0 to 9 thus in combination with the function only those characters are allowed

    Graham Mayor - Word MVP

    Friday, June 17, 2016 4:02 AM