List box question 1

[willyboy58]

I have six command buttons on a worksheet. Three to print and three to preview. Right now they all work as desired in printing and/or previewing their respective worksheets. I am wanting to add some code to each button to open a List Box and display the files in a directory in which to print or preview.

Example: I have FileA, FileB and FileC and the user selects the Preview Invoices button. The List Box appears with the three files listed. The user then selects the desired file and the code executes (fires?) to preview the selected worksheets in the selected file.

The following code is from VBA help:

Dim EntryCount As Single

Private Sub CommandButton1_Click()
EntryCount = EntryCount + 1
ListBox1.AddItem (EntryCount & " - Selection&quot
End Sub

The following is my attempt:

Public ReportMonth As String

Sub AddToListBox()
ReportMonth = "Prop1" & ReportMonth & ".xls"
ListBox1.AddItem (ReportMonth)
'below is the Path for saving the new file
Workbooks("Propxfer&quot.Close savechanges:=False
End Sub

I receive the message “Object Required”. Isn’t the ListBox1 the object? What is the difference between my code and Help’s? There is definitely a list box on my worksheet. How can I check the name to make sure that it is ListBox1?

Would this be easier using a Dialog box?

TIA

Bill

 

[vladk]

willyboy58,

Did you put the list box on your worksheet?
Did you name it ListBox1?
Is sheet with ListBox1 active during the execution of your code?

;0)

 

[SQLBI]

Hi,

Have you tried adding

Sheet1.ListBox1

or whatever the sheet name maybe to your code.

If your LisBox is a control on a form you will need to use

Form1.ListBox1

or whatever name the form has.

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[willyboy58]

Hey guys,

I added the worksheet name (new code below) with no change in the situation. When the Propxfer workbook closes, the Prop1Compute workbook is active and contains the sheet "Menu" that displays the buttons and the list box.

There is definitely a list box on the "Menu" sheet, but to be honest, I'm not sure what it's name is. I added and deleted a few other boxes and controls while trying to get this to work. How do I check the name of the current list box?

Sub AddToListBox()
ReportMonth = "Prop1" & ReportMonth & ".xls"
Sheet("Menu&quot.ListBox1.AddItem (ReportMonth)
'Sheet("Menu&quot is NOT in the "Propxfer" workbook below _
it is in the Prop1Compute workbook that is active _
after "Propxfer" is closed
'below is the Path for saving the new file
Workbooks("Propxfer&quot.Close savechanges:=False
End Sub

vladk,

You asked "Is sheet with ListBox1 active during the execution of your code?"

My answer is Yes. On the "Menu" worksheet are the buttons and the list box. I am wanting to have the list box appear (open) when any one of the buttons is clicked (for preview or printing) and the user then select the file (month) to be previewed or printed. Once the user selects the file, the preview or print happens.

But just to get it to work, I thought that I would put the list box on the sheet, which may be the only way I can get it to work. The user may have to select the month first, then click the appropriate button.

TIA

Bill

 

[RobBroekhuis]

easiest way to check the names of the controls on your worksheet is to type in the immediate window:

for each ob in activesheet.oleobjects:?ob.name:next ob



Rob

 

[willyboy58]

Rob,

I typed your line of code:

for each ob in activesheet.oleobjects:?ob.name:next ob

into the Immediate window and pressed Enter as Help says to do. Nothing happened except the cursor advanced as it should down the page. The sheet “Menu” was active.

I then tried the following from Help:

Private Sub Button27_Click() ‘changed button # to run _ procedure
Dim MyControl As Control
With ActiveSheet
For Each MyControl In Controls
MsgBox "MyControl.Name = " & MyControl.Name
Next
End With
End Sub

I get the message “Object Required” on the line “ For Each MyControl In Controls”.

Isn’t the active sheet the object?

TIA

Bill

 

[vladk]

willyboy58,

The code in your previous example will not work. You use "With ActiveSheet" and don't reference any ActiveSheet objects. Secondary, you can refer to Controls collections when you run through the form's objects. In your case, you run through the worksheet objects. Please try the following, assuming you put Button27 CommandButton on your sheet.

Private Sub Button27_Click()
Dim MyControl As OLEObject

For Each MyControl In OLEObjects
MsgBox "MyControl.Name = " & MyControl.Name
Next

End Sub

Vladk

 

[RobBroekhuis]

The fact that you got no response from my code suggests you don't have OLEObjects on your worksheet - you can check by typing
?activesheet.oleobjects.count

What type of listbox do you have on your worksheet then?


Rob

 

[willyboy58]

Rob,

You hit the nail on the head. The buttons and list box that I had put on the worksheet were from the Forms toolbar. I was using the Forms toolbar so I could easily assign a macro to each button.

After reading your post and running the code
"?activesheet.oleobjects.count" The count was 0. I then added a list box from the Control toolbox and the count was then 1.

I just ran my whole procedure. The month name was not saved to the list box but I'm thinking that it's b/c I didn't save the new file (named: Prop1October2003.xls) to its own directory and then reference the file from that directory into the list box. Does that make sense? Or should the Listbox display the file name without regard to the directory?

Here is what I have for the Listbox code:

Sub AddToListBox()
ReportMonth = "Prop1" & ReportMonth & ".xls"
Sheets("Menu&quot.ListBox1.AddItem (ReportMonth)

'below should be the Path for saving the new file
ActiveWorkbook.SaveCopyAs Filename:="Prop1" & ReportMonth & ".xls"

'close raw data workbook
Workbooks("Propxfer&quot.Close savechanges:=False
End Sub

Vladk,

Thanks for the post. As you can see from above, the problem was with the listbox coming from the Forms toolbox and not the Control toolbox.

Any other suggestions or help re: this issue will be greatly appreciated.

Thanks

Bill

 

[willyboy58]

Rob and Vladk,

Some info from a previous post of mine:

“I am wanting to add some code to each button to open a List Box and display the files in a directory in which to print or preview. Example: I have FileA, FileB and FileC and the user selects the Preview Invoices button. The List Box appears with the three files listed. The user then selects the desired file and the code executes (fires?) to preview the selected worksheets in the selected file.” Right now the listbox is sitting on the worksheet with no months in it.

I will have multiple months that I want to display in the list box when the workbook with my macros is opened (“Prop1Compute”). The user can process the current month if desired or look up info on past months using the listbox. If the user processes the current month, it should be saved to the list box.

Am I headed in the right direction?

Thanks

 

[RobBroekhuis]

I'm confused about different "months" and different "files" - is each month one file, or each month one folder which contains multiple files? If the latter, then how does the user select the month?
I think you're on the right track. You'll need to program the listbox1_change event to fire your preview code, and probably some workbook_open event to initially populate the listbox. Make sense?


Rob

 

[willyboy58]

Rob,

Each month is its own file. All in one folder. Folder name: "Prop1Months". File names: Prop1January2003, Prop1February2003, etc.

"You'll need to program the listbox1_change event to fire your preview code, and probably some workbook_open event to initially populate the listbox". I'll need to do some reading on this.

The original idea was to have the user make a selection (click a button), such as Preview Invoices, and have the month selection ListBox appear. The user would then click the desired month and the procedure would then run. Right now the List box is always visible on the Menu sheet with the buttons.

I've been wondering if I would be better off to just have Excel's Dialog box for choosing files (I can't think of what it's called) to open up to the proper directory.

Thoughts?

TIA

Bill

 

[RobBroekhuis]

What you're trying to do is definitely possible, and not all that complicated. I would consider using a userform instead of the on-sheet listbox, but if you want to go with your original approach, clicking the button can make the .visible property of the listbox go to TRUE (after loading the proper filenames into the listbox), and then in the listbox1_change event you have the code for previewing.


Rob

 

[willyboy58]

Rob,

If I understand:

1)Create a user form
2)Put all my command buttons (process, print, preview) and the HIDDEN list box on the form
3)Code my process button to run my processing code
4)Code my print and preview code to make the list box visible
5)Code the list box with a click event to run the print or preview code when the desired month is selected

