Userform question

[ipbr21054]

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

I’m 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’m 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’ve visited them, or any other suggestions would be good.

Thanks.

 

[My Aswer Is This]

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.

 

[Michael M]

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

 

[dmt32]

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/publications/guide-to-the-general-data-protection-regulation

Dave

 

[ipbr21054]

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.

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

 

[My Aswer Is This]

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

 

[ipbr21054]

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.

 

[My Aswer Is This]

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

 

[My Aswer Is This]

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?

 

[My Aswer Is This]

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