[RESOLVED] From TextBox to Listbox (Excel vb)

[Borgas]

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.

[Fonty]

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.

[DKenny]

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?

[Borgas]

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.

[Borgas]

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 ( 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

[DKenny]

OK, a few follow-on questions/ideas.

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

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.

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

[Borgas]

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

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.

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

[DKenny]

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

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

[Borgas]

wow.. DKenny, that's just perfect

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

[DKenny]

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".

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

[Borgas]

Thank you Declan. You have no idea how much time your help saved me.

The program runs perfectly.

/B

[Borgas]

one more question about the listbox...

Is there any easy way to sort the list in the listbox?

[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.

[Borgas]

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.

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

[DKenny]

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.

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.

SortListControl Me.lstCoords

[Borgas]

Thank you again Declan!

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?

[DKenny]

My bad, sorry.

Change

If .List(lRecNum) > .List(lRecNum + 1) Then

to

If CDbl(.List(lRecNum)) > CDbl(.List(lRecNum + 1)) Then

[Borgas]

What does the "CDbl" stand for? .... You wrote it in the first code aswell and I remember thinking about that

[TheBionicOrange]

"convert to double" data type

[DKenny]

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.

[TheBionicOrange]

Now why can't I be that concise !

[DKenny]

I could get into an Irish Vs Welsh thingy here, but alas this isn't ChitChat

[Borgas]

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?

[DKenny]

Correct.

[Borgas]

Thany You!