• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Select a range via hyperlink then print selection automatically

A

alexsheehan

Member
Hi guys


I want to be able to click a hyperlink in cell "I1" and then this will activate range "C2:Q26". Once the hyperlink is clicked and the relevant range is selected, I then want to print the selection automatically. The print range has to be dynamic.


Basically, I have a range of data in "C2:Q26" with a "Print" hyperlink in cell "I1", I can get the "Print" hyperlink to select the correct range, but I cannot figure out how to then print this range once selected.


The other problem is that the "Print" hyperlink needs to dynamically reference the print range, i.e. I have several ranges in worksheet "C2:Q26", "C36:Q60", "C70:Q94" etc. (this will expand down the sheet as more data is added) and each range has a "Print" hyperlink in cell "I1", "I35", "I69" respectively (again this will expand down the sheet as more data is addded).


I need each of those "Print" hyperlinks to automatically print the respective ranges but I don't want to manually have to adjust the print ranges every time I add more data ranges.


Does this make sense?


Any help is greatly appreciated, please let me know if you need me to clarify.
 
Hi Alex Sheehan,


I am not sure if this can be achieved by hyperlinks, but you can certainly do it by vba coding and worksheet change event as follows:


'place this code under sheet which has your form for print

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'if you click the cell which has "Print" text ..

If Target.Value = "Print" Then


Dim iCurRw As Integer


'tracks the row number + 1 as you have "print" one row above the print area

iCurRw = Target.Row + 1


'selects C col of given row + 25 rows to I col

Range("C" & iCurRw & ":I" & (iCurRw + 25)).Select


'the below code prints selected area and one copy

Selection.PrintOut Copies:=1


End If


End Sub


'*************************


option 2:


If My guess is right, you have some design or format of form with 25 rows and 15 columns..


Well, you can also get your problem solved by assigning the macro to a "shape" or "command button" or even use some "print pictures", it would look even more neat and professional:


Sub My_Print()


Dim iCurRw As Integer


iCurRw = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row + 2


Range("C" & iCurRw & ":I" & (iCurRw + 25)).Select

Selection.PrintOut Copies:=1


End Sub


note: each new shape or picture you use should have unique names like shape1 shape2 etc, if two shapes or pictures are same you would get first occurence of shape's area printed!


Hope I did not confuse you.


Regards,

Prasad
 
Hi Prasad


Many, many, many, many thanks!!!!! Option 1 works like a charm!


I bow to your superior knowledge - I have been scratching my head over this for AAAAAGGGGGGGES!!!!


Having said that.... since you mention the "Selection.PrintOut Copies:1" command which is great, is there a way of having a request that allows the user to select how many copies they want to print?


Thanks once again fo ryou response.


Alex
 
Addendum to post above, I have just noticed that if my "Print" cell (the cell with the hyperlink to range "C2:Q6") is in a merged cell, the above macro doesn't fire.


The correct range of cells is still highlighted as you would expect, but the SelectionChange event does not appear to be firing.


Any thoughts on this?


Thanks once again.
 
For the print options, change the Selection.Print line to this block:

[pre]
Code: 
Dim xCopies As Integer
On Error Resume Next
Do Until xCopies > 0
xCopies = InputBox("How many copies do you want?", "Copies", 1)
Loop
On Error GoTo 0
Selection.PrintOut Copies:=xCopies
[/pre]
For the merged cell issue...ugh. Merged cells cause many problems, usually more than they're worth. Any chance you can work around this? (maybe use the horizontal, center across selection alignement option?)
 
Thanks Luke, that also works like a charm! Thank you for your time.


On the subject of the merged cell issue - I don't have to use a merged cell, but the trouble with not using a merged cell is that because of the way my data is set up and the position I want the "Print" hyperlink in, the print button ends up being rather small and inconspicuous.


Therefore I wanted to increase the size of the "Print" hyperlink cell/area to increase visibility. I know I can use the "Merge and centre across selection" option, however the problem with this is that the hyperlink only works when the first cell in the 'unmerged' range is selected - not ideal.


In other words, short of completely redesigning my worksheet to incorporate a larger unmerged "Print" hyperlink cell (a daunting prospect considering the time it has already taken me), I am at a loss.


