[RESOLVED] Adding and removing data from a list box using a command button in VB 6

[Paddyjmcc]

Hi all,

I have imported data from an excel file into a list box in a program I'm working on. I have coded a number of command button so that if a certain button is clicked, certain information is displayed in the list box.
So I click a command button and information is displayed in the list box. But I want to be be able to click on another command button and display totally different information from the same Excel file which will replace the original information.
In other words, when I click say, ''button 1'', ''Text 1'' appears in the list box. But I want to be then able to click say, ''button 2'' and by clicking button 2, ''Text 1'' disappears from the listbox and is replaced by ''Text 2''.

As a side note, I have 6 command buttons in total, all of which display a different chunk of information from the same Excel file when displayed.

I would appreciate any help on this matter

Thanks

[Hack]

Moved From THe CodeBank (which is for sharing code rather than posting questions )

[DataMiser]

To clear a list box you use the clear method of the listbox to remove and item you use the remove method.

[SamOscarBrown]

As DM says. Sounds like you simply need to use 'list1.clear' as the first line in each of your command buttons. You coild also simplify your code if you used an array od command buttons, instead of six 'individual' ones.

ps-was your previous thread about using Excel resolved?

[Paddyjmcc]

Thanks for the replies guys!

Yes I just tried adding 'list1.clear' as the first line in one of the command buttons and it didnt do anything. I also tried putting it at the bottom at the end of the code and that caused the information to appear but then disappear after 1 second.

Also SamOscarBrown yes my Excel issue was resolved thanks!

[DataMiser]

List1.Clear will remove all entries from the list. That is what clear means. Naturally if there is nothing in the list it will not appear to do anything and naturally if you use it when you have added items they will be removed.

You need to think about what you are doing.

[Paddyjmcc]

Yes but Im wondering if 'List1.Clear' is the code that should be used because at the moment it isn't doing anything. I need info to be displayed in the list box on the clicking of a button and then on a clicking of another button in the form the previous info is replaced with the new info. List1.Clear isn't doing this.

[SamOscarBrown]

No, list1.clear REMOVES everything from your list1 on the form.
try this for ex:
private sub command1_click()
list1.clear 'clears anything in the listbox before adding something else - if you want to KEEP 'ADDING' MORE to the box, don't use this
List1.additem ("Hello")
list1.additem ("HI")
end sub

private sub command2_click()
list1.clear
list1.additem("Good by")
list1.additem("Adios")
end sub

[Paddyjmcc]

Yep great that code works perfectly thanks.

So just for the purpose of my project, instead of the text 'hello', 'HI', 'Good By' etc. I need a column of an excel file to be displayed in the list box. Do I just put the file location and column into the code instead of where those words are in your example??

[SamOscarBrown]

Yes...kinda. You will have to look up how to 'load' an Excel file (if you have not already done so)...here's some pointers:
First, put a REFERENCE to Excel by selecting References from the Project menu, and select Microsoft Excel 12.0 Object Library
.
In your declarations section of your form (or in a global Module, your choice), put in something like this:

Dim oExcel As Excel.Application
Dim obook As Excel.workbook
Dim oSheet As Excel.Worksheet

'Then, in your cmdbutton to execute the function, do something like this:

private sub command1_click()
list1.clear
Set wkbObj = GetObject(myExcelFileName) '''Where 'myExcelFileName' contains the path to your excel file and the name itself (e.g, app.path & "\myExcel.xlsx"
'''followed by something like this:
Dim i As Integer, numrows as integer
numrows = wkbObj.Worksheets(1).Range("A1").CurrentRegion.Rows.Count 
For i = 1 To numrows 
    list1.additem(wkbObj.Worksheets(1).Range("A" & i + 1).Value) 'where "A" is the column you want loaded
