PDA

View Full Version : [SOLVED] Trying to Create a Remove Last Button



cpounds217
11-30-2017, 01:02 PM
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
11-30-2017, 04:13 PM
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
12-01-2017, 08:44 AM
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
12-01-2017, 09:04 AM
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
12-01-2017, 09:46 AM
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
12-01-2017, 11:02 AM
Can you give me an example of our string please, I thought there was only one semicolon involved!

21099

paulked
12-01-2017, 11:03 AM
PS, I'm off to the pub with the boys in a bit. You may have to wait until morning!

cpounds217
12-01-2017, 11:26 AM
###-###-### Chemical Name; ##-##-#### Chemical Name; etc.

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

paulked
12-01-2017, 11:29 AM
May have got it now:

21100



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
12-01-2017, 12:07 PM
May have got it now:

21100



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
12-01-2017, 01:03 PM
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
12-01-2017, 01:22 PM
Got it. Will get the solution when I get home (having 🍻 with the boys at the mo!)

cpounds217
12-01-2017, 02:04 PM
Enjoy, I will enjoying some adult beverages at a local brewery here shortly!

Thanks for your help!

paulked
12-02-2017, 04:03 AM
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
12-02-2017, 04:20 AM
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


21101

SamT
12-02-2017, 12:19 PM
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
12-04-2017, 07:08 AM
I am a little confused because I do not have any cells involved with the text box I am deleting from...

paulked
12-04-2017, 07:13 AM
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
12-04-2017, 07:15 AM
Run that code in a new worksheet or workbook and look at the results, or download the file I posted.

cpounds217
12-04-2017, 07:35 AM
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?

cpounds217
12-04-2017, 07:38 AM
Ok photo attachment did not work:

Dim Str as String, Str1 as String, Str2 as String

Str = SelectedEHSList.Value
Str1 = Left(Str, (InStrRev(Str, “;”, -1)))
Str2 = Mid(Str, InStrRev(Str, “;”) +1)
Str2 = Right(Str2, (Len(Str2) -1))
MessageBox

paulked
12-04-2017, 07:47 AM
1. What does the RemoveLastEntry.xlsm give you? Try changing the string in A1 and see what difference that makes.

2. What is the value of SelectedEHSList? Can you post that?

3. Can you attach your workbook? If not, then at least the code that is associated with the SelectedEHSList.

4. MessageBox. Post the code from that sub.

cpounds217
12-04-2017, 07:54 AM
I did not use the provided excel you did, as I cannot download onto my personal computer (ipad, no coding allowed), and I can’t download at work either.

The value of SelectedEHSList varies based upon the selections made in the EHSList (listbox).

I cant attach my workbook, but:

Private Sub SelectedEHSList_Change (this is all validation code only)

The important code is in the button that moves the selection from the listbox to the textbox:

Private Sub AddSelected_Click()

Dim Str as String
Dim i as Long

For i = 0 to EHSList.ListCount - 1

If EHSList.Selected(i) then
If Str <> vbNullString Then Str = Str & “; “
Str = Str & EHSList.List(i) & “; “
End If

Next

SearchChemical.Value = “”
EHSList.TopIndex = 0

SelectedEHSList.text = SelectedEHSList.Text & Str

cpounds217
12-04-2017, 07:54 AM
The Message Box Code is exactly what you provided:

MsgBox Str1 & vbCr & Str2

paulked
12-04-2017, 08:13 AM
have you tried:



Str = SelectedEHSList.Text


as in your post #22?

Also what does



MsgBox Str & vbCr & Str1 & vbCr & Str2



give you?

21107

I get this.

paulked
12-04-2017, 08:17 AM
That should be


MsgBox Str & vbCr & Str1 & vbCr & Str2


Won't let me edit it out!

cpounds217
12-04-2017, 08:26 AM
When using exactly what you provided above for the MsgBox I get:

50-07-7 Mitomycin; 50-14-6 Ergocalciferol; No EHS;
50-07-7 Mitomycin; 50-14-6 Ergocalciferol; No EHS;

And the Ok button.

cpounds217
12-04-2017, 08:35 AM
Current RemoveLast_Click()
Dim Str As String, Str1 As String, Str2 As String


Str = SelectedEHSList.Text
Str1 = Left(Str, (InStrRev(Str, ";", -1)))
Str2 = Mid(Str, InStrRev(Str, ";") + 1)
Str2 = Right(Str2, (Len(Str2) - 1))
MsgBox Str & vbCr & Str1 & vbCr & Str2