Thanks once again for the helpful tips so far.
 
An idea...

Let's say you want a "merged cell" size of 5x1. In all 5 cells, place the hyperlink. Color the font of the first 2 and last 2 cells white (invisible). Draw a border around all 5 cells so it looks like 1 big one. Would that work?
 
Many thanks Luke M and to Prasad for the original response,


To be honest, I wouldn't have ever thought of doing that at all instead of merging cells (well ok, maybe after HOURS of scratching my head and getting nowhere!). It just goes to show what a bit of lateral thinking does - unfortunately (for me at least) you appear to be better at this than I!


Thanks for your time, this does indeed work. In my case I wanted a "merged" cell of 3x1, but the principal is still the same. The font in the middle cell has to be a little smaller than I would like, but I can work around that minor detail! Thank you.
 
You can execute your print code specifically when a hyperlink is clicked by using the FollowHyperlink event in VBA.


This code in the worksheet that has the hyperlinks should work, I think, if you leave your hyperlinks such that they automatically select the report ranges:

[pre]
Code: 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Select Case Target.Name 'Seems to be the same as Target.TextToDisplay, but I think Excel will modify the text to be unique if more than one hyperlink withthe same text on the sheet.
Case "Print Report 1", "Print Report 2"
Selection.PrintOut Copies:=1
End Select
End Sub
[/pre]
The code fires AFTER Excel follows the hyperlink to it's destination.


You could also add to this a little code to set the selection back to some default after printing.


Asa
 
Hello again


Related to the post above, I have a hyperlink which selects Q1 Analysis, Q2 Analysis etc. and I wanted some code which would fire when each was clicked to allow the user to edit the respective Analysis.


I can get each hyperlink to selet the appropriate cell, that is no problem, however I have been trying to get the code below to work for each respective analysis, however, it only works on the first "Q1 Analysis" cell and I can't figure out why. Interestingly, when I select the hyperlink with "Q2", "Q3" etc., it still fires, but tries to edit "Q1" analysis.

[pre]
Code: 
If Target.Value = "Edit Q1 Analysis" Then

iCurRw = Target.Row + 2

Application.SendKeys "{F2}"
End If

If Target.Value = "Edit Q2 Analysis" Then

iCurRw = Target.Row + 2

Application.SendKeys "{F2}"
End If
[/pre]

I'm sure it is something really simple I am missing but cannot see what.


Cheers for your help.
 
Hi Alex ,


I think it has to do with two different declarations of the Target parameter ; Prasad had declared it as a Range , while Asa has declared it as a hyperlink.


If you are following Asa , then the statement Target.Row may have to be modified to Target.Range.Row ; also Target.Value may have to be modified to Target.Name.


Prasad or Asa can confirm.


Narayan
 
Thanks Asa/Narayan,


Hopefully to make things a little clearer, I think what I want is multiple "IF" statements, but I'm not sure of the syntax I should be using, let me explain;-


Something along the lines of;-

[pre]
Code: 
If Target.Value = "Print" Then

(print code goes here)

Else Target.Value = "Edit Q1 Analysis" Then

(Select the cell 2 rows directly beneath this one and press {F2})

Else Target.Value = "Edit Q2 Analysis" Then

(Select the cell 2 rows directly beneath this one and press {F2})
[/pre]

Etc...


Does this make sense?


Cheers guys


Alex
 
Scratch that! It was my own stupidity!


I had not updated my hyperlink references to highlight/reference the correct cell(s) so therefore everytime I was clicking on any "Edit" hyperlink, it was taking me to the exact same cell everytime and then {F2} was being fired (incorrectly on my part) on that cell.


Thanks guys for your help and support and sorry for being a pain in the ar**!


Regards, Alex
 
Hi Alex,

"Multiple If scenarios" are usually handled using a single If/Then/[ElseIf...]/[Else]/End If block, or a Select statement, as I have used below.


Here's a version that avoids the appearance of the selection changing, and prints using the built-in printer dialog (familiar; can select copies/printer/preview/cancel).


