Frustrated - List Box

R

RK

First I created a forn and then . . .
I figured out how to create a list box for the various
spreadsheets that I have and to be able to select 1 or
more of them and then print them. I also have on my form
a check box to select all or deselect all.
See code below.


+ + + +

'Form Code 1


Private Sub CheckBox1_Click()
Dim iloop As Integer

For iloop = 1 To ListBox1.ListCount
ListBox1.Selected(iloop - 1) = CheckBox1.Value
Next
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then
Sheets(ListBox1.List(iloop - 1, 0)).PrintOut
ListBox1.Selected(iloop - 1) = False
End If
Next
End Sub

Private Sub UserForm_Initialize()
Dim sSheet

For Each sSheet In Sheets
If sSheet.Type = 3 Then 'Chart sheet
ListBox1.AddItem sSheet.Name
ElseIf WorksheetFunction.CountA(sSheet.Cells) > 0
Then
ListBox1.AddItem sSheet.Name
End If
Next sSheet
End Sub


+ + + +

But what I need to do is. . .
On ( sheet1,Column AC )
is a listing of Hyperlinks that I would like to do the
the same way. List in my list box and with a check box
be able to select one or more then hit my print button
that I created.

Does anyone know how I can do this?

Thank you for your help

RK
 
D

Dick Kusleika

RK
But what I need to do is. . .
On ( sheet1,Column AC )
is a listing of Hyperlinks that I would like to do the
the same way. List in my list box and with a check box
be able to select one or more then hit my print button
that I created.
Click to expand...

You want to print hyperlinks?
 
G

Guest

No, I would like to print the spreadsheet that the
hyperlink refers to.

I have 40 spreadsheets, of them I have 35 that I need to
print, based on a table I created. What spreadsheet is
printed is based on year and quarter.
I created (see code) a listing of all the spreadsheets and
with checkboxes that I can select one, two, or more and
then I can print that selection. The problem I have is
this:

2004 1st Qtr = sheet4
2004 2nd Qtr = sheet17
2004 3rd qtr = sheet29, etc.

depending on the year and quarter the spreadsheet changes.

So I created a hyperlink to correspond to the correct
worksheet.

If I use a listbox for the Hyperlink, I only shows the
frendly name and selecting that doesn't go to the sheet,
nor can I print the sheet.

What I am trying to do is to create a list box with
checkboxes, that show the friendly name of the hyperlink
and when selected, when I hit my cmdPrintButton, it would
print those sheets.

Excel programming is new to me, but what I have created
so far works very well.

Thank you for your help.

RK
 
D

Dick Kusleika

RK

How did you create the hyperlinks? Insert - Hyperlink or HYPERLINK
worksheet formula? If the former, try something like this

Private Sub CommandButton1_Click()

Dim i As Long

For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
Range(Me.ListBox1.List(i)).Parent.PrintOut
End If
Next i

End Sub

Private Sub UserForm_Initialize()

Dim cell As Range

Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 1
Me.ListBox1.ColumnWidths = "0;1"

For Each cell In Sheet1.Range("A2:A11").Cells
Me.ListBox1.AddItem cell.Hyperlinks(1).SubAddress
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) =
cell.Hyperlinks(1).TextToDisplay
Next cell
End Sub
 
R

RK

Compile error - "Expected: Expression"


Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) =

How can I add:
CheckBox1.Value

Thanks Again for your help.

RK
 
G

Guest

I would like to have check boxes listed in my listbox so I could select 1 or more to print
However, I think that I have figured it out. I will let you know if I have any problem
with this on Thursday. Thank you for your help. BTW I like your web site

RK
 
D

Dick Kusleika

RK
I would like to have check boxes listed in my listbox so I could select 1 or more to print.
However, I think that I have figured it out. I will let you know if I have any problems
with this on Thursday.
Click to expand...

Change the ListStyle property to fmListStyleOption to get checkboxes in your
listbox.
Thank you for your help. BTW I like your web site.
Click to expand...

You're welcome, and thanks for reading my site.
 
R

RK

The listbox is empy. here is the code as I have it:


Private Sub CommandButton1_Click()

Unload Me

End Sub

Private Sub CommandButton2_Click()

Dim i As Long

For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
'Range(Me.ListBox1.List(i)).Parent.PrintOut
Range(Me.ListBox1.List(i)).PrintOut
End If
Next i

End Sub

