VBA column print hyperlinks

[Zigman36]

Hi All,

Need to the world of VBA so apologies before I start!
I have a column for each week of the year, in each column I have a few hyperlinks that are required to be printed off in as each week begins.

So far I have created a Command button and assigned the VBA below (for the first column representing Week 1 'col L')
This is manually selected by the user at week start.
Question is,
Is the best way forward to create and 50+ more command buttons and add 50+ modules with code to assign against or is there any better methods? Maybe using a calendar etc?
Thanks in advance!

Sub ButtonJan1_Click()
'
' ButtonJan1_Click Macro
' Jan 1 Print all hyperlinks
'

'
Range("L8:L108").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("Annual Overview").Select
ActiveSheet.Shapes.Range(Array("Button 23")).Select
Application.GoTo Reference:="ButtonJan1_Click"
ActiveWorkbook.Save
ActiveWorkbook.Save
End Sub

 

[Micron]

IMO, 50 of anything is a bad idea. My limit would be maybe 3. If you describe the user actions and desired outcome a bit more, you might get more responses. I can't figure out why you need 50+ of anything to loop/scan over the same range (L8:108) on what seems like the same sheet.

That being said, perhaps the code needs a loop (from col L/12) to col 12+50 (for 50 weeks I suppose). Knowing what the user actions are/are wanted would also help to use the right event. Perhaps a cell double click, and maybe raise an input box to get the starting cell (or even the entire range)

Please post code within code tags (use vba button on posting toolbar) for more than a couple of lines. This makes it easier to read and maintains indentation.

 

[Zigman36]

Hi Micron,

thanks for the reply....I'll try to elaborate some more.

It's essentially a task planner. Each week of the year is represented by a column.
Column L would represent week 1 of January, Col M would be week 2 of Jan and so on.

There are a number of tasks to complete over the year, there could be seven to complete in week 1, three to complete in week 2 etc.
These tasks can occur at different frequencies.
There are approx. 80 tasks, which I have listed in Col B, effectively meaning all 'Week' columns have 80 rows. I mark the relevant row in each Week Col against the required task.

I have some VBA that will send email reminders for each task (to aid with planning).
Conditional formatting is used to highlight tasks missed, completed etc.
Each task has a specific set of instructions. When a task is planned for any given week, I mark the appropriate cell and add a hyperlink to the task instruction details.
For training, standards (and so on) purposes, it is best to print the task instruction out and sign off once complete.

The idea had been to the tasks printed out at the start of each week. Rather than clicking several times to do this, I thought a one button approach would be better.
I had assumed that one command button per 'week' column, (52 weeks of year), which when clicked would print out all hyperlinked task instructions for that given week.

I have it working for the first column, but doing separate buttons for each week, appears messy / elongated in the VBA.

I had been wondering if there is a better / slicker way?

Sub ButtonJan1_Click()
'
' ButtonJan1_Click Macro
' Jan 1 Print all hyperlinks
'

'
Range("L8:L108").Select

Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

Sheets("Annual Overview").Select
ActiveSheet.Shapes.Range(Array("Button 23")).Select
Application.GoTo Reference:="ButtonJan1_Click"
ActiveWorkbook.Save
ActiveWorkbook.Save

End Sub

 

[Zigman36]

Should mention even though I have only 80 tasks, I have the row range 8-108 for future add ins.

regards

 

[Micron]

These are just my thoughts:
One button. A way to target the proper column is needed. I take it that 1st, 8th, etc. are day numbers starting in Jan. 2024. You could have more than one of those values along that range so I consider it to be more difficult than if you had a row (additional or not) with week numbers. Using application.WorksheetFunction.WeekNum(Date()) you can run it for the current week regardless of what day it is. This could be useful, because it should still work if Monday was a holiday and you're doing this on Tuesday. This also assumes you want to run it for the current week, not next week because you're doing this for Monday but on a Friday. For that you'd have to add a prompt of some sort. Not a great idea if you want to minimize having to dismiss bothersome prompts. If you want to eliminate a lot of the guess work, use a form and use controls to provide as many options or variations that you might think of.

Another option would be dbl click. Run code only if dbl click is in the range of column 7 (?) to 59 (?). Then you could stick with the day dates (I think) and the column being processed is the one that was dbl clicked. That seems simple to me. Now for the hyperlinks: I assume they are always the same for a given task. Why not avoid the repetition of adding them every week? Why not put them in a separate column and use them only if the intersecting cell of task row/week column is "marked"? I have no idea what "marked" means, but if you've inserted an 'x' in column 12 in rows 5,6 and 7, then double clicked on A12, the code only uses the hyperlinks in those marked rows.