Since it uses the FollowHyperlink event, it will work no matter where your hyperlink is located, even a merge cell. It also avoids an isse with SelectionChange that if the user is navigating with the keyboard, the link cell could get selected without clicking, causing unexpected behavior.


This code handles both "Print" links and "Edit" links. Set the Address for all links to the range you want to print or edit.


In your worksheet code

[pre]
Code: 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim t As String
Dim DestinationRange As Range

t = Target.TextToDisplay

Select Case True
Case LCase(t) = "print" ' Hyperlink says "Print"
Set DestinationRange = Selection
Target.Range.Select ' Unselect report / Move selection to hyperlink cell
PrintThis DestinationRange
Case LCase(t) Like "edit *" ' Hyperlink says "Edit ..."
' Just edit the selected cell.
Application.SendKeys "{F2}"
End Select
Set DestinationRange = Nothing
End Sub
In a module[/b]

Public Sub PrintThis(PrintWhat As Object) ' Any selectable object reference cam be printed
Dim OldWindow As Window
Dim OldSelection As Object ' Could be other than cells selected
Dim OldCell As Range
Dim ScreenUpdateStatus As Boolean
On Error GoTo ErrorHandler

' Save ScreenUpdating status
ScreenUpdateStatus = Application.ScreenUpdating
' Disable Screen Updating to hide selection and/or window changes
Application.ScreenUpdating = False

'Save the state of windows and selection
Set OldWindow = ActiveWindow
PrintWhat.Parent.Activate
Set OldSelection = Selection
If TypeName(OldSelection) = "Range" Then Set OldCell = ActiveCell

'Select print area
PrintWhat.Select

' Print the selection (we could have changed the worksheet's Print Area instead of
' changing Selection, but then it's not clear from the dialog what will be printed.
Application.Dialogs(xlDialogPrint).Show Arg12:=1 ' Arg12/Print_What = 1/Selection

GoSub Cleanup
Exit Sub

Cleanup:
' Restore state of windows and selection
If Not OldSelection Is Nothing Then OldSelection.Select
If Not OldCell Is Nothing Then OldCell.Activate
If Not OldWindow Is Nothing Then OldWindow.Activate
' Free object references
Set OldSelection = Nothing
Set OldCell = Nothing
Set OldWindow = Nothing
' Restore ScreenUpdating
Application.ScreenUpdating = ScreenUpdateStatus
Return

ErrorHandler:
' Restore screen updating before failing
Application.ScreenUpdating = ScreenUpdateStatus
Err.Clear
' Try again and (probably) really fail
On Error GoTo 0
Resume
End Sub
[/pre]
Asa
 
Hi Alex,

"Multiple If scenarios" are usually handled using a single If/Then/[ElseIf...]/[Else]/End If block, or a Select statement, as I have used below.


Here's a version that avoids the appearance of the selection changing, and prints using the built-in printer dialog (familiar; can select copies/printer/preview/cancel).


Since it uses the FollowHyperlink event, it will work no matter where your hyperlink is located, even a merge cell. It also avoids an isse with SelectionChange that if the user is navigating with the keyboard, the link cell could get selected without clicking, causing unexpected behavior.


This code handles both "Print" links and "Edit" links. Set the Address for all links to the range you want to print or edit.


In your worksheet code

[pre]
Code: 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim t As String
Dim DestinationRange As Range

t = Target.TextToDisplay

Select Case True
Case LCase(t) = "print" ' Hyperlink says "Print"
Set DestinationRange = Selection
Target.Range.Select ' Unselect report / Move selection to hyperlink cell
PrintThis DestinationRange
Case LCase(t) Like "edit *" ' Hyperlink says "Edit ..."
' Just edit the selected cell.
Application.SendKeys "{F2}"
End Select
Set DestinationRange = Nothing
End Sub
In a module[/b]

Public Sub PrintThis(PrintWhat As Object) ' Any selectable object reference cam be printed
Dim OldWindow As Window
Dim OldSelection As Object ' Could be other than cells selected
Dim OldCell As Range
Dim ScreenUpdateStatus As Boolean
On Error GoTo ErrorHandler

' Save ScreenUpdating status
ScreenUpdateStatus = Application.ScreenUpdating
' Disable Screen Updating to hide selection and/or window changes
Application.ScreenUpdating = False

