Trying to Create a Remove Last Button

[cpounds217]

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)

[paulked]

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!

[cpounds217]

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!

[paulked]

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

[cpounds217]

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

[paulked]

Can you give me an example of our string please, I thought there was only one semicolon involved!

Book2.xlsm

[paulked]

PS, I'm off to the pub with the boys in a bit. You may have to wait until morning!

[cpounds217]

###-###-### Chemical Name; ##-##-#### Chemical Name; etc.

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

[paulked]

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

[paulked]

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.

[cpounds217]

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.

[paulked]

Got it. Will get the solution when I get home (having 🍻 with the boys at the mo!)

[cpounds217]

Enjoy, I will enjoying some adult beverages at a local brewery here shortly!

Thanks for your help!

[paulked]

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

[paulked]

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

[SamT]

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

[cpounds217]

I am a little confused because I do not have any cells involved with the text box I am deleting from...

[paulked]

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

[paulked]

Run that code in a new worksheet or workbook and look at the results, or download the file I posted.

[cpounds217]

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?