'''Note the 'i + 1'---use the '+ 1" IF your Excel first row contains headers you don't want in the listbox, otherwise, just use 'i'
next i
end sub

'the above is untested, but SHOULD produce what you want

[Paddyjmcc]

Hmm no im afraid that didnt work either. When I tried your code with an excel file that Im using for my project. 'Run Time Error 432' popped up saying the 'file name was not found during automation operation'.
Here's the code I put in:

Dim oExcel As Excel.Application
Dim obook As Excel.Workbook
Dim oSheet As Excel.Worksheet


Private Sub Command1_Click()
List1.Clear
Set wkbObj = GetObject(App.Path & "F:\Machine info for HQ and FQ Shrink Tunnel.xlsx") '''Where 'myExcelFileName' contains the path to your excel file and the name itself (e.g, app.path & "\myExcel.xlsx"
'''followed by something like this:
Dim i As Integer, numrows As Integer
numrows = wkbObj.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
For i = 1 To numrows
List1.AddItem (wkbObj.Worksheets(1).Range("A" & i + 1).Value) 'where "A" is the column you want loaded
'''Note the 'i + 1'---use the '+ 1" IF your Excel first row contains headers you don't want in the listbox, otherwise, just use 'i'
Next i
End Sub


Note: "F:/Machine info for HQ and FQ Shrink Tunnel.xlsx" is the location and name of the excel file Im using

[SamOscarBrown]

take out "App.Path & "
You have your hardcoded location ("F:\etc") so don't need the app.path. Many folks use app.path because they locate their files in the same directory as their project, OR, they use a commonDialog to 'find' and load the file......

[Paddyjmcc]

No sorry its very strange its still not working. I took out app.path and tried running it but when I tried clicking the coded command button nothing happened at all.

[SamOscarBrown]

Attach your program. Make sure no personal information is included (include the excel example file as well). When you say 'nothing happened at all', you got an issue.

[SamOscarBrown]

Sorry...I DID have one error in that example I sent....instead of "DIM obook as Excel.Workbook", insert "DIM wkbObj as Excel.Workbook"

I just created a short program and an excel file and it works just fine (now that I dimensioned the right variable!!!!!)

[Paddyjmcc]

Ok no that change didnt make any difference. The short program Im running just to test this code is a simple form with a list box and 2 command buttons.
Here's the exact code I'm using to try run it. Clicking command 1 should display the excel information and then on clicking command 2 the excel info should be replaced by 'Good By' 'Adios'. Is this correct?

Dim oExcel As Excel.Application
Dim wkbObj As Excel.Workbook
Dim oSheet As Excel.Worksheet

Private Sub Command1_Click()
List1.Clear
Set wkbObj = GetObject(App.Path & "F:\Machine info for HQ and FQ Shrink Tunnel.xlsx") '''Where 'myExcelFileName' contains the path to your excel file and the name itself (e.g, app.path & "\myExcel.xlsx"
'''followed by something like this:
Dim i As Integer, numrows As Integer
numrows = wkbObj.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
For i = 1 To numrows
List1.AddItem (wkbObj.Worksheets(1).Range("A" & i + 1).Value) 'where "A" is the column you want loaded
'''Note the 'i + 1'---use the '+ 1" IF your Excel first row contains headers you don't want in the listbox, otherwise, just use 'i'
Next i
End Sub

Private Sub Command2_Click()
List1.Clear
List1.AddItem ("Good by")
List1.AddItem ("Adios")
End Sub

[SamOscarBrown]

No...headed to work, will fix it for you in about an hour.

[Doogle]

In case Sam's busy at work....

Set wkbObj = GetObject(App.Path & "F:\Machine info for HQ and FQ Shrink Tunnel.xlsx")

Should be

Set wkbObj = GetObject("F:\Machine info for HQ and FQ Shrink Tunnel.xlsx")

on the assumption that the xlsx file is on the root of the "F" Drive and its FileName is: "Machine info for HQ and FQ Shrink Tunnel"

[SamOscarBrown]

:-) Thanks, Doog! precisely (I had already informed Paddy of this posts ago, but he reverted to his orig).

[Paddyjmcc]

Ahh very sorry guys my mistake I did actually try the code without the 'app.path', I had it written as u wrote it in your last post doogle:

Set wkbObj = GetObject("F:\Machine info for HQ and FQ Shrink Tunnel.xlsx")

Unfortunately this still didnt work.

[SamOscarBrown]

Can you please say WHAT didn't work? What happened when you clicked on that command button? PRECISELY, please. "Didn't work" doesn't tell us anything.

[Paddyjmcc]

Well literally nothing. It was as if there was no code attached to the command button at all. It could be clicked but no information appeared in the list box or anything. Its like when a command button is added to a form but no code is written for it so when it is clicked nothing happened....

BUT..when the other button was clicked, command 2, it worked fine. Command 2 is the button with the following code attached:

Private Sub Command2_Click()
List1.Clear
List1.AddItem ("Good by")
List1.AddItem ("Adios")
End Sub

[SamOscarBrown]

Paddy...replace your code with this below
Run it.
Click on your command1 button
What number appears in the msgbox when it runs?

Option Explicit

Dim oExcel As Excel.Application
Dim wkobj As Excel.Worksheet
Dim wkbobj As Excel.Workbook

Private Sub Command1_Click()
List1.Clear
Set wkbobj = GetObject("F:\Machine info for HQ and FQ Shrink Tunnel.xlsx") 
Dim i As Integer, numrows As Integer
numrows = wkbobj.Worksheets(1).Range("A1").CurrentRegion.Rows.Count

MsgBox numrows

For i = 1 To numrows
    List1.AddItem (wkbobj.Worksheets(1).Range("A" & i).Value) 'where "A" is the column you want loaded
Next i
End Sub

Private Sub Command2_Click()
List1.Clear
List1.AddItem ("Good by")
List1.AddItem ("Adios")
End Sub

[Paddyjmcc]

Interesting..So i replaced my code with the code from your last post and ran it. On clicking of command , the number 1 appears in the message box that pops up. Then when I click ok, the column and row A1 from my excel file appears in the List Box, then when I click command 2, the info. from A1 in my excel file is replaced by ''Good By'' ''Adios''.





Just as a side note, in my excel file, I need the entire column A to appear in the list box not just column and row A1.

[Paddyjmcc]

Oh also just after I wrote that last reply I changed the code on command 2. I cut what was originally coded for command 2 (Good By,Adios) and copied command 1's code for command 2 and this line:
numrows = wkbobj.Worksheets(1).Range("A1").CurrentRegion.Rows.Count

I changed to:
numrows = wkbobj.Worksheets(1).Range("A3").CurrentRegion.Rows.Count

The number 3 popped up in a msgBox and when I clicked ok, the info from column and row A3 from my Excel file appeared in my List Box.

[SamOscarBrown]

All right....will look once I get to work.....numrows SHOULD be the total number of rows......let me check and will get back with you. Or, IOWs, you should see that number of rows in that messagebox.

[SamOscarBrown]

Here...use this rowcounter instead of numrows = wkbobj.Worksheets(1).Range("A1").CurrentRegion.Rows.Count:

numrows = wkbObj.Worksheets(1).UsedRange.Rows.Count

does it return the total number of rows in your spreadsheet? It should.
Now, what column are you working with---what do you want to see in that listbox? If column A, then leave,

List1.AddItem (wkbobj.Worksheets(1).Range("A" & i).Value) 'where "A" is the column you want loaded

as it is. If another column, change the "A" to that column's Identifier (B, C, D,etc).
If this DOES not give you what you want, attach the excel spreadsheet (if it contains personal data, change it).

[Paddyjmcc]

Fantastic it works perfectly now. I changed the 'numrows' line at you stated in your last post and that did the trick. It now returns the total number of rows in the spreadsheet and whenever I change A to any other column identifier, that column is the one that appears in the list box so ye Its working very well, I put that code into my main project that Im working on and its proving very effective. Thanks very much for all your help, I know I was being a bit of a pain but i really apprecite the help!

Thanks
Paddy

[SamOscarBrown]

No pain, Paddy.....glad to assist. Please mark this thread as Resolved (Thread Tools on the menu bar).