Erase a cell that has a link before the file is saved

[daniels012]

I have code below.

Select Case WB1.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "\\MIKESRGATEWAY\MikesProposals\" & strfilename
            
        Case "TD"
            strfilename = "\\Tomsblackibm\TomsProposals\" & strfilename
            
        Case "DJ"
            strfilename = "\\DAVEJONES\DavesProposals\" & strfilename
            
        Case "CP"
            strfilename = "\\Chuckscomputer\daily\" & strfilename
            
    End Select
    
    WB1.SaveCopyAs Filename:=strfilename
    
    WB1.ActiveSheet.Shapes("Button 53").Visible = False
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True
    
    WB1.Close

Where can I enter...
Range("M3").ClearContents
So when saved to the new computer this link is gone?

Thank You,
Michael

 

[LilStevie]

Option Explicit 
 
Sub DeleteLinksInBook() 
    Dim Ws As Worksheet, FirstSheet As Worksheet 
    Dim Query As VbMsgBoxResult 
    Query = MsgBox("CAUTION: Every single link, formula " & _ 
    "& hyperlink in this " & vbLf & _ 
    "book will be deleted - Do you " & _ 
    "still want to proceed?", vbYesNo, _ 
    "Sever All Links?") 
    If Query = vbNo Then Exit Sub 
    Set FirstSheet = ActiveSheet 
    Application.ScreenUpdating = False 
    For Each Ws In Worksheets 
        Ws.Activate 
         
         '****remove formulae & external links****
        With Cells 
            .Select 
            .Copy 
            Selection.PasteSpecial Paste:=xlValues 
            Application.CutCopyMode = False 
        End With 
         '**********************************
         
         '*********Remove Hyperlinks**********
        Cells.Hyperlinks.Delete 
         '**********************************
         
        [A1].Select 
    Next 
    FirstSheet.Activate 
End Sub

Found this code on vbaexpress knowledge base. You should be able to adopt this to your use.

 

[daniels012]

Thank You for your help!
I know the cell to erase that has the link.
I just need to know where to enter it in my code I provided!!

Michael

 

[Jonmo1]

mabye on the BeforeSave event

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
sheets("sheetname").Range("M3").ClearContents 
End Sub

since this is in a Workbook event, not a sheet event I believe you will have to specify the sheetname as well as the range.