Frustrated - List Box
 

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

Frustrated - List Box
 

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.

You want to print hyperlinks?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Frustrated - List Box
 

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


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

You want to print hyperlinks?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.

Frustrated - List Box
 

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

wrote:
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


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

You want to print hyperlinks?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.

Frustrated - List Box
 

Insert Hyperlink.

I will try this and let you know.
Thank you for your help

RK



-----Original Message-----
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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

wrote:
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


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

You want to print hyperlinks?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.


.

Frustrated - List Box
 

Compile error - "Expected: Expression"


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

How can I add:
CheckBox1.Value

Thanks Again for your help.

RK


-----Original Message-----
Insert Hyperlink.

I will try this and let you know.
Thank you for your help

RK



-----Original Message-----
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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

wrote:
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


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

You want to print hyperlinks?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.


.

.

Frustrated - List Box
 

RK

Compile error - "Expected: Expression"


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


The line wrapped in your newsreader. This and the line below it are one
line

How can I add:
CheckBox1.Value


Add it to what?


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Frustrated - List Box
 

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

Frustrated - List Box
 

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.

Change the ListStyle property to fmListStyleOption to get checkboxes in your
listbox.

Thank you for your help. BTW I like your web site.

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Frustrated - List Box
 

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



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

Change the ListStyle property to fmListStyleOption to
get checkboxes in your
listbox.

Thank you for your help. BTW I like your web site.

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.

Frustrated - List Box
 

RK

Private Sub frmUserForm1_Initialize()

Dim cell As Range

Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 1

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

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

Next cell
End Sub



--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Frustrated - List Box
 

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




-----Original Message-----
RK

Private Sub frmUserForm1_Initialize()

Dim cell As Range

Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 1

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

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

Next cell
End Sub



--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.

Frustrated - List Box
 

RK


No change. Nothing prints out with the added code.
??

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.

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Frustrated - List Box
 

sent this to you "dick", per your site
Did you see what the problem might be

Thanks RK

Frustrated - List Box
 

Any luck

RK

Frustrated - List Box
 

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

--
BaronVonMarlo
-----------------------------------------------------------------------
BaronVonMarlon's Profile: http://www.excelforum.com/member.php...fo&userid=2823
View this thread: http://www.excelforum.com/showthread.php?threadid=21699

Frustrated - List Box
 

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"
<BaronVonMarlon.1x70ye_1129799119.0606@excelforu m-nospam.com wrote in
message news:BaronVonMarlon.1x70ye_1129799119.0606@excelfo rum-nospam.com...

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

THEREFORE, I would like to modify the code to look at a particular cell
- 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' by
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 Sub


--
BaronVonMarlon
------------------------------------------------------------------------
BaronVonMarlon's Profile:
http://www.excelforum.com/member.php...o&userid=28232
View this thread: http://www.excelforum.com/showthread...hreadid=216991