[含整理]R1C1引用样式与A1引用样式之间的切换

keypress

搞了一个按钮，以方便操作
原贴见http://club.excelhome.net/thread-86482-1-1.html
感谢shuiyuan等坛友的付出

1. 
   
2. '------------------------以下内容放在ThisWorkBook中-------------------
   
3. Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
4. Application.CommandBars("MyBar").Delete
   
5. End Sub
   
6. Private Sub Workbook_Open()
   
7. Application.CommandBars.Add(Name:="MyBar", Position:=msoBarRight).Visible = True
   
8. NewFace
   
9. End Sub
   
10. 
    
11. '-----------------------以下内容放在模块1中--------------------------
    
12. Option Explicit
    
13. Sub NewFace()
    
14.     Dim Newbutton As CommandBarControl
    
15.     Dim myPic1, myPic2, myTxt As String
    
16.     Dim foundFlag As Boolean
    
17.     Dim cb As CommandBarControl
    
18.    
    
19.     myPic1 = "R1.bmp"    'R1C1图片请自行调整路径
    
20.     myPic2 = "A1.bmp"    'A1图片
    
21.     With Application
    
22.         If .ReferenceStyle = xlR1C1 Then
    
23.             myTxt = myPic2
    
24.         Else
    
25.             myTxt = myPic1
    
26.         End If
    
27.     End With
    
28. foundFlag = False
    
29. For Each cb In CommandBars("MyBar").Controls
    
30.     If cb.Caption = "转换" Then
    
31.         cb.Visible = True
    
32.         foundFlag = True
    
33.     End If
    
34. Next cb
    
35.     With Application
    
36.         If Not foundFlag Then
    
37.             Set Newbutton = .CommandBars("MyBar").Controls.Add(Type:=msoControlButton, Before:=1)
    
38.             With Newbutton
    
39.                 .Picture = LoadPicture(myTxt)
    
40.                 .Style = msoButtonIcon    '图像
    
41.                 .Caption = "转换"    '标题
    
42.                 .Visible = True    '显示
    
43.                 .OnAction = "RC_A1"    '运行指定的过程名
    
44.             End With
    
45.         Else
    
46.             Set Newbutton = .CommandBars("MyBar").Controls(1)
    
47.             With Newbutton
    
48.                 .Picture = LoadPicture(myTxt)
    
49.                 .Style = msoButtonIcon    '图像
    
50.                 .Caption = "转换"    '标题
    
51.                 .Visible = True    '显示
    
52.                 .OnAction = "RC_A1"    '运行指定的过程名
    
53.             End With
    
54.         End If
    
55.     End With
    
56. End Sub
    
57. '----------------------
    
58. 'R1C1样式与A1样式的转换
    
59. '
    
60. Private Sub RC_A1()
    
61.     With Application
    
62.         If .ReferenceStyle = xlR1C1 Then
    
63.             .ReferenceStyle = xlA1
    
64.         Else
    
65.             .ReferenceStyle = xlR1C1
    
66.         End If
    
67.     End With
    
68.     Call NewFace
    
69. End Sub
    
70. 
    

复制代码

个人经验，存为加载项（.xla），这样可以在EXCEL启动时随时调用.
Enjoy with Excel