You don't need to use 108 rows if you find the last row in column A that has a value. There are options; one way is
Cells(rows.count, "A").End(xlUp).Row

 

[Zigman36]

Thanks Micron,

I like the application.WorksheetFunction.WeekNum(Date()) suggestion.
I could change the week beginning to dates to Week 1 etc. as you say (and that would tie in with the company's financial year calendar).

The screen shot wasn't that clear, the tasks do intersect the week columns where required. I had been using '-' to highlight the planned task needing done and manually dropping the hyperlink in.
'Y' and 'N' where the conditional formatting refs to show if the task has been completed or not.
I would need to use the '-' to drive the print function to the 'Task' column which would contain the hyperlinked tasks.

All food for thought for the week when I'm back in work.

Thanks again for the direction

 

[Micron]

You're welcome. Hope you keep us posted. I think there are many ways you could approach this (sorry if I'm repeating myself - only 1 coffee so far!) :
- button runs code for current week number column (maybe use cf on week num's so there's no confusion about what week it is). Not real flexible IMO.
- or dbl click desired column
- or button raises range input box and one week number cell is chosen
- having hyperlinks permits people to click them. What happens then? If not desired, use file path w/o hyperlink. Could hide file path text.
- if you're sending emails the idea of including the file as an attachment might seem slick. However, people have a way of saving them in a convenient place, then the document is changed and they're using the old one. That is one reason why I'm not sure the idea of sheet hyperlinks is good. They can follow and save these files.

I'm guessing the code needs to validate which rows in a column contain "-" but then what? Don't print file if there is a completed date? Or a sent date? Do you want repeat printouts where something has been sent but is incomplete (or overdue)? Or do you just want to send another email?
Things to think about.

 

[Zigman36]

Having another play around with the code today.

I liked the idea of printing per date range, but then thought what happens if we're trying to print the week before to cover holidays. So I've shelved that idea.
I'll stick with a button per column for now. Once fully populated it should never change for years to come, (If I change column titles for 'week 1', 'week 2' as opposed to first Monday dates for each week).

I will probably try to figure out how to print a ref hyperlink if a cell is intersected as suggested previously.
For now I am trying to prove code to loop through a column range, select any hyperlinks and print.

Currently my code will select the first hyperlink, open in new window but will try to print the worksheet range and not the opened hyperlinked page.
How would i correct this?
I think I need an array for the looping piece, but how do I print the open hyperlink.
(the hyperlinks are from a Sharepoint folder, so i'm assuming this is OK as they have an HTTP address as is fine that way?)

Selection.Hyperlinks(1) - if I have several hyperlinks, does the (1) change to 'x' for example?
There will always be at least one hyperlink, so I don't think I need to checks for missing?

thanks in advance

Sub ButtonJan1_Click()
'
' ButtonJan1_Click Macro
' Jan 1 Print all hyperlinks
'
    Sheets("Annual Overview").Select
    ActiveSheet.Range("L8:L85").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, Preview:=True, IgnorePrintAreas:=False
    ActiveSheet.Shapes.Range(Array("Button 23")).Select
    Application.GoTo Reference:="ButtonJan1_Click"
    ActiveWorkbook.Save
    
End Sub

 

[Micron]

I don't have experience with this so hopefully someone will chime in. Maybe the printing problem is that the ActiveWindow is the wb you're in, not the one you opened. Maybe I can help you troubleshoot in the meantime.

You should step through your code (F8) and verify things as you go and watch if it flows as you expect. When you get to the printout line in this case, you can ask what the name of an object is by typing in the immediate window
?ActiveWindow.SelectedSheets.Name <and hit Enter after that.

Virtually every object has a name property so I find it very useful.

 

[Zigman36]

So, I've had a bit of a think regarding sheet layout etc.
In column B, I will have several rows of hyperlinked PDF's. _ Columns L - BL will have drop down boxes.
I would like to print the hyperlinks from Col B that intersect the other Col L-BL, if the cells have a value.
The cell will either be blank, or have a selection of 'Y, N, - '
I know that i can identify cells with values (or formula), I've had success at printing the odd hyperlinked PDF.
How would I code "If cell L8 has a value, then print hyperlink in cell B8"? Then loop through the rest of the cells in Col B.

I think a CMD button per Column (L through to BL) is the best way.
It will involve alot of duplication, but gives the user opportunity to print any relevant Col without worrying about dates. (Col L to BL are effectively week 1, week 2, week 3 etc etc)

thanks