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

Put this script into your ComboBox Replacing previous one I gave you.

You will need a UserForm ListBox named ListBox1

This script will load all the Visits into the ListBox

And will also perform all the previous things it did with script in Post 18

'Modified  5/20/2019  8:26:43 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Dim lastrow As Long
Dim i As Long
Dim r As Long
Dim b As Long
Dim Lastcolumn As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B2:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
For i = 1 To 7
    Controls("TextBox" & i).Value = Cells(SearchRange.Row, i * 2).Value
Next
Lastcolumn = Cells(SearchRange.Row, Columns.Count).End(xlToLeft).Column
ListBox1.Clear
For b = 20 To Lastcolumn
    ListBox1.AddItem Cells(SearchRange.Row, b).Value
Next
End Sub

 

[ipbr21054]

Re: Userform question if I may ask please

Hi,
Very nice & works perfect thanks.

A couple of aditional questions if i can ask please.
TextBox6 regards to payment received.
Currently for my example xustomer i see on the userform 35
Can you advise what i need to do please so i see £35.00

The other question ive since forgot so will thank on it.

have a nice day

 

[My Aswer Is This]

Re: Userform question if I may ask please

Try this:

Private Sub ComboBox1_Change()
'Modified  5/20/2019  9:26:43 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Dim lastrow As Long
Dim i As Long
Dim r As Long
Dim b As Long
Dim Lastcolumn As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B2:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
For i = 1 To 7
    Controls("TextBox" & i).Value = Cells(SearchRange.Row, i * 2).Value
Next
[COLOR=#ff0000]Me.TextBox6.Value = Format(Me.TextBox6.Value, "?#,##0.00")[/COLOR]
Lastcolumn = Cells(SearchRange.Row, Columns.Count).End(xlToLeft).Column
ListBox1.Clear
For b = 20 To Lastcolumn
    ListBox1.AddItem Cells(SearchRange.Row, b).Value
Next
End Sub

 

[My Aswer Is This]

For some reason this forum does not like the pound symbol

It takes £ symbol and turns it into a ?

So replace ? with the pound symbol

 

[ipbr21054]

All done and great thanks.

I will post back here when i need some extra help in a day or two.

Thanks

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

All done and great thanks.

I will post back here when i need some extra help in a day or two.

Thanks

 

[ipbr21054]

Hi,
I have done the following in every other cell,so below example is for Jeff,

Cell T11 01/02/2019
Cell U11 £30.00

Cell V11 01/03/2019
Cell W11 £40.00

Cell X11 01/04/2019
Cell Y11 £50.00

Now when i select jeff using ComboBox1 i would like if possible to see the below in the ListBox1

01/02/2019
£30.00
01/03/2019
£40.00
01/03/2019
£40.00

Private Sub ComboBox1_Change()'Modified  5/20/2019  8:26:43 AM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Dim lastrow As Long
Dim i As Long
Dim r As Long
Dim b As Long
Dim Lastcolumn As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B11:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
For i = 1 To 9
    Controls("TextBox" & i).Value = Cells(SearchRange.Row, i * 2).Value
Next
Me.TextBox6.Value = Format(Me.TextBox6.Value, "£#,##0.00")
Lastcolumn = Cells(SearchRange.Row, Columns.Count).End(xlToLeft).Column
ListBox1.Clear
For b = 20 To Lastcolumn
    ListBox1.AddItem Cells(SearchRange.Row, b).Value
Next
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 9
        With Me.Controls("TextBox" & i)
        If .Text = "" Then
        MsgBox Choose(i, "Name", "Where Advert Was Seen", "Telephone Number", _
                                "Post Code", "Area", "Paid", "Next Cut", "Mileage", "Address") & _
                                " 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 9
        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

 

[My Aswer Is This]

I believe all your needing is to have a Pound symbol entered into the listbox when the value is a number.
Otherwise my script should be doing what you want.

Try this:
This forum may change the Pound symbol to a question mark if so change that line of code marked in red and put in a pound symbol where this forum puts in a question mark.

Private Sub ComboBox1_Change()
'Modified  5/22/2019  2:09:30 PM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = ComboBox1.Value
Dim lastrow As Long
Dim i As Long
Dim r As Long
Dim b As Long
Dim Lastcolumn As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Set SearchRange = Range("B2:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
For i = 1 To 7
    Controls("TextBox" & i).Value = Cells(SearchRange.Row, i * 2).Value
[COLOR=#ff0000]Next
Me.TextBox6.Value = Format(Me.TextBox6.Value, "?#,##0.00")[/COLOR]
Lastcolumn = Cells(SearchRange.Row, Columns.Count).End(xlToLeft).Column
ListBox1.Clear
For b = 20 To Lastcolumn
If IsNumeric(Cells(SearchRange.Row, b).Value) Then
    [COLOR=#ff0000]ListBox1.AddItem "?" & Cells(SearchRange.Row, b).Value[/COLOR]
Else
    ListBox1.AddItem Cells(SearchRange.Row, b).Value
End If
Next
End Sub

 

[ipbr21054]

Thanks,
That now gives me £35

Various symbols ive tried so it shows £35.00 but unlucky,please advise correct symbols so i see £35.00

Thanks

 

[Sharid]

Re: Userform question if I may ask please

This was something I was building for someone about a year ot two back, might give you a bit of an idea on layout. Never did finish it and it still has a few bugs.

https://app.box.com/s/whdubynnom8k4whkv3lcnogya6ygu80i

Hope it helps