Userform question

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,210
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,743
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,743
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,210
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,743
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,210
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,743
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,210
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,095,720
Messages
5,446,131
Members
405,383
Latest member
mccordscvs

This Week's Hot Topics

Top