Monday, June 27, 2016

A Simple And Useful Userform (Address Book)

          I updated the userform that I did previously to run it in all version of Excel.

With this userform ;
- Data can be added
- Data can be deleted
- Data can be edited
- Data can be searched on the sheet
- Listbox can be filled and emptied with button.
- Listbox can be scrolled with the spin button.

          Id numbers are generated automatically  when new record is added to the worksheet and  when record is removed.
The listbox is empty when the userform is opened.
The entered value in the searching box can be searched  on the worksheet. listbox are filled with the found results .


          The listbox column widths is automatically adjusted according to the widths of the sheet's column with Vba codes.

The Address Book (Userform's Listbox Contains 12 Column)

            A follower of the blog wanted this example.
Maximum 10 columns can be added to the listbox with ListBox1.AddItem Method. To solve this problem, we have filled the listbox an array.

Do
            m = m + 1
            ReDim Preserve myarr(1 To 12, 1 To m)
            For j = 1 To 12
                myarr(j, m) = .Cells(k.Row, j + 1).Value
            Next j
            Set k = .Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).FindNext(k)
        Loop While Not k Is Nothing And k.Address <> adrs
        ListBox1.Column = myarr
......

Also we added a new macro  to avoid duplicate data entry in Column B (names in this column).


46 comments:

  1. Dear All
    My issue is that can we search multiple search in excel sheet in one click. Kindly guide : abuhafiz61@live.com

    ReplyDelete
  2. Dear Sir,
    This VBA also show values of hidden cells can you exclude that values. waiting for your reply

    ReplyDelete
    Replies
    1. I could not fully understand

      Delete
    2. hi sir can you please email me please jeanesky@yahoo.com I have an excel PROJET

      Delete
  3. I mean when we search then if suppose we have hidden the row then the search result also shows the values of hidden row.

    ReplyDelete
  4. Hi ,

    May i know your email id, i need help for VBA.

    ReplyDelete
  5. please reach me @ b.ranjitbcom@gmail.com

    ReplyDelete
  6. sir i have found your tutorial very useful and thats helps me in many ways thanks a lot. Now im having a new issue... how could i add 12 textboxes values to a listbox in a userform it only allowing me 10 textboxes. plz help me sir i couldn't go futher in this project at the moment

    ReplyDelete
    Replies
    1. thank you very much sir........ looking for that tutorial........ i got stuck with this project at the moment....think you u will post it asap.....

      Delete
    2. THANK YOU VERY MUCH SIR

      Delete
    3. I created new template :
      The Address Book (Userform's Listbox Contains 12 Column)

      You can download it.

      Delete
    4. omgggg thank u very much sir it helps me a lottttttttt. i don't have words to say thanks because just thanks is not enough....

      Delete
  7. I don't know where am i making mistake when i take this code to my project. what am i trying is 12 textboxes value to listbox by click of a button and afterwards i click the button to transfer my listbox items to a closed worksheet. so is it possible sir to do that with ur new coding?

    ReplyDelete
    Replies
    1. You must make changes into codes.Have you examined this template : https://merkez-ihayat.blogspot.com.tr/2016/06/adding-data-into-closed-workbook-with.html

      Delete
    2. the link u has given is fine sir but the issue comes when i try to add 12textbox value to listbox because the 12textbox values going to be first added to listbox not to the worksheet. Afterwards i have another separate button to add listbox items to workbook. So first i need is how to add 12textbox values to listbox before data going to worksheet

      Delete
    3. Take a look at to new template :https://merkez-ihayat.blogspot.com/2016/09/excel-vba-copy-listbox-items-into.html

      Delete
    4. AWESOME TUTORIAL SIR U R SUCH A GENIUS... YOU HELPS ME ALOT THANK YOU SIR. FOR YOU KIND ATTENTION THIS TUTORIAL IS HELPING FOR MY SECOND PART OF THE PROJECT. BUT I NEED 12TEXTBOES TO LISTBOX WHERE I'VE STRUGGLING FOR NEARLY 1 MONTH. EARLIER YOUR TUTORIAL IS 12 COLUMNS FROM WORKSHEET TO LISTBOX. BUT I NEED 12TEXTBOXES TO LISTBOX SIR. CAN YOU HELP ME WITH THAT SIR PLZ?

      Delete
    5. I do not understand exactly what you want. How is "12 text boxes to listbox" ?

      Delete
    6. yes you are correct sir. 12 text boxes to listbox

      Delete
    7. can u help me with that sir?

      Delete
    8. I do not understand exactly what you want . Can you describe on a picture

      Delete
    9. sure sir can u give me your mail id so that i can send u the picture sir.

      Delete
    10. thank you very much sir i have sent u the pic and explained it also... hope u find it and reply me soon. Thanks for the support that u are giving to me sir........

      Delete
    11. ok sir i have sent u the file also and mentioned the issues im facing. hope find it sir.

      Delete
  8. Hello

    I'm trying to download the file, but for some reason is not working.

    Could you please send by mail: estevam.castelaro@hotmail.com

    thank you

    ReplyDelete
    Replies
    1. The template can be downloaded from Dropbox . To download the file does not need to be your dropbox account.You can press the "Direct Download" link for it.

      Delete
    2. I got it Sir, This tool is pretty amazing.
      Thank very much

      Delete
  9. Hello Sir
    I want to use 2 VBA code as seen below in same one sheet, how can i do this , please advice !!

    First Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    FontSize = ActiveCell.Font.Size
    LargeSize = FontSize * 1.6
    Cells.Font.Size = FontSize
    ActiveCell.Font.Size = LargeSize
    End Sub

    Second Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    On Error Resume Next
    With Target
    If .Count = 1 Then
    str = .Address & "," & .Row & ":" & .Row _
    & "," & Left(.Address, InStr(2, .Address, "$") - 1) & ":" _
    & Left(.Address, InStr(2, .Address, "$") - 1)
    End If
    End With
    Range(str).Select
    On Error GoTo 0
    End Sub

    ReplyDelete
    Replies
    1. Try it :

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim str As String,FontSize, LargeSize As Byte
      On Error Resume Next
      With Target
      If .Count = 1 Then
      str = .Address & "," & .Row & ":" & .Row _
      & "," & Left(.Address, InStr(2, .Address, "$") - 1) & ":" _
      & Left(.Address, InStr(2, .Address, "$") - 1)
      End If
      End With
      Range(str).Select
      FontSize = ActiveCell.Font.Size
      LargeSize = FontSize * 1.6
      Cells.Font.Size = FontSize
      ActiveCell.Font.Size = LargeSize
      End Sub

      Delete
  10. I want to put date in Column A and want in same column as soon as date is entered it should give me number of days till date , how to make it possible through VBA Excel code

    ReplyDelete
  11. No their is date in column A, for e.g 10/09/2016 and i want number of days like 5 (days) till date.

    ReplyDelete
    Replies
    1. I made an example file .Take look at this link :https://merkez-ihayat.blogspot.com/2016/09/excel-vba-calculating-days-between-two.html

      Delete
    2. Sir, the link you provided that VBA code gives values in days in TextBox but i want in column A, please give me VBA code. Thank You

      Delete
    3. Take look at this link :https://merkez-ihayat.blogspot.com/2016/09/calculating-days-between-two-dates-2.html

      Delete
    4. Sir
      Can you give me code , i have not expert in excel VBA i am not able to fetch the code from this file. ( Date to days)

      Delete
    5. Did you download template ? If you downloaded template ,open it. Press Alt + F11 keys. Vba window is opened.You can view the codes there.

      Delete
  12. Hai sir
    I just to drop a very important question.how u hide the worksheet behind the userform ?? And how u add code for show sheet button. Please help me with this.
    Tq
    Thank you very much

    ReplyDelete
    Replies
    1. Is there a sollution for allready?

      Delete
  13. Selam. Bi konuda yardımcı olurmusunuz?

    ReplyDelete