Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 37

Thread: Trying to Create a Remove Last Button

  1. #1
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location

    Exclamation Trying to Create a Remove Last Button

    Hello All,

    I am new to these forums, and relatively new to VBA in Excel. I have created a userform to input a bit of data. One of the things I needed to do was create a ListBox of over 350 items that was searchable. I accomplished this, but in order for my data to be input into the worksheet correctly, I had to create an Add to List button, and move the selected option to a text box. I also created a remove all button to empty the box. Currently, I am trying to create a delete last entry button so that the very last entry is deleted. All entries are followed by the string “; “, so I could use this as the marker before the last item, but I cannot provide a character count, as each item varies in length (this is a hazmat form, and has CAS no, and varying length chemical names).

    I have tried creating a function:

    Private Function LastString() as integer
    str = SelectedEHSList.Value
    LastString = InStrRev(str, “;”, 2, vbTextCompare)
    End Function

    Then I would call it in the sub for the RemoveLast_Click()

    Problem is, I do not know how to turn the LastString value into the amount of characters -1 that I want to remove.

    Any help is greatly appreciated, this is work-related project. The remove last button isn’t necessary, as I have the remove all button, but it would be a nice convenience for the end-user (especially as this form will be used a few hundred to thousand times each year).

    Thank You!

    PS: my home computer, which is what I will be using for most of my chatting on here is an iPad Pro, which Apple has yet to outfit with coding capabilities (sadly), and my work computer is a secured device where I am not supposed to download. So please post any suggestions as code within for me to reference. Thanks! (Also means I can’t attach)

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Hi there, welcome to the forum!

    If you play around with this it should help!

    Sub test()
        MsgBox Len(Left("I like red wine: But also like white!!", _
            InStrRev("I like red wine: But also like white!!", ":", -1)))
    End Sub
    Best regards

    Paul Ked

    PS The -1 above is to tell InStrRev to use the compare option.

    Sub test()
        MsgBox Len(Left("I like red wine: But also like white!!", _
            InStrRev("I like red wine: But also like white!!", ":", -1)-1))
    End Sub
    The second -1 is to give you the position you asked for!
    Last edited by paulked; 11-30-2017 at 07:11 PM. Reason: Added the extra -1
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location
    The problem I see with your suggestion is that I cannot put an exact line of text, such as “I like red wine...”, as the line of text will be subject the number of chemicals they select and the names of those chemicals. I remember coming across a method for an unspecified length, but I am having trouble locating that information. If you could help me with that, it would be greatly appreciated!

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Hi there.

    You can put any string in place of the "I like..."

    It could be a cell reference eg Sheet1.Cells(2,4) or Sheets("Sheet1").Range("AB34") etc

    It could be a variable eg MyStrng, Str etc

    I would do a test on the string before the InStrRev to make sure that a) it is not empty and b) it has the character you are looking for, otherwise it mat error.

    I'd give you an example, but I'm in a rush, sorry.

    Can give you one later if you are struggling or no-one else has replied.

    Hope this has helped

    Paul Ked
    Semper in excretia sumus; solum profundum variat.

  5. #5
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location
    Ok so the code you provided gives me the entire length of the text box string. Now I need to narrow it down to the length of the last string only:

    Private Function FindLastEntry () As String
    dim str as string

    str = SelectedEHSList.Value

    if str <> “” then
    FindLastEntry = Len(Right(str, InstrRev(str, “; “, -1) -1))
    End if

    End Function

    For testing purposes I have this set as a call and if not empty returns a message box showing the FindLastEntry value, which as I stated as returned the entire text box. Now I need to turn that into the last string of text, which is housed between “; “ and “; “, but I want to keep the first semi-colon. So i need to return the string length in there minus 1, and then delete that amount.

    Thanks

  6. #6
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Can you give me an example of our string please, I thought there was only one semicolon involved!

    Book2.xlsm
    Last edited by paulked; 12-01-2017 at 11:23 AM.
    Semper in excretia sumus; solum profundum variat.

  7. #7
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    PS, I'm off to the pub with the boys in a bit. You may have to wait until morning!
    Semper in excretia sumus; solum profundum variat.

  8. #8
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location
    ###-###-### Chemical Name; ##-##-#### Chemical Name; etc.

    I have it set so that after each chemical name is inserted a “; “ is inserted.

  9. #9
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    May have got it now:

    Book2.xlsm

    Sub Button1_Click()
        Dim str As String, Endstr As String
        str = Cells(1, 1)
        Endstr = Left(str, (InStrRev(str, "; ", -1)))
        MsgBox str & vbCr & Endstr
    End Sub
    Semper in excretia sumus; solum profundum variat.

  10. #10
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Quote Originally Posted by paulked View Post
    May have got it now:

    Book2.xlsm

    Sub Button1_Click()
        Dim str As String, Endstr As String
        str = Cells(1, 1)
        Endstr = Left(str, (InStrRev(str, "; ", -1)))
        MsgBox str & vbCr & Endstr
    End Sub

    That will give you the string you want left.
    Semper in excretia sumus; solum profundum variat.

  11. #11
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location
    That counts only the last “; “, but I need the second to last. And I have tried to change the “start” placement but it never returns a value when I do that. So it ends up giving me the character count for the entire string of text from the very last “;”, instead of second to last.

  12. #12
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Got it. Will get the solution when I get home (having 🍻 with the boys at the mo!)
    Semper in excretia sumus; solum profundum variat.

  13. #13
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location
    Enjoy, I will enjoying some adult beverages at a local brewery here shortly!

    Thanks for your help!

  14. #14
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Good day.

    This may do it!

    Sub Button1_Click()
        Dim Str As String, Str1 As String, Str2 As String
        Str = Left(Cells(1, 1), Len(Cells(1, 1)) - 2)     'Take off the "; " added programatically
        Str1 = Left(Str, (InStrRev(Str, ";", -1)))        'The list without the last entry
        Str2 = Mid(Str, InStrRev(Str, ";") + 1)           'The last entry with a programatically added " "
        Str2 = Right(Str2, (Len(Str2) - 1))               'Trim off that space
        MsgBox Str1 & vbCr & Str2
    End Sub
    Best regards

    Paul Ked
    Semper in excretia sumus; solum profundum variat.

  15. #15
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Add +1 to the formula to keep the space after the semicolon:

        Str1 = Left(Str, (InStrRev(Str, ";", -1) + 1))      'The list without the last entry
    RemoveLastEntry.xlsm
    Semper in excretia sumus; solum profundum variat.

  16. #16
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    Currently, I am trying to create a delete last entry button so that the very last entry is deleted.
    I'm sure there's an error in here, but you get the idea
    Dim Tmp As Variant
    Tmp = ListBox1.List
    Redim Preserve Tmp, UBound(Tmp) - LBound(Tmp)
    ListBox1.List = Tmp
    - LBound(Tmp) could be -0 or -1, depending on how your VBA is set up
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #17
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location
    I am a little confused because I do not have any cells involved with the text box I am deleting from...

  18. #18
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    the cell references are there for testing only as I don't have your code! replace the cells with the strings as I said in post #5
    Semper in excretia sumus; solum profundum variat.

  19. #19
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Run that code in a new worksheet or workbook and look at the results, or download the file I posted.
    Semper in excretia sumus; solum profundum variat.

  20. #20
    VBAX Regular cpounds217's Avatar
    Joined
    Nov 2017
    Location
    Albany
    Posts
    49
    Location
    Attached is a screenshot of the code. Everytime I run it, my MsgBox gives me the entire value of the textbox (the list), and then returns the value 16 for Str2. And 16 is returned no matter the length of the value in the textbox, for example the one test was only “No EHS; “, while another was a much longer string of text, but both returned 16.

    Thoughts?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •