A01059 - 获取 Excel 菜单栏信息 - P38

admin · 发表于 2019-9-15 21:53:48

马上注册，结交更多好友，享用更多功能，让你轻松玩转新大榭论坛！

您需要登录才可以下载或查看，没有账号？注册

x

Public Sub 技巧1_059()* N2 H! Y: y2 q( f0 p
Dim myCBar As CommandBar, myCbarCnt As CommandBarControl% [4 e2 V. D' ?2 u j/ q0 e
Dim i As Long, myRng As Range
5 U8 ~- J) \5 w6 @$ l: _
Cells.Clear- d7 o8 Q* h- W2 E7 w8 P, S
Set myRng = Cells(1, 1)
7 g: ^5 p3 A# j8 d0 a
myRng.Value = 1
& p/ P0 S/ }3 A9 w( d& Q9 ]) H
Set myCBar = Application.CommandBars(1)
2 v0 @' L. r, q( F" E8 q
For Each myCbarCnt In myCBar.Controls
) k: J) k5 p- R/ P' s; r2 \, j, Y
With myCbarCnt, ?+ T! ]+ P! g! q9 }
myRng.Value = myRng.Value + 1; _! _( F" ]5 S8 g" E
Cells(myRng.Value, 1).Resize(, 7).Value = _; Y, _6 B) B: p% w; E. T
Array(myCBar.Index, myCBar.Name, myCBar.NameLocal, _
( ]+ n- n: \3 Z
myCBar.Type, .Caption, .ID, .Type)
Q/ R" p+ F& _" E; S y
If .Type = 10 Then% W/ V) A- K' I0 T) R% {
myRng.Value = myRng.Value - 1
' ?/ c5 Z: k7 z/ o# x' \+ L
Call mySub(myCbarCnt, 8, myRng)
: m5 i7 `+ W4 c. C
End If
& H6 D( j# I8 c, o% M& |
End With; `# D/ `: W2 R% x: q, Q2 ]% `8 r, H
Next7 I) R0 M' m, v1 ^% r8 d7 g; i
ActiveSheet.UsedRange.EntireColumn.AutoFit2 `0 S& M, B2 }* D" U8 t* H# T
Set myCBar = Nothing" S9 Z. c: S! i7 p) |
Set myCbarCnt = Nothing1 V* T0 D$ y$ _6 k E7 R
End Sub) _) ?: w, C. t6 {& {! z+ F
Public Sub mySub(myCnt As CommandBarControl, myClm As Long, myCell As Range)
~% r: j4 H" ~* C+ v+ {: N
Dim myChdCnt As CommandBarControl0 c2 H9 \+ \% `+ b2 q7 n9 [
For Each myChdCnt In myCnt.Controls
3 ]; d* S2 h( k. E
With myChdCnt
+ d) h' w0 k3 I+ ~. D2 x
myCell.Value = myCell.Value + 1
, b4 g8 ^, V. V
Cells(myCell.Value, myClm).Resize(, 3).Value = _
/ g" n6 n8 Z# Q
Array("'" & .Caption, .ID, .Type)
. S3 I% _ n# e% O' I
If .Type = 10 Then/ Y% k9 R" |/ G l# b0 j
myCell.Value = myCell.Value - 14 @$ D& \ T- r: a: ] C# T
Call mySub(myChdCnt, myClm + 3, myCell)
8 `9 q7 d5 p8 C; h0 j
End If
# [. w4 l6 h2 R5 N) R7 x8 S
End With
! o6 ~+ x$ ]% l. @8 f: n- k S- w
Next
7 a5 {# Q0 Q+ j6 p
Set myChdCnt = Nothing! y9 N }/ C/ Z: x8 I8 J8 Y
End Sub

复制代码

		自动登录	找回密码
密码			注册

[VBA精品课] A01059 - 获取 Excel 菜单栏信息 - P38

马上注册，结交更多好友，享用更多功能，让你轻松玩转新大榭论坛！