'Save the state of windows and selection
Set OldWindow = ActiveWindow
PrintWhat.Parent.Activate
Set OldSelection = Selection
If TypeName(OldSelection) = "Range" Then Set OldCell = ActiveCell

'Select print area
PrintWhat.Select

' Print the selection (we could have changed the worksheet's Print Area instead of
' changing Selection, but then it's not clear from the dialog what will be printed.
Application.Dialogs(xlDialogPrint).Show Arg12:=1 ' Arg12/Print_What = 1/Selection

GoSub Cleanup
Exit Sub

Cleanup:
' Restore state of windows and selection
If Not OldSelection Is Nothing Then OldSelection.Select
If Not OldCell Is Nothing Then OldCell.Activate
If Not OldWindow Is Nothing Then OldWindow.Activate
' Free object references
Set OldSelection = Nothing
Set OldCell = Nothing
Set OldWindow = Nothing
' Restore ScreenUpdating
Application.ScreenUpdating = ScreenUpdateStatus
Return

ErrorHandler:
' Restore screen updating before failing
Application.ScreenUpdating = ScreenUpdateStatus
Err.Clear
' Try again and (probably) really fail
On Error GoTo 0
Resume
End Sub
[/pre]
Asa
 
Hi Alex,

"Multiple If scenarios" are usually handled using a single If/Then/[ElseIf...]/[Else]/End If block, or a Select statement, as I have used below.


Here's a version that avoids the appearance of the selection changing, and prints using the built-in printer dialog (familiar; can select copies/printer/preview/cancel).


Since it uses the FollowHyperlink event, it will work no matter where your hyperlink is located, even a merge cell. It also avoids an isse with SelectionChange that if the user is navigating with the keyboard, the link cell could get selected without clicking, causing unexpected behavior.


This code handles both "Print" links and "Edit" links. Set the Address for all links to the range you want to print or edit.


In your worksheet code

[pre]
Code: 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim t As String
Dim DestinationRange As Range

t = Target.TextToDisplay

Select Case True
Case LCase(t) = "print" ' Hyperlink says "Print"
Set DestinationRange = Selection
Target.Range.Select ' Unselect report / Move selection to hyperlink cell
PrintThis DestinationRange
Case LCase(t) Like "edit *" ' Hyperlink says "Edit ..."
' Just edit the selected cell.
Application.SendKeys "{F2}"
End Select
Set DestinationRange = Nothing
End Sub
In a module[/b]

Public Sub PrintThis(PrintWhat As Object) ' Any selectable object reference cam be printed
Dim OldWindow As Window
Dim OldSelection As Object ' Could be other than cells selected
Dim OldCell As Range
Dim ScreenUpdateStatus As Boolean
On Error GoTo ErrorHandler

' Save ScreenUpdating status
ScreenUpdateStatus = Application.ScreenUpdating
' Disable Screen Updating to hide selection and/or window changes
Application.ScreenUpdating = False

'Save the state of windows and selection
Set OldWindow = ActiveWindow
PrintWhat.Parent.Activate
Set OldSelection = Selection
If TypeName(OldSelection) = "Range" Then Set OldCell = ActiveCell

'Select print area
PrintWhat.Select

' Print the selection (we could have changed the worksheet's Print Area instead of
' changing Selection, but then it's not clear from the dialog what will be printed.
Application.Dialogs(xlDialogPrint).Show Arg12:=1 ' Arg12/Print_What = 1/Selection

GoSub Cleanup
Exit Sub

Cleanup:
' Restore state of windows and selection
If Not OldSelection Is Nothing Then OldSelection.Select
If Not OldCell Is Nothing Then OldCell.Activate
If Not OldWindow Is Nothing Then OldWindow.Activate
' Free object references
Set OldSelection = Nothing
Set OldCell = Nothing
Set OldWindow = Nothing
' Restore ScreenUpdating
Application.ScreenUpdating = ScreenUpdateStatus
Return

ErrorHandler:
' Restore screen updating before failing
Application.ScreenUpdating = ScreenUpdateStatus
Err.Clear
' Try again and (probably) really fail
On Error GoTo 0
Resume
End Sub
[/pre]
Asa
 