AddSelected_Click Sub:

Private Sub AddSelected_Click()


Dim Str As String
Dim i As Long


For i = 0 To EHSList.ListCount - 1

If EHSList.Selected(i) Then
If Str <> vbNullString Then Str = Str & "; "
Str = Str & EHSList.List(i) & "; "
End If


Next

SearchChemical.Value = ""
EHSList.TopIndex = 0

SelectedEHSList.Text = SelectedEHSList.Text & Str
End Sub

Below is a SnipIt of the Userform (Page 2), that I am working with.
21108


I also have a search function to search within the listbox:

Private Sub SearchChemical_Change()


Dim MyList() As Variant
Dim X As Long
Dim Y As Long
Dim FoundSomething As Boolean
FoundSomething = False
Y = 0
For X = 2 To wsLists.Range("J" & Rows.Count).End(xlUp).Row
If InStr(1, UCase(wsLists.Range("J" & X).Value), UCase(SearchChemical)) > 0 Then
FoundSomething = True
ReDim Preserve MyList(Y)
MyList(Y) = wsLists.Range("J" & X).Text
Y = Y + 1
End If
Next
If FoundSomething Then
EHSList.List = MyList
Else
EHSList.Clear
End If


End Sub


Validation code for SelectedEHSList:

Private Sub SelectedEHSList_Change()
If SelectedEHSList.Value <> "" Then
SelectedEHSList.Locked = True
End If

If SelectedEHSList <> "" Then
SelectedEHSList.BackColor = rgbWhite
SelectedEHSListLabel.ForeColor = Me.ForeColor
SelectedEHSListLabel.Caption = "Selected EHS List"
SelectedEHSList.Locked = True
SelectedEHSList.Enabled = False
End If

If SelectedEHSList.Text = "No EHS; " Then
AddSelected.Enabled = False
EHSList.Enabled = False
SearchChemical.Enabled = False
End If

End Sub

The Userform Initialize Event:



Private Sub UserForm_Initialize()


Tier2Pages.Value = 0
DateFiled.Value = Date
County.List = Range("NYSCounties").Value
SubjectComboBox.List = Range("SubjectList").Value

EHSList.List = wsLists.Range("J2", wsLists.Range("J1").End(xlDown)).Value

End Sub

paulked
12-04-2017, 09:01 AM
So what do you get in your msgbox when you run Remove_Last???? Whatever it is you run as it's not in a Private Sub!

cpounds217
12-04-2017, 09:22 AM
What you see below:

21109

paulked
12-04-2017, 09:35 AM
I think you missed my post #15... Take off the two characters added programatically.

This will do it:



Private Sub RemoveLast_Click()
Dim Str As String
Str = SelectedEHSList.Text
Str = Left(Str, Len(Str) - 2)
Str = Left(Str, (InStrRev(Str, ";", -1) + 1))
SelectedEHSList.Text = Str
End Sub

paulked
12-04-2017, 09:37 AM
It takes the last entry off on every click until there is nothing left (you may want to trap that error!)

cpounds217
12-04-2017, 09:43 AM
Thank you very much. We can mark this as SOLVED!

cpounds217
12-04-2017, 09:55 AM
Hold the phone! Or door...depending on your preference in TV cliches...

For some reason it tends to leave 1 character left if it comes to the last string of text.

How can I test this with an If statement. i was thinking something along the lines of:

Dim i as integer

i = Len(SelectedEHSList.Text)

If i < 2 then
removelast.enabled = false
else: removelast.enabled = true
End if

Except that returns a bug and highlights the code line:
str = left(str, len(str) - 2)

Thoughts?

paulked
12-04-2017, 10:23 AM
As I said it would error out. If you put the trap in:



Private Sub RemoveLast_Click()
Dim Str As String
Str = SelectedEHSList.Text
If Len(Str) < 2 Then
Str = ""
GoTo Xit
End If
Str = Left(Str, Len(Str) - 2)
Str = Left(Str, (InStrRev(Str, ";", -1) + 1))
Xit:
SelectedEHSList.Text = Str
End Sub


it will clear the list.

cpounds217
12-04-2017, 01:18 PM
Sorry, I am still quite new to VBA. I have learned a lot but there is a still so much I need to pick up.

My plan is eventually take a course on this but my boss wants me to be somewhat established on my own before sending me to the sharks.

Thank you for your help, consider this closed!

paulked
12-04-2017, 01:34 PM
You're welcome, my pleasure.

Good luck with the learning

:beerchug: