工作表内容变更时批量移除/修改超链接屏幕提示的方法咨询

Got it, let's tackle this problem head-on. The core issue here is that Excel's HYPERLINK function doesn't have a built-in parameter to set or modify screen tips—so we'll use a VBA Worksheet Change event to automatically update those tips whenever your sheet content changes. Here's a straightforward, step-by-step solution:

Step-by-Step Implementation #

1. Open the VBA Editor
   Press Alt + F11 to launch the VBA editor window.

2. Select Your Target Worksheet
   In the left-hand "Project Explorer" pane, find and double-click the worksheet containing your hidden C-column links and D-column hyperlinks (e.g., Sheet1). This will open a blank code window for that sheet.

3. Paste the VBA Code
   Copy and paste the following code into the code window:

   Private Sub Worksheet_Change(ByVal Target As Range)
       ' Define the range of hyperlinks to update (D5 to D42, matching your 38-row C-column range starting at C5)
       Dim linkRange As Range
       Set linkRange = Me.Range("D5:D42")
       
       ' Only run the code if the changed area overlaps with our hyperlink range
       If Not Intersect(Target, linkRange) Is Nothing Then
           ' Disable event triggers temporarily to avoid infinite loops
           Application.EnableEvents = False
           
           Dim cell As Range
           For Each cell In linkRange
               ' Check if the cell contains a hyperlink
               If cell.Hyperlinks.Count > 0 Then
                   ' Set your custom screen tip here
                   cell.Hyperlinks(1).ScreenTip = "Link will open in another application"
                   ' To REMOVE the screen tip entirely, replace the line above with:
                   ' cell.Hyperlinks(1).ScreenTip = ""
               End If
           Next cell
           
           ' Re-enable event triggers
           Application.EnableEvents = True
       End If
   End Sub
   

4. Save Your Workbook
   Save your file as an Excel Macro-Enabled Workbook (.xlsm)—regular .xlsx files don't support macros, so this step is critical.

How It Works #

• The Worksheet_Change event fires automatically whenever any cell in the sheet is modified.
• We first check if the changed cells are within your D-column hyperlink range (D5:D42). If not, the code stays idle to avoid unnecessary processing.
• For each hyperlink in the target range, we update its screen tip to your custom text (or clear it entirely if you use the commented line).
• We disable EnableEvents during the update to prevent the code from triggering itself repeatedly as it modifies cells.

Quick Adjustments #

• If your C-column range starts at a different row (not C5), tweak the linkRange to match your D-column starting row and total rows (e.g., if C1:C38, use D1:D38).
• When opening the workbook, make sure to enable macros via the security prompt at the top of the sheet—click "Enable Content" to activate the code.

内容的提问来源于stack exchange，提问作者Rahilkhan Pathan