-
[RESOLVED] From TextBox to Listbox (Excel vb)
Hello!
Ive tried to search about this topic but didnt find any thread that would match my problem specificly...
Here it goes. First of all I have to say that Im quite new at VB so please keep it basic :)
I want the user to add data (x- and y- koordinates in this case) by typing it in two seperate textboxes (one for the x-value and one for the y-value). Then, by pushing a button, the values should be added from the textboxes to a single (-preferably) listbox.
The values in the listbox should be editable when clicked (a new edit window pops up with textboxes for the two values from that row) so I have to find a way keep the x- and y- values apart. I dont know if there is a way to work with rows and columns in the listbox?
I hope I wrote it somewhat understandable, as I wrote - I'm qouite new at this so probably I dont use the right terms.
-
Re: From TextBox to Listbox (Excel vb)
I recently posted more or less the same problem, at the moment I'm able to do what you want. Tou can check the code and the file at the end of http://vbforums.com/showthread.php?t=413171. My problem is still that for succesive items added I cannot keep a 'column' format, just an individual 'row' format. Check the file.
-
Re: From TextBox to Listbox (Excel vb)
Borgas, welcome to the Forums!
There are a few questions I would ask about your requirements.
1/ Are these controls, the textboxes and the listbox, on a worksheet or on a userform?
2/ If they are on a worksheet are they ActiveX controls (from the Controls toolbar) or are they Excel controls (from the Forms toolbar)?
3/ Will your listbox allow multiple instances of a co-ordinate combonation? I.e. can I add (x=1,y=1) twice?
4/ Would you want to use the same 2 textboxes for editing existing entries that you use for addition of new entries?
-
Re: From TextBox to Listbox (Excel vb)
I found out with a "little" help how to list posts in diferent columns in the listbox now...
Private Sub button_Click()
ListBox1.ColumnCount = 2
Dim x As Single
Dim y As Single
x = TextBox1
y = TextBox2
Me.ListBox1.AddItem (x)
Me.ListBox1.Column(1, Me.ListBox1.ListCount - 1) = y
TextBox1 = ""
TextBox2 = ""
End Sub
this is all good as long as i write numbers (like i want it to) in the textboxes but if I for instance forget to write something in one box, or if i write text in it, then I get a error message...
What code should I write so that the program can recognize the non numerical values (included empty text boxes) and if so empty the textbox, exit the sub and open a mesage box with a warning.
-
Re: From TextBox to Listbox (Excel vb)
Thank You DKenny, thats nice of you. :)
1/ Are these controls, the textboxes and the listbox, on a worksheet or on a userform?
They are on a userform ( :bigyello: I think ???)
3/ Will your listbox allow multiple instances of a co-ordinate combonation? I.e. can I add (x=1,y=1) twice?
I haven't thought about that yet... but i guess no (if i want this to be that advanced). - in my case the x-values should only appear once
4/ Would you want to use the same 2 textboxes for editing existing entries that you use for addition of new entries?[/QUOTE]
I haven't decided yet... Fist I thought that a special edit form should appear with separate textboxes, but now when I think about it maybe it would be easiest to edit in the existing boxes.... What do you think?
B
-
Re: From TextBox to Listbox (Excel vb)
OK, a few follow-on questions/ideas.
Quote:
I haven't thought about that yet... but i guess no (if i want this to be that advanced). - in my case the x-values should only appear once
So you saying that each value of X should apprear only once and that you can have multiple instances of any y value?
I.e.
This list is acceptable.
X Y
1 2
2 3
3 3
But this list is not.
X Y
1 2
2 3
2 2
Quote:
I haven't decided yet... Fist I thought that a special edit form should appear with separate textboxes, but now when I think about it maybe it would be easiest to edit in the existing boxes.... What do you think?
I always like to use the same controls for adding and editing. It usually makes the form look a lot neater, so lets go with one set of textboxes.
Quote:
this is all good as long as i write numbers (like i want it to) in the textboxes but if I for instance forget to write something in one box, or if i write text in it, then I get a error message...
OK, so before we add the new values to the listbox, we will need to check that
A/ Neither of the textboxes are blank
and
B/ Both textboexs contain numeric values.
and
C/ Does the x value already exits in the listbox
These are all easy to check.
Is there any other validation required? E.g. are there any lower or upper limits on the x and y values, do they need to be whole number, etc...
-
Re: From TextBox to Listbox (Excel vb)
Quote:
Originally Posted by DKenny
So you saying that each value of X should apprear only once and that you can have multiple instances of any y value?
I.e.
This list is acceptable.
X Y
1 2
2 3
3 3
But this list is not.
X Y
1 2
2 3
2 2
That is correct :)
Quote:
Originally Posted by DKenny
I always like to use the same controls for adding and editing. It usually makes the form look a lot neater, so lets go with one set of textboxes.
You have a point there. If it would be possible to do without confusing the user then I guess its best to keep it neat.
Quote:
Originally Posted by DKenny
OK, so before we add the new values to the listbox, we will need to check that
A/ Neither of the textboxes are blank
and
B/ Both textboexs contain numeric values.
and
C/ Does the x value already exits in the listbox
These are all easy to check.
Is there any other validation required? E.g. are there any lower or upper limits on the x and y values, do they need to be whole number, etc...
Thats all I can think of right now. Regarding the numbers - the program should allow decimal numbes so I guess the "Single" is correct(?)
The cordinates in the list will later be used to draw a diagram with two lines (if possible) one acording to the listed coordinates and one that is a "offset" (i dont know if thats the correct expression) line from the fist one. Basically what Im after is not so much the diagram but the equation for the two lines.... But thats a upcoming problem. I guess I have to spend some sleapless nights to figure that one out. For now I have enough trubble adding the values to the list :)
-
Re: From TextBox to Listbox (Excel vb)
I never use the Single datatype, I use Double instead.
OK, Here's some code that gets fired when the user click on the "Add" button. The code performs all the validations that we have identified.
I have assumed the your controls have the following names, you will need to either replace the names I have use in my code with the names of your contols, or rename your controls to the names I have used here.
My Names
txtXCoord - X textbox enter
txtYCoord - Y textbox enter
lstCoords - Listbox with Existing coordinates
cbtnAdd - Add coordinates button
There is a lot in this code, so spend some time going through it and testing it to make sure it work as you expect and that you understand exactly what it is doing at each step.
Once your happy with this section, we'll move onto the Edit record code....
VB Code:
Private Sub cbtnAdd_Click()
Dim dXValue As Double
Dim dYValue As Double
Dim lItemNum As Long
' ----------------------------------------------------
' Phase 1 - Check for Valid Data
' ----------------------------------------------------
'Check if X Co-ord is populated
If Me.txtXCoord.Value = "" Then
MsgBox "Please supply a value for X."
Me.txtXCoord.SetFocus
Exit Sub
End If
'Check if the X Co-ord is a valid number
If Not IsNumeric(Me.txtXCoord.Value) Then
MsgBox "X Coordinate must be numeric."
Me.txtXCoord.SetFocus
Exit Sub
End If
'Check if Y Co-ord is populated
If Me.txtYCoord.Value = "" Then
MsgBox "Please supply a value for Y."
Me.txtYCoord.SetFocus
Exit Sub
End If
'Check if the Y Co-ord is a valid number
If Not IsNumeric(Me.txtYCoord.Value) Then
MsgBox "Y Coordinate must be numeric."
Me.txtYCoord.SetFocus
Exit Sub
End If
'Now that we know that we have numeric values
'we can pass them into our variables
dXValue = txtXCoord.Value
dYValue = txtYCoord.Value
' ----------------------------------------------------
' Phase 2 - Check if the x value is already used
' ----------------------------------------------------
'Loop through the existing values, trying
'to find a match
With Me.lstCoords
For lItemNum = 0 To .ListCount - 1
If dXValue = CDbl(.List(lItemNum, 0)) Then
MsgBox "X Coordinate is in use."
Me.txtXCoord.SetFocus
Exit Sub
End If
Next lItemNum
End With
' ----------------------------------------------------
' Phase 3 - Add the new values and clear the textboxes
' ----------------------------------------------------
'Add the values to the listbox
With Me.lstCoords
.AddItem (dXValue)
.Column(1, .ListCount - 1) = dYValue
End With
'Clear the Y textbox
Me.txtYCoord.Value = ""
'Clear the X textbox and move the
'cursor to it
With Me.txtXCoord
.Value = ""
.SetFocus
End With
End Sub
-
Re: From TextBox to Listbox (Excel vb)
wow.. DKenny, that's just perfect :thumb: :thumb: :thumb:
You made it so siple and easy to folow that even I could understand (I could even see that you forgot to set the ColumnCount=2 and that is a perfect sign how pedagogically its written)
Thank You so much!!! (and how much do I own you? ;) )
If you could share some advice regarding the editing part I would be glad to read it...
Thank you again :)
-
Re: From TextBox to Listbox (Excel vb)
Glad to be able to help.
I didn't set the ColumnCount propert within the code, as I was assuming that it had been set to two with the form design, and would remain so.
Here's some code that will take a seleced line and pass it back to the textboxes. I called the Edit button "cbtnEdit".
VB Code:
Private Sub cbtnEdit_Click()
With Me.lstCoords
'Has the user selected a row in the LisBox?
If .ListIndex = -1 Then
MsgBox "Please Select a Row to Edit."
.SetFocus
Exit Sub
End If
'Pass the values back to the textboxes
Me.txtXCoord.Value = .List(.ListIndex, 0)
Me.txtYCoord.Value = .List(.ListIndex, 1)
'Clear the row from the listbox
.RemoveItem .ListIndex
End With
'Move the cursor to the X textbox
Me.txtXCoord.SetFocus
End Sub
-
Re: From TextBox to Listbox (Excel vb)
Thank you Declan. You have no idea how much time your help saved me.
The program runs perfectly. :thumb:
/B
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
one more question about the listbox...
Is there any easy way to sort the list in the listbox?
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
I had a feeling that this one was coming! ;)
Do you want the sort to be ascending only and only on the X value?
Or do you want a more dynamic, user driven, sort - i.e. User selects column and sort direction?
If its the first, I would assume you would want it to auto-sort whenever the user adds a value.
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
Quote:
Originally Posted by DKenny
I had a feeling that this one was coming! ;)
Do you want the sort to be ascending only and only on the X value?
Or do you want a more dynamic, user driven, sort - i.e. User selects column and sort direction?
If its the first, I would assume you would want it to auto-sort whenever the user adds a value.
:bigyello:
Ive been looking at this all day, didnt think this was such a problem becouse I thought that VB had some "sort" or "arrange" command.
Then I tried to write a code to step up the row numbers and... well it became too complecated
Anyway.
Yes I want it to be ascending (auto-sorting) only and only according to the x-value.
/ B
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
OK, that's what I figured you would need. Here's a generic sort procedure that you can use with any listbox or combobox. (VBA doesn't include any method to sort these controls). You can use this proc in any form.
VB Code:
Sub SortListControl(ByRef MyControl As Control)
Dim bSorted As Boolean
Dim lRecNum As Long
Dim dXTemp As Double
Dim dYTemp As Double
'Chck that the passed control is a listbox or combobox
If TypeName(MyControl) <> "ListBox" _
And TypeName(MyControl) <> "ComboBox" Then Exit Sub
'No need to sort if we only have one record
If MyControl.ListCount = 1 Then Exit Sub
'Loop until the List is sorted
Do While Not bSorted
'Assume the List is in the correct order
bSorted = True
With MyControl
'Loop through the List (first item t second last)
For lRecNum = 0 To .ListCount - 2
'Are the current item and the next item in the wrong order
If .List(lRecNum) > .List(lRecNum + 1) Then
'If so, then swap the rows
dXTemp = .Column(0, lRecNum)
dYTemp = .Column(1, lRecNum)
.Column(0, lRecNum) = .Column(0, lRecNum + 1)
.Column(1, lRecNum) = .Column(1, lRecNum + 1)
.Column(0, lRecNum + 1) = dXTemp
.Column(1, lRecNum + 1) = dYTemp
'Record that we are not sorted
bSorted = False
End If
Next lRecNum
End With
Loop
End Sub
To call this procedure you will need to include the following line to the "Add" procedure, right after you add the new values and before you set the focus back to the textboxes.
VB Code:
SortListControl Me.lstCoords
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
Thank you again Declan! :thumb:
Ive tried it now and it works great... but theres a little problem if i for instance would like to sort this list:
123
12
24
78
1458
11
Then the program sorts it as
11
12
123
1458
24
78
Is there a way to avoid that?
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
My bad, sorry.
Change
VB Code:
If .List(lRecNum) > .List(lRecNum + 1) Then
to
VB Code:
If CDbl(.List(lRecNum)) > CDbl(.List(lRecNum + 1)) Then
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
What does the "CDbl" stand for? .... You wrote it in the first code aswell and I remember thinking about that
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
"convert to double" data type
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
It is a type conversion Function. It converts the passed value to a Double dataype.
The problem here is that data in listboxes inheriently have a String datatype. When we do a comparison on Strings VBA will always compare in alphabetical order, including numbers - which is why 123 came before 24.
By converting the values to Doubles, the comparison is now performed using a numeric comparison, in which case 24 comes before 123.
Hint: If you're ever unsure what a particular VBA 'word' means, just highlight the word and press F1. The help files have a wealth of info.
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
Now why can't I be that concise !
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
I could get into an Irish Vs Welsh thingy here, but alas this isn't ChitChat ;)
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
Ok so the data in the listbox is automatically converted to string, even though we originally set the values in the textboxes to Double before we moved them down ti the listbox?
So now, if I want to use the data in the listbox in a next step, I have to convert it to Double every time?
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)
-
Re: [RESOLVED] From TextBox to Listbox (Excel vb)