Trying to Create a Remove Last Button

[cpounds217]

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]

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]

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]

The Message Box Code is exactly what you provided:

MsgBox Str1 & vbCr & Str2

[paulked]

have you tried:

Str = SelectedEHSList.Text

as in your post #22?

Also what does

MsgBox Str & vbCr & Str1

& vbCr & Str2

give you?

MsgBox.png

I get this.

[paulked]

That should be

MsgBox Str & vbCr & Str1 & vbCr & Str2

Won't let me edit it out!

[cpounds217]

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]

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.
Capture.JPG


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]

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]

What you see below:

Capture.JPG

[paulked]

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]

It takes the last entry off on every click until there is nothing left (you may want to trap that error!)

[cpounds217]

Thank you very much. We can mark this as SOLVED!

[cpounds217]

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]

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]

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]

You're welcome, my pleasure.

Good luck with the learning