Excel New Userform Example

Excel VBA Userform Example



This Excel Userform Example contains many advanced features.

       ◾ When the userform is loaded , the data in the columns is automatically listed on the listbox’s 15 columns. The related codes :
Sheets("Data").Activate
ListBox1.ColumnWidths = "92;140;110;65;65;35;40;65;65;115;150;65;65;65;65"         'Column Widths Of Listbox Columns
ListBox1.ColumnCount = 15                                                          'Column Count Of Listbox
ListBox1.List = Sheets("Data").Range("A2:O" & Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row).Value

        Between the listbox items can be navigated with the spin button on the right. The selected item on listbox is highlighted at same time on the worksheet :
Private Sub ListBox1_Click()
Dim say, lastrow As Long, a As Byte
...
lastrow = Sheets("Data").Cells(Rows.count, "A").End(xlUp).Row
Sheets("Data").Activate
Sheets("Data").Range("A2:A" & lastrow).Find(What:=ListBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate
say = ActiveCell.Row
Sheets("Data").Range("A" & say & ":O" & say).Select
End Sub

       ◾ The contents of selected item on the listbox are displayed in the textbox corresponding to each column :
Private Sub ListBox1_Click()
For a = 0 To 11
Controls("textbox" & a + 1) = ListBox1.Column(a)
Next
TextBox16.Value = ListBox1.Column(12)
TextBox17.Value = ListBox1.Column(13)
TextBox18.Value = ListBox1.Column(14)
....
End Sub

         Data can be searched with textbox in four different columns. The search result is listed on the listbox.

         Listbox items can be selected as single or multiple . The selected items can be copied to another worksheet :
Private Sub OptionButton1_Click()
ListBox1.MultiSelect = 0
End Sub

Private Sub OptionButton2_Click()
ListBox1.ListIndex = -1
ListBox1.MultiSelect = 1
End Sub

Private Sub OptionButton3_Click()
ListBox1.ListIndex = -1
ListBox1.MultiSelect = 2
End Sub


       ◾ We have used that codes to copy items of listbox to other sheet :
Private Sub CommandButton8_Click()
  Dim Litem As Long, LbRows As Long, LbCols As Long
 Dim bu As Boolean
 Dim Lbloop As Long, Lbcopy As Long

 LbRows = ListBox1.ListCount - 1
 LbCols = ListBox1.ColumnCount - 1
       For Litem = 0 To LbRows
    If ListBox1.Selected(Litem) = True Then
          bu = True
          Exit For
    End If
    Next
    If bu = True Then
    With Sheets("SelectedData").Cells(Rows.count, 1).End(xlUp).Offset(1, 0)
                  For Litem = 0 To LbRows
                If ListBox1.Selected(Litem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  Lbcopy = Lbcopy + 1
            For Lbloop = 0 To LbCols
                       'Transfer selected row to relevant row of transfer range
            .Cells(Lbcopy, Lbloop + 1) = ListBox1.List(Litem, Lbloop)
         Next Lbloop
                End If
            Next
            For m = 0 To LbCols
                With Sheets("SelectedData").Cells(Rows.count, 1).End(xlUp).Offset(0, m).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 23
        End With
Next
        End With
          Else
         MsgBox "Nothing chosen", vbCritical
         Exit Sub
    End If
MsgBox "The Selected Data Are Copied.", vbInformation
Sheets("SelectedData").Select
End Sub



39 comments:

  1. I shall b very thankful to u if u make it 30 column

    ReplyDelete
  2. Hello,
    I am trying to add button with popup of list and it works but, how do i tell to insert my selection to correct text box? The list extensive to create pull down list.

    Thanks for your help in advance.
    Nimesh

    ReplyDelete
  3. Private Sub ListBox1_Click()
    Dim say As Long, a As Byte

    For a = 0 To 11
    Controls("textbox" & a + 1) = ListBox1.column(a)
    Next

    Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
    say = ActiveCell.row
    Sheets("Data").Range("A" & say & ":L" & say).Select
    TextBox15 = ListBox1.ListIndex + 1
    End Sub


    Hi, I want to add "combobox" and "dtpicker" in control box along with "textbox". How to do this?

    ReplyDelete
  4. Link is not work Please update again in other Side

    ReplyDelete
    Replies
    1. Links are correct :
      http://adf.ly/1S8y8n OR https://adf.ly/1S8y8n

      Delete
    2. Private Sub ListBox1_Click()
      Dim say As Long, a As Byte

      For a = 0 To 11
      Controls("textbox" & a + 1) = ListBox1.column(a)
      Next

      Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
      say = ActiveCell.row
      Sheets("Data").Range("A" & say & ":L" & say).Select
      TextBox15 = ListBox1.ListIndex + 1
      End Sub


      Hi, I want to add "combobox" and "dtpicker" in control box along with "textbox". How to do this?

      Delete
  5. Private Sub ListBox1_Click()
    Dim say As Long, a As Byte

    For a = 0 To 11
    Controls("textbox" & a + 1) = ListBox1.column(a)
    Next

    Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
    say = ActiveCell.row
    Sheets("Data").Range("A" & say & ":L" & say).Select
    TextBox15 = ListBox1.ListIndex + 1
    End Sub


    Hi, I want to add "combobox" and "dtpicker" in control box along with "textbox". How to do this?

    ReplyDelete
  6. Hi, I wanted to know if there is a way to send data from listbox after you filter to a sheet?

    ReplyDelete
    Replies
    1. You can review this userform example :
      http://merkez-ihayat.blogspot.com.tr/2015/11/updated-userform.html

      Delete
    2. Got it thank you!
      One More question, How do I get ListBox1 to show data from multiple sheets?

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hello,

    I have the same question as Akshay. Is there a way to do combo boxes and text boxes? I got the same error message as Akshay.

    Any help would be very, very much appreciated.

    Kind regards,
    Jacquie

    ReplyDelete
  9. Links are broken... Unable to download..

    ReplyDelete
    Replies
    1. No , links are valid. I using link shortening service and pop ads in links. You can download example files from Dropbox without signing.

      Delete
  10. Sir, I need the New Userform Example VBA Code. Can u please send to my email or dropbox account.
    Email : ylnvprasadrao5@gmail.com

    ReplyDelete
    Replies
    1. Example workbook and it's codes can be downloaded here : http://adf.ly/1S8y8n

      Delete
  11. Your google drive download link is down

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. thank you so much for adding copy to page

    i want to Reflects columns in list box to become from tight to left To fit with the Arabic language؟

    ReplyDelete
  15. Hi Akshay!

    Thanks a lot for your Template.

    I am trying to upgrade the search function as follows: i would like to find a Word (string) inside a cell. right now the codes only looks for the first word in the cell. how would you change the logic to search the word inside the whole cell ?

    thanks for your reply.

    ReplyDelete
  16. Hi I would like to incluide more text to the excel listbox, but only va read information till column L. Hoiw can I write information in Column M, N, and to see in VBA the info in the Listbox?.
    Thank you.

    ReplyDelete
  17. Hello there,
    First let me thank you for this great webpage with full of useful VBA temples.
    I have been working on my project which is pretty close to your temple, except I have added 4 search boxes, and I'm trying to narrow down my search function which included option box and check box as well. I would be greatly appreciated if you can help me out with this issue.

    ReplyDelete
  18. Excelent Userform, very nicely done.
    I'm trying to use your code but my form has more than 50 columns.
    What is the recommendation?

    Is there any code so we can increase the number of columns according to our need?

    It may even get a little slow but this will help me a lot.

    All help will be appreciated.

    ReplyDelete
  19. thanks so much for the great user form that you created.

    I just would like to know that is it possible to add the picture related with each data entry.

    thanks

    ReplyDelete
  20. hi is it possible to contact you ?? my email address is jay.purshottam@exclusive.co.uk

    ReplyDelete
  21. message as shown.. "Your requested URL has been blocked as per the directions received from Department of Telecommunications, Government of India. Please contact administrator for more information."

    ReplyDelete
  22. It´s possible to add a hyperlink field that goes a pdf file?

    Thx

    ReplyDelete
    Replies
    1. Hi,
      Do you want to create pdf file from Excel file?

      Delete
  23. Hi sir great work
    Want to know about add photo option in the data entry form is it possible i am sending a sample template IF you add the code

    ReplyDelete
  24. Need code for image entry to this form

    ReplyDelete
  25. An attachement in email i Sent need YOUR kind consideration to it

    ReplyDelete