How do I make this happen?

[Shaka215]

Hello Fellow Programmers!

ListBox1.RowSource = "Sheet1!IE3:IE200"
Dim sStr As String
Dim dblVal As Double
sStr = "! "
iloc = InStr(1, sStr, "%")
If iloc <> 0 Then
dblVal = CDbl(Left(sStr, iloc - 1))
Else
dblVal = 0
End If

The idea is to have the Listbox pull the information from Sheet1
IE3:E200 and delete every piece of text after the "!". Now the huge gap
is because I didn't know how to accomadate the code to look for what to
delete after the "!". Any help is much appreciated...I'd like this to
work with the Userform_Activate. Thanks!

 

[NickHK]

I don't see the connection between the list box and the values to
edit...Maybe

ListBox1.RowSource = "Sheet1!IE3:IE200"
Dim cell as range
Dim dblVal As Double

for each cell in worksheets("Sheet1").range("IE3:IE200")
iloc = InStr(1, cell.value, "!")
If iloc <> 0 Then
dblVal = CDbl(Left(cell.value, iloc - 1))
Else
dblVal = 0
End If
'Do something with dblVal
next

If you looking to populate the list box with these editted values, look at
the .AddItem method instead of .RowSource. Or use the worksheet to get the
list entries in the correct form first.

NickHK

 

[Shaka215]

Seems the code I got (not from you) but from the boards isn't working
right... The desired result is to do the following...

1. Take the information from Sheet1 IE2:IE200 and list it in Listbox1
2. With the values from IE2:IE200 delete everything that is the RIGHT
of the "!" but only in the list box not in the spreadsheet.

The values in the listbox are going to be changing so it's lot a matter
of using =RIGHT or =LEFT for that matter unless someone could provide
some code to show me how to count the characters in each cell and apply
a formula in the spreadsheet (to produce the desired result) and just
have the Listbox pull those values...I can get the code to work
correctly then but I think in the grandshceme of things I'd be better
off it the listbox was able to do this without the spreadsheet
funciton. I appreciate the time you spent to get back to me NickHK, as
anyone else who can help figure out this needed code. Thanks you!

-Todd

 

[NickHK]

May be this for flexibility:

Private Sub UserForm_Click()
Call FillListBox(ListBox1, Worksheets("Sheet2").Range("O4:O12"), "!")
End Sub

Private Function FillListBox(LBox As MSForms.ListBox, _
SourceRange As Range, _
Optional DelimChar As String = "") _
As Long
Dim cell As Range

For Each cell In SourceRange
LBox.AddItem Split(cell.Text, DelimChar)(0)
Next

FillListBox = LBox.ListCount

End Function

NickHK

 

[Shaka215]

NickHK,

Thanks again man but I'm still having issues...It seems your code is
giving me the error message Run-time error '9': "Subscript out of
range"

the following code is highlighted

LBox.AddItem Split(cell.Text, DelimChar)(0)

What's weird is it works but still causes the error message...Will try
a If Error Goto: 0 and see if that helps but maybe something in the
code's syntax is wrong/missing?

 

[Shaka215]

NickHK,

Thanks man your code works great...Just needed to add that error
handler and it worked fine. I'll remember this and hopefully some day I
can return the favor. Thanks alot man!

-Todd

 

[NickHK]

Yes, you will get that error if you have blank entries in your list.
You can add a check:

If cell.Text<>"" Then LBox.AddItem Split(cell.Text, DelimChar)(0)

NickHK