Some code as such:

Private Sub ProcessButton_Click()
‘processes raw data
End Sub

Private Sub ListBox_DoubleClick()
ListBox1.Visible
Command1_Click ‘lets user double click the month name to Print or preview
End Sub

Private Sub PreviewInvoices_Click()
ListBox_DoubleClick ‘to open the list box
‘ place Preview invoice code here
End Sub

Private Sub PrintInvoices_Click()
ListBox_DoubleClick ‘to open the list box
‘place Print invoice code here
End Sub

It seems that all the code that I have found for adding items to a list box are as follows:

Private Sub AddToBox()
List1.AddItem “January”
List1.AddItem “February”
List1.AddItem “March”
End Sub

But how do I get the month’s into the list box without having to open the code module and type them in as above? I’ll want the month’s to be saved in their own folder “Prop1Months”. Code such as:

Sub AddToBox()
ListBox1.AddItem “Prop1” & ReportMonth & “.xls”
End Sub

Will the month’s be available for the next session after I close Excel?

Thanks for the help. All of this is confusing, but I’m slowly getting there.

 

[willyboy58]

Rob,

On the MS site, I found the following code. I'm trying to adapt to my needs. Thanks for all your help.

ListBox with File search Knowledge # 155837

In Microsoft Excel 97 for Windows, you can use the FileSearch object to locate files on a local or network drive. This article provides an example of how to use the FileSearch object in Microsoft Visual Basic for Applications to fill a list box with file names.
1. On the Tools menu, click Macro, and then click Visual Basic Editor.
2. On the Insert menu, click UserForm to create a new user form.
3. Select the ListBox control in the Toolbox to add a list box to the user form.
4. On the Insert menu, click Module to insert a new module sheet.
5. In the new module sheet, type the following macro code:
6. Sub FileSearchListBox()
7.
8. ' Dimension variables.
9. Dim myarray()
10. Dim fs As Object
11. Dim i As Integer
12.
13. ' Declare filesearch object.
14. Set fs = Application.FileSearch
15.
16. ' Set folder to search.
17. fs.LookIn = "c:\xldocs"
18.
19. ' Set file name to search for.
20. fs.FileName = "*.xls"
21.
22. ' Execute the file search, and check to see if the file(s) are
23. ' present.
24. If fs.execute > 0 Then
25.
26. ' Redimension the array to the number of files found.
27. ReDim myarray(fs.FoundFiles.Count)
28.
29. ' Loop through all found file names and fill the array.
30. For i = 1 To fs.FoundFiles.Count
31. myarray(i) = fs.FoundFiles(i)
32. Next i
33. Else
34. ' Display message if no files were found.
35. MsgBox "No files were found"
36. End If
37.
38. ' Loop through the array and fill the list box on the UserForm.
39. For i = 1 To fs.FoundFiles.Count
40. UserForm1.ListBox1.AddItem myarray(i)
41. Next i
42.
43. ' Display the UserForm.
44. UserForm1.Show
45.
46. End Sub

 

[RobBroekhuis]

That's a good start. Let us know how you fare.
My suggestion actually was to keep the button on the worksheet, and use its click event to show the userform (no hiding/unhiding listboxes). But that may not be what you had in mind.


Rob