print from list box - I'm new to vb, be gentle! 1

[misscrf]

I am so excited! I have automated charts in excel, so that the user can open to a menu worksheet, refresh all of the chart's data (with parameter) and preview and print them.

I am learning vb as I go, and I don't think I am do TOO bad. Anyway, I wanted to make a user form in the vb code to have a list box. I would make a 4th button on this menu sheet, so that the user can choose to print just 1 or more chart, rather than all of them.

I created the list box, and populated it list. This is a multiselect with checkboxes.

Private Sub UserForm_Initialize()
'The 1st list box contains 3 data columns
ListBox1.ColumnCount = 7

'Load integer values into first column of MyArray


'Load columns 2 and three of MyArray
MyArray(0, 0) = "Domestic Long Distance"
MyArray(1, 0) = "Toll Free"
MyArray(2, 0) = "Directory Assistance"
MyArray(3, 0) = "Local"
MyArray(4, 0) = "International"
MyArray(5, 0) = "Calling Cards"
MyArray(6, 0) = "Usage Type"


'Load data into ListBox1
ListBox1.List() = MyArray

End Sub


Now I will have a command button on the user form to print the selected charts. My question is, how do I do that?

I have looked for the code, but I am not finding a direct answer. I figure I need to declare each row value = to the chart that I have, and then for each value selected print that chart. Right?

Any help on this code would be great!
Thanks all.

This is fun. I want to be a vb expert! hehehehe

misscrf

Management is doing things right, leadership is doing the right things

 

[Loomah]

Hi misscrf
Your post made me smile!

I've assumed that the charts are on separate chart sheets rather than being objects on a worksheet.

Add your commandbutton to your form and add this code to the click event for the button. I've given it in full here.

This is all quite new to me too but it should work!

Private Sub CommandButton1_Click()
Dim i As Integer
Dim cnt As Integer
Dim myArray()

For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
        ReDim Preserve myArray(cnt)
        myArray(cnt) = Me.ListBox1.List(i)
        cnt = cnt + 1
    End If
Next
Sheets(myArray()).PrintOut copies:=1
End Sub

Good Luck
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[misscrf]

Can you explain what this code means? I am kind of doing this as "fake work" because I have nothing to do, so learning vb is what I am doing.

I don't understand how this will define which choice equals which chart. I do have each chart in separate chart sheets.

I'm glad my post made you smile!

misscrf

Management is doing things right, leadership is doing the right things

 

[Loomah]

Now you're asking for something - me actually explaining what I've done (or what I think I've done!!)

I've just redone the code with comments and have highlighted these in bold below

'loop through each item in the listbox
For i = 0 To Me.ListBox1.ListCount - 1
'is the item sected?
If Me.ListBox1.Selected(i) Then
'resize the array, preserving existing values
ReDim Preserve myArray(cnt)
'add the relevant listbox item to the array
myArray(cnt) = Me.ListBox1.List(i)
'increment the array element
cnt = cnt + 1
End If
Next
'print all sheets that have been selected
Sheets(myArray()).PrintOut copies:=1

In essence what I'm trying to do is loop through all the items in the listbox to see if they have been selected.
If the have been selected I have added the value of the listbox item to an array.
At the end I've told excel to print out all the sheets in the array, ie those selected in the listbox.

As for which choice equals which chart I'm assuming that the values you loaded into the listbox are the names of the sheets (on the tabs) containing the charts.

I find it a bit tricky to explain in any greater depth without going on for hours!

As I said before this isn't something that I've necessarily done before so I've relied quite heavily on the help files and would suggest that you do the same in relation to the code I've given you. In particular have a look at:-
Selected
List
ReDim

If you'd like me to try to explain any of this in a little more detail, please post back and I'll try but I'm no teacher!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[misscrf]

This looks good, but I do have that array problem.

As I showed my array thing looks like:
MyArray(0, 0) = "Domestic Long Distance"
MyArray(1, 0) = "Toll Free"
MyArray(2, 0) = "Directory Assistance"
MyArray(3, 0) = "Local"
MyArray(4, 0) = "International"
MyArray(5, 0) = "Calling Cards"
MyArray(6, 0) = "Usage Type"

but Domestic Long Distance, would actually be the chart LD
Directory assitance would be DirAsst, and so on.

I wouldnt want the person choosing from the list to have to look at a list that has yucky names that I abbreviated, as their choices.

BTW you are a very good teacher!


misscrf

Management is doing things right, leadership is doing the right things

 

[Loomah]

I'm not 100% sure about this but I'd start looking at having a listbox with 2 columns:-
Col 1 has the full user friendly name in it
Col 2 has the actual sheet name in it


So when you initialise the form you will have something like
MyArray(0, 0) = "Domestic Long Distance"
etc
etc then
MyArray(0, 1) = "LD"
etc

I think, but I've never done it, that you'd be able to have Excel return the value in column 2 to the array in the code I gave you. It may even be possible to "hide" the second column from your users but I don't know.

As I say I've never done anything like this and I'm a little short on time for learning now but look into using multiple columns and reading from the second column. There seems to be quite a few examples in the help files that may get you on your way.

Again look at the List property for all this - that's where I'd start!

If you try to put something together and run into problems just post your problems to this forum. Someone will almost definately help you!

Good Luck
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[misscrf]

ok, to make things easier, I changed the names of the charts, so that they would be the nice names that I have in the array. Now here is the problem.

I am getting an error when I run this code. I have a command button that is supposed to show the userform that has the listbox.

Private Sub UserForm_Initialize()
'The 1st list box contains 7 data columns
ListBox1.ColumnCount = 7
'Load MyArray
myArray(0, 0) = "Domestic Long Distance"
myArray(1, 0) = "Toll Free"
myArray(2, 0) = "Directory Assistance"
myArray(3, 0) = "Local"
myArray(4, 0) = "International"
myArray(5, 0) = "Calling Cards"
myArray(6, 0) = "Usage Type"

'Load data into ListBox1
ListBox1.List() = myArray

End Sub

It highlights Private Sub UserForm_Initialize()
and says " wrong number of dimensions"

Any ideas?

Thanks again!


misscrf

Management is doing things right, leadership is doing the right things

 

[Loomah]

I'd suggest just changing the code in this situation as the difference in performance should be negligible.

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .AddItem "Domestic Long Distance"
        .AddItem "Toll Free"
        .AddItem "Directory Assistance"
        .AddItem "Local"
        .AddItem "International"
        .AddItem "Calling Cards"
        .AddItem "Usage Type"
    End With
End Sub

Means not bothering with however dimensions etc you have in your array!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[misscrf]

Thank you, Thank you, Thank you!


Wow

You are the greatest teacher in the world!

It worked!
awesome!
I love you!

Your my best friend!
heheheh

YAY!
You get 10 Stars!!!!!



misscrf

Management is doing things right, leadership is doing the right things