Userform question

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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

Code:
'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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Re: Userform question if I may ask please

Try this:
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
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

Code:
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
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
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.

Code:
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
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,074
Messages
5,466,464
Members
406,483
Latest member
Shlammed

This Week's Hot Topics

Top