Hi Alex,

"Multiple If scenarios" are usually handled using a single If/Then/[ElseIf...]/[Else]/End If block, or a Select statement, as I have used below.


Here's a version that avoids the appearance of the selection changing, and prints using the built-in printer dialog (familiar; can select copies/printer/preview/cancel).


Since it uses the FollowHyperlink event, it will work no matter where your hyperlink is located, even a merge cell. It also avoids an issue with SelectionChange that if the user is navigating with the keyboard, the link cell could get selected without clicking, causing unexpected behavior. It will, however, only work with hyperlink objects (Insert->Hyperlink), not formulas using the
Code: 
HYPERLINK function.


This code handles both "Print" links and "Edit" links.  Set the Address for all links to the range you want to print or edit.


[b]In your worksheet code

[pre][code]Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim t As String
Dim DestinationRange As Range

t = Target.TextToDisplay

Select Case True
Case LCase(t) = "print" ' Hyperlink says "Print"
Set DestinationRange = Selection
Target.Range.Select ' Unselect report / Move selection to hyperlink cell
PrintThis DestinationRange
Case LCase(t) Like "edit *" ' Hyperlink says "Edit ..."
' Just edit the selected cell.
Application.SendKeys "{F2}"
End Select
Set DestinationRange = Nothing
End Sub
In a module[/b]

Public Sub PrintThis(PrintWhat As Object) ' Any selectable object reference cam be printed
Dim OldWindow As Window
Dim OldSelection As Object ' Could be other than cells selected
Dim OldCell As Range
Dim ScreenUpdateStatus As Boolean
On Error GoTo ErrorHandler

' Save ScreenUpdating status
ScreenUpdateStatus = Application.ScreenUpdating
' Disable Screen Updating to hide selection and/or window changes
Application.ScreenUpdating = False

'Save the state of windows and selection
Set OldWindow = ActiveWindow
PrintWhat.Parent.Activate
Set OldSelection = Selection
If TypeName(OldSelection) = "Range" Then Set OldCell = ActiveCell

'Select print area
PrintWhat.Select

' Print the selection (we could have changed the worksheet's Print Area instead of
' changing Selection, but then it's not clear from the dialog what will be printed.
Application.Dialogs(xlDialogPrint).Show Arg12:=1 ' Arg12/Print_What = 1/Selection

GoSub Cleanup
Exit Sub

Cleanup:
' Restore state of windows and selection
If Not OldSelection Is Nothing Then OldSelection.Select
If Not OldCell Is Nothing Then OldCell.Activate
If Not OldWindow Is Nothing Then OldWindow.Activate
' Free object references
Set OldSelection = Nothing
Set OldCell = Nothing
Set OldWindow = Nothing
' Restore ScreenUpdating
Application.ScreenUpdating = ScreenUpdateStatus
Return

ErrorHandler:
' Restore screen updating before failing
Application.ScreenUpdating = ScreenUpdateStatus
Err.Clear
' Try again and (probably) really fail
On Error GoTo 0
Resume
End Sub[/code][/pre]
Asa
 
In the PrintThis procedure,

Code: 
If TypeName(OldSelection) = "Range" Then Set OldCell = ActiveCell


can be changed to

If TypeOf OldSelection Is Range Then Set OldCell = ActiveCell


It should be a little more efficient/elegant.
 
Thanks Asa, it's good to know there are a few different ways to accomplish the task, I have not tried your method yet, it does seem a little tidier and I will try this at some stage, but since I have a functioning system for printing/editing now, I have moved on to another problem which I am now trying to solve in urgency! As with most of these things, you solve one problem, only to create or find another!


Thank you very much all for your help on this.


Now for my tick box problem!!!
 
Good luck with your new task :)

Yes, one thing leads to another!


If you take one very practical thing from my example, let it be this:

Code: 
Application.Dialogs(xlDialogPrint).Show Arg12:=1


is the equivalent to

Selection.PrintOut


With the difference that it presents the Print dialog with the "Selection" option chosen, rather than immediately printing the selection with no user interaction.


Asa
 
You must log in or register to reply here.
Back
Top