Page 1 of 5 123 ... LastLast
Results 1 to 10 of 43

Thread: Userform question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,213
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Userform question

    I知 thinking of making a userform for my grass cutting business.

    I知 not entirely sure how to approach it yet but something like select a customers name & the relevant info fields are then shown.

    One thing I知 stuck with is some customers have repeat visits so maybe by clicking on a fields drop down arrow would then show a list of dates when I致e visited them, or any other suggestions would be good.

    Thanks.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,449
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Userform question if I may ask please

    We need some specific details like
    Normally I believe users show use rows as records and columns as fields
    So in your case put "Name" in Range("A1") and address in Range("B1") and so on maybe like Phone Number in C1
    Then enter all your customer names in A2 and below and address in B2 and below
    And then enter last service dates in the columns to the right.
    Like first visit in G10 and next visit in G11 and next visit in G12 and so on.
    Now if you want to see all your Visits in a Userform Listbox you could do this:


    Load all the Names in column A into a Combobox in your userform.
    Then you can click on a Name in the combobox and have all the visit dates loaded into the Listbox

    This keeps you from needing a whole lot of textboxes to load visit dates into.

    Would this be something you may want.

    Other wise tell me what you want with more details.
    Having a lot of textboxes with visit dates is not a good plan it would require a on going need to add more Text Boxes.

    But one listbox can have hundreds of visit dates with very little code and grows on it own as you make more visits.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,392
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Userform question if I may ask please

    Have a look here for step by step instructions ( with videos and examples) on creating UserForms

    https://www.contextures.com/xlUserForm01.html
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,841
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Userform question if I may ask please

    Hi,
    just a personal thought - whilst Excel can be made to function as a simple database, it may be worthwhile vising the Access Database Forum & see if anyone there can assist with your project.

    Also, when keeping customer records need to be aware of General Data Protection Regulation (GDPR) 2018
    https://www.gov.uk/government/public...ion-regulation

    Dave

  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,213
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform question if I may ask please

    MAIT

    Here is what i have at present,
    Thought i would show you this & then you could advise,might be easier ??

    I like the idea of clicking a name from the combobo & have it show visit dates etc.
    This form allows me to input info & have it then saved on the worksheet,at present there is no provision to select a name & show the info from worksheet on the userform but this might be the way forward.

    Code:
    Private Sub CommandButton1_Click()Unload GrassForm
    End Sub
    Private Sub CommandButton2_Click()
        Dim i As Integer
        Dim LastRow As Long
        Dim wsGRASS As Worksheet
        
        Set wsGRASS = ThisWorkbook.Worksheets("GRASS")
        
        For i = 1 To 7
            With Me.Controls("TextBox" & i)
            If .Text = "" Then
            MsgBox Choose(i, "Name", "Where Advert Was Seen", "Telephone Number", _
                                    "Post Code", "Area", "Paid", "Last Cut") & _
                                    " Not Entered", vbCritical, "GRASS CUTTING SHEET"
                .SetFocus
                Exit Sub
            End If
            End With
        Next i
    
    
    
    
        With wsGRASS
            LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
        End With
        
        For i = 1 To 7
            With Me.Controls("TextBox" & i)
            wsGRASS.Cells(LastRow, i * 2).Value = .Text
                .Text = ""
            End With
        Next i
        MsgBox "GRASS CUTTING SHEET UPDATED", vbInformation, "GRASS CUTTING SHEET"
        TextBox1.SetFocus
    End Sub
    Private Sub TextBox1_Change()
        TextBox1 = UCase(TextBox1)
    End Sub
    Private Sub TextBox2_Change()
        TextBox2 = UCase(TextBox2)
    End Sub
    Private Sub TextBox3_Change()
        TextBox3 = UCase(TextBox3)
    End Sub
    Private Sub TextBox4_Change()
        TextBox4 = UCase(TextBox4)
    End Sub
    Private Sub TextBox5_Change()
        TextBox5 = UCase(TextBox5)
    End Sub
    Private Sub TextBox6_Change()
        TextBox6 = UCase(TextBox6)
    End Sub
    Private Sub TextBox7_Change()
        TextBox7 = UCase(TextBox7)
    End Sub
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,449
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Userform question if I may ask please

    I will Look at this:

    Are you happy with this?
    If not what would you like to change
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,213
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform question if I may ask please

    I am happy with how I input data & send to worksheet.

    Maybe add combobox,listbox to do what you suggested.
    Also think about not only select customer name and have it show dates in the listbox but also get data from worksheet & show on userform tel number address price paid etc etc.

    What do you think ?
    Shall I advise you what cell the tel number address price paid etc etc are in or please advise the info you need.

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,449
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Userform question if I may ask please

    I tested your code and it looks really nice and works well.
    Now let me read your next post and see what I can suggest
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,449
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Userform question if I may ask please

    OK I have now read your second post

    So tell me how you have your sheet laid out

    What is in column A
    What is in column B

    Do you have it laid out like I earlier mentioned

    Each Customer has a separate record or Row as excel calls it?

    If so tell me what's in each column

    Like Column A stating in Row(2) I have Customer names
    And column B has address and column C has Phone and on and on.

    And if you enter all service dates where are they shown

    Like if column A to G has Personal info and last service dates start in column H tell me that.

    Or do you just keep track of last service date and not all previous service dates

    If you will supply that information I will see if I can help.

    And if you want personal data entered into Text Boxes what data goes in what Box

    And just a question

    Do you know what a UserForm Multipage is?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,449
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Userform question if I may ask please

    And your script worked well but I really did not know what type data it was putting into the sheet

    Would you care to tell me what it was entering was it the Personal information Like name address phone number for new customers or something else like last service dates
    Last edited by My Aswer Is This; May 18th, 2019 at 03:33 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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