Private Sub frmUserForm1_Initialize()

Dim cell As Range

Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 1
Me.ListBox1.ColumnWidths = "0;1"

For Each cell In Sheet1.Range("AC2:AC69").Cells
Me.ListBox1.AddItem cell.Hyperlinks(1).SubAddress
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) =
cell.Hyperlinks.TextToDisplay
Next cell
End Sub


What am I missing?

Any help would be appreciated

RK
 
D

Dick Kusleika

RK
Private Sub frmUserForm1_Initialize()

Dim cell As Range

Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 1
Click to expand...

Comment out this line and see what shows up in the first column. If you get
what looks like a hyperlink subaddress, then your TextToDisplay property is
empty. If you get nothing in the first column, then I'm not sure what's
going on. You would get an error if there were no hyperlinks.
Me.ListBox1.ColumnWidths = "0;1"

For Each cell In Sheet1.Range("AC2:AC69").Cells
Me.ListBox1.AddItem cell.Hyperlinks(1).SubAddress
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) =
cell.Hyperlinks.TextToDisplay
Click to expand...

Add these lines here
Debug.Print cell.Address, cell.Hyperlinks.Count, cell.Hyperlinks(1).Address
Debug.Print cell.Hyperlinks(1).SubAddress, cell.Hyperlinks(1).TextToDisplay
Debug.Print "-----------------------------------------"
 
R

RK

Dick K.

No change. Nothing prints out with the added code.
??
I am using a form with other command buttons on it,
like a MainMenu. I have a CommandButton2_Click()
with the code attached to it.
I have this code in a form "frmUserForm1"
This code is not in Sheet1.
Is this where the problem is? If so How should I fix it.

Again, thank you for your help.
RK
 
D

Dick Kusleika

RK
No change. Nothing prints out with the added code.
??
Click to expand...

Nothing? The Hyperlinks.Count should produce something even if it's a zero.
Do you have the immediate window visible? That's where they would print.
I am using a form with other command buttons on it,
like a MainMenu. I have a CommandButton2_Click()
with the code attached to it.
I have this code in a form "frmUserForm1"
This code is not in Sheet1.
Is this where the problem is? If so How should I fix it.
Click to expand...

No, that's not the problem. The code should be in the initalize event, not
the click event. The hyperlinks that you want in the listbox are on Sheet1,
right?

Put a break point on the first line of the Initialize event and show the
form. Then use f8 to step through the macro and see if you can see what's
going on. If you like, you can email a copy of the workbook to me.
 
B

BaronVonMarlon

I use the same piece of code to print only selected sheets containin
data. However, it still adds them to the list if they contai
templates awaiting user input. I know this is obvious, however,
don't want them added to the print list unless a user has complete
certain fields.

THEREFORE, I would like to modify the code to look at a particular cel
- say R8 - and if it contains a number >=1, then add it to the list.

In addition, how do I also set it to have the boxes 'checked' b
default?

Here is the code I use with the user form:

Private Sub CheckBox1_Click()
Dim iloop As Integer

For iloop = 1 To ListBox1.ListCount
ListBox1.Selected(iloop - 1) = CheckBox1.Value
Next
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then
Sheets(ListBox1.List(iloop - 1, 0)).PrintOut
ListBox1.Selected(iloop - 1) = False
End If
Next
End Sub

Private Sub Label1_Click()

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub UserForm_Initialize()
Dim sSheet

For Each sSheet In Sheets
If sSheet.Type = 3 Then 'Chart sheet
ListBox1.AddItem sSheet.Name
ElseIf WorksheetFunction.CountA(sSheet.Cells) > 0 Then
ListBox1.AddItem sSheet.Name
End If
Next sSheet
End Su
 
T

Tom Ogilvy

Private Sub CommandButton2_Click()
Dim iloop As Integer
Dim sh as object
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then
set sh = SheetsSheets(ListBox1.List(iloop - 1, 0))
if sh.Range("R8").value = 1 then
sh.PrintOut
end if
ListBox1.Selected(iloop - 1) = False
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"BaronVonMarlon"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

list box remove item by combobox 4
Create Hyperlink to URL 4
For..Next error 'End if without block if' 6
CheckIBAN 3
Sorting a listbox when items are added 4
what is the code cann make this pls. help me 1
move item in multiselect listbox 1
Listbox Output to Individual Listboxes 1

Top