如何为Excel单元格NoteText的指定内容设置加粗格式？

经排查，目前仅能通过Range.NoteText为单元格添加批注文本，现需将其中txtFld6.Value & ": "部分（包含填写表单的员工ID）设置为加粗格式。虽然知道批注（Comments）有更多格式选项，但希望尽量保持当前手动操作的填充方式一致。

原相关代码片段 #

Cells(LR, 5).NoteText txtFld6.Value & ": " & modForms.Note1
Cells(LR, 6).NoteText txtFld6.Value & ": " & modForms.Note2

原完整Sub代码 #

'Submit button Subs
Private Sub cmbSubmit_Click()

    Dim LR As Integer
    
    Set modForms.ws = ActiveWorkbook.Worksheets.Item(modForms.Assay)
    modForms.ws.Activate
    
    LR = ActiveSheet.UsedRange.Rows.Count
    If Cells(LR, 1).Value <> "" Then
        ActiveSheet.ListObjects(modForms.Assay).ListRows.Add
    End If
    
    LR = ActiveSheet.UsedRange.Rows.Count
    
    Cells(LR, 1).Value = txtRun.Value
    Cells(LR, 2).Value = txtMonth.Value & "/" & txtDay.Value & "/" & txtYear.Value
    Cells(LR, 3).Value = cbInstrument.Value
    Cells(LR, 4).Value = txtSamples.Value
    Cells(LR, 5).Value = cbCT.Value
    Cells(LR, 5).NoteText txtFld6.Value & ": " & modForms.Note1
    Cells(LR, 6).Value = cbNEGEC.Value
    Cells(LR, 6).NoteText txtFld6.Value & ": " & modForms.Note2
    Cells(LR, 7).Value = txtFld1.Value
    Cells(LR, 8).Value = txtFld2.Value
    Cells(LR, 9).Value = txtFld3.Value
    Cells(LR, 10).Value = txtFld4.Value
    Cells(LR, 11).Value = txtFld5.Value
    Cells(LR, 12).Value = txtFld6.Value
    Cells(LR, 13).Value = txtFld7.Value
    Cells(LR, 14).Value = txtFld8.Value
    
    Unload Me

End Sub 

修改后的代码实现加粗效果 #

Range.NoteText方法无法直接设置部分文本的格式，需要改用Comment对象的文本格式设置方法。以下是调整后的代码，既保留原有填充逻辑，又实现员工ID部分的加粗：

'Submit button Subs
Private Sub cmbSubmit_Click()

    Dim LR As Integer
    Dim empIdText As String
    Dim commentText As String
    
    Set modForms.ws = ActiveWorkbook.Worksheets.Item(modForms.Assay)
    modForms.ws.Activate
    
    LR = ActiveSheet.UsedRange.Rows.Count
    If Cells(LR, 1).Value <> "" Then
        ActiveSheet.ListObjects(modForms.Assay).ListRows.Add
    End If
    
    LR = ActiveSheet.UsedRange.Rows.Count
    
    Cells(LR, 1).Value = txtRun.Value
    Cells(LR, 2).Value = txtMonth.Value & "/" & txtDay.Value & "/" & txtYear.Value
    Cells(LR, 3).Value = cbInstrument.Value
    Cells(LR, 4).Value = txtSamples.Value
    Cells(LR, 5).Value = cbCT.Value
    
    '处理第5列批注
    empIdText = txtFld6.Value & ": "
    commentText = empIdText & modForms.Note1
    '先清除原有批注（如果有）
    If Not Cells(LR, 5).Comment Is Nothing Then
        Cells(LR, 5).Comment.Delete
    End If
    '添加新批注
    Cells(LR, 5).AddComment commentText
    '设置员工ID部分为加粗
    With Cells(LR, 5).Comment.Shape.TextFrame.Characters(1, Len(empIdText)).Font
        .Bold = True
    End With
    
    Cells(LR, 6).Value = cbNEGEC.Value
    
    '处理第6列批注
    commentText = empIdText & modForms.Note2
    '先清除原有批注（如果有）
    If Not Cells(LR, 6).Comment Is Nothing Then
        Cells(LR, 6).Comment.Delete
    End If
    '添加新批注
    Cells(LR, 6).AddComment commentText
    '设置员工ID部分为加粗
    With Cells(LR, 6).Comment.Shape.TextFrame.Characters(1, Len(empIdText)).Font
        .Bold = True
    End With
    
    Cells(LR, 7).Value = txtFld1.Value
    Cells(LR, 8).Value = txtFld2.Value
    Cells(LR, 9).Value = txtFld3.Value
    Cells(LR, 10).Value = txtFld4.Value
    Cells(LR, 11).Value = txtFld5.Value
    Cells(LR, 12).Value = txtFld6.Value
    Cells(LR, 13).Value = txtFld7.Value
    Cells(LR, 14).Value = txtFld8.Value
    
    Unload Me

End Sub 

代码说明 #

• 定义empIdText变量存储需要加粗的员工ID文本，减少重复代码
• 添加批注前清除单元格原有批注，避免内容叠加
• 使用AddComment添加完整文本后，通过Characters(起始位置, 长度)定位员工ID部分，设置字体加粗
• 整体逻辑与原有填充流程一致，仅修改了批注的添加和格式设置方式

内容的提问来源于stack exchange，提问作者Ray WALKER