怎样使用VBA生成带圈之编号?

1、首先在开发工具中打开VBA编辑器

2、在单元格区域当中输入一些内容作为例子

3、在VBA编辑器中插入模块

4、在模块当中输入如下代码，然后运行

Sub 带圈编号()

 

Dim row1 As Integer, row2 As Integer, fontsize As Byte, ZT As String

 

On Error GoTo err

 

If Selection.Cells.Count=1 Then

 

row1=InputBox("请输入填充起始号", "序号", 1)

 

row2=InputBox("请输入填充终止号", "序号", 10)

 

fontsize=InputBox("请输入序号之字体大小", "字号", 10)

 

ZT=InputBox("请输入序号之字体" & Chr(10) & "若单元格较小，请用宋体！", "字体",

 

    "Impact")

 

Application.ScreenUpdating=False

 

For i=row1 To row2

 

    ActiveSheet.Shapes.AddShape(msoShapeOval, Selection.Left,

 

        Selection.Top, Selection.Width, Selection.Height).Select

 

    Selection.Characters.Text=i

 

    With Selection.Characters(Start:=1, Length:=Len(i)).Font

 

      .Name=ZT

 

      .Size=fontsize

 

    End With

 

    With Selection

 

      .ShapeRange.Fill.Visible=False

 

      .Font.ColorIndex=1

 

      .ShapeRange.Line.ForeColor.SchemeColor=8

 

      .ShapeRange.Line.Visible=msoTrue

 

      .HorizontalAlignment=xlCenter

 

      .VerticalAlignment=xlCenter

 

      .Orientation=xlHorizontal

 

    End With

 

    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

 

    Next

 

Else

 

MsgBox "请选择单个单元格再启用本程式", vbDefaultButton1+64, "提示"

 

End If

 

Application.ScreenUpdating=True

Exit Sub

 

err:

 

MsgBox "请选择单个单元格再启用本程式", vbDefaultButton1+64, "提示"

 

End Sub

5、将光标定位于需要产生编号的第一个单元格，利用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，将分别弹出输入起始号、终止号、字体大小及选择字体之对话框，如图