Listbox to display active cell values

  • Hi All,

    I am very new to VBA, just practising and learning. Thanks in advance for all the help.
    I have an excel which has different columns.
    the first is application , when i put an appno and click on userform i want the listbox to display the appno.
    also when i click the next row, the list box should be empty,
    alternatively if there is another app no then the listbox should show that appno.
    when i click individually the userform listbox should show individual app nos in the list box.
    but whn i drag both the cells together the userform listbox should show all the applications.

    i have written this in the userform_activate

    Dim ws As Excel.Worksheet
    Set ws = Report.Sheet14

    Dim rngName As Range

    Dim i As Integer

    For i = 4 To ws.Cells(ws.Rows.Count, 1).End(xlUp).row Step 1
    If ws.Cells(i, 1).Value <> vbNullString Then Me.ListBox1.AddItem ws.Cells(i, 1).Value
    Next i

    but it gives me the same application number again..

    Have been trying for the whole day with no success.
    Hoping to make it work.


  • I don't understand your terms. What does "put in" mean here? What does "click on userform" mean? "Click on next row?" "If there is another app no?" "Click individually?" "Drag both the cells together?"

    What cells are you referring to? When do you show the userform? Is this a modal userform and the listbox should change before one's eyes when different cells are selected?

  • Hi,
    Thanks for responding.
    i mean..

    I have an excel.

    the first column is application no where i have to put like type the application no and then on clicking a cell on another column the userform pops up which is a multipage userform.
    The userform has 2 frames on each page
    each frame has 5 to 6 checkboxes.
    the form also has a textbox.
    now when i have typed the application number in the 1st column of the excel, the uf pops up on click.
    now when uf pops the text box should have the app no. then i click the checkboxes which have arrays associated with them and their values are appneded to the textbox,

    now when i input the next app and click cell on the next column the uf pops up, it shows both the appnos, which i dont want, i want only the next one to be shown.

    but when i put many applications in the excel column and drag the same number of cells in the next column the textbox should pop up with all the application numbers.

    this is what i was supposed to do earlier, but i was not able to so thought of a listbox.

    so my textbox should like
    checkbox2 value

    checkbox2 value

    so on..

    Hope this is clear.
    please help me.. i have been stuck on this for quite some time now.
    as i am not able to iterate through the applications

  • How is a userform "popping up" when a cell is clicked? Are you using a Worksheet_SelectionChange event? If the userform "pops up" again, what had caused it to close? Is this a modeless form? (As XenoCode pointed out, I meant Modeless, not Modal.)

    Why do you have a listbox, textbox, and checkboxes all with the same information? How are the checkboxes "associated" with arrays (and why)? How (and why) is the userform being used (i.e., what is the purpose for the form)? Are there commandbuttons on the form that do something with its information?

  • Hi,
    Thanks for replying.
    This is a really big project involving many different things, so yes on the cell click the userform opens up.
    there are lots of checkboxes and on checking them the corresponding array values declared are seen in the textbox and then there is a button which saves all in a dictionary and so on.

  • Your explanations leave something to be desired, but I think you may be looking for the Worksheet_SelectionChange event in the code for the relevant worksheet. Its Target parameter has the selected range, which may be multiple cells. Experiment with what you call "dragging" and "clicking" to see if that can get what you want.

  • Hi,
    thanks for responding.
    Is there a way by which i can append manually written text to a textbox which already has some text in it.
    like the textbox has
    person's name
    then i have to write " this person is ok to go ahead". the thing is this tool is for many anybody can write anything...which has to be appended and then saved into an excel.
    is there a way by which i can like save whatever the user writes in the text box and append it.

  • I do not know where you will "write" the text to be appended, but here's an example where it is entered into a worksheet cell:

    With UserForm.TextBox1
        .Value = .Value & Worksheets(1).Range("A10").Value2
      End With
  • If you're unloading the userform, then use Hide instead. Also include code for the form's QueryClose event, e.g.,

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
      Cancel = True
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!