• 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.

Add hyperlink to other sheets from shapes added thru VBA

HanSam

Member
I am using below code to generate files based on unique values from a column on a raw data.
This is originally from Ron's work and I adapted it to my needs. My problem now is adding a link on the generated "buttons" on Home sheet. I have been on it for half a day now, and I can't make it to work. Can someone please help?
 

Attachments

p45cal

Well-Known Member
You need to add two lines to your Copy_To_Workbooks macro in Module1.
Specifically, (1) you have a With pms…End With block and just before End With (line 280 of the module) you need to add:
Code:
.Parent.Hyperlinks.Add Anchor:=pms, Address:="", SubAddress:="'Previous Month'!A1"
and (2) you have a With cms…End With block and just before End With (line 251 of the module) you need to add:
Code:
.Parent.Hyperlinks.Add Anchor:=cms, Address:="", SubAddress:="'" & WSNew.Name & "'!A1"
 

HanSam

Member
Thank you. Got it working then saw your post. On the same code, do you know how I can generate the new files and the gridlines are off on those files for all sheets?
 

p45cal

Well-Known Member
Turning off gridlines is one of those few cases where you need to select the sheets you want to process, because the command works on a window, not a sheet.
Probably the easiest is just before you save and close the file, you makes sure it's the active file and
Code:
    Set shtOrig = ActiveSheet 'store which sheet is the active sheet
    Sheets.Select 'will select all sheets as long as none are hidden
    ActiveWindow.DisplayGridlines = False
    shtOrig.Activate 'put the selected sheet back to what it was
 
Top