Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 43

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

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

    Default 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
    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"

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

    Default 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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

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

    Default 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
    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
    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"

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

    Default Re: Userform question

    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
    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"

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

    Default Re: Userform question

    All done and great thanks.

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

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

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

    Default Re: Userform question

    Glad I was able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by ipbr21054 View Post
    All done and great thanks.

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

    Thanks
    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. #37
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,047
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform question

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

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

    Default Re: Userform question

    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
    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
    If IsNumeric(Cells(SearchRange.Row, b).Value) Then
        ListBox1.AddItem "?" & Cells(SearchRange.Row, b).Value
    Else
        ListBox1.AddItem Cells(SearchRange.Row, b).Value
    End If
    Next
    End Sub
    Last edited by My Aswer Is This; May 22nd, 2019 at 02:12 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"

  9. #39
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,047
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform question

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  10. #40
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    479
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    If I could code in VB, life wouldn't be such a pain in the A$$

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
  •