gpt4 book ai didi

vba - If 语句中的 InStr

转载 作者:行者123 更新时间:2023-12-02 23:34:31 25 4
gpt4 key购买 nike

我需要循环浏览工作簿中的所有工作表。如果工作表的名称包含文本“Benefits”,那么我需要在该工作表上作为事件工作表运行宏,然后循环到下一个工作表。

我有这段代码,(1)它以任何一种方式运行宏,并且(2)它不会循环到下一张纸。

我是 VBA 新手,还无法弄清楚这一点。

Sub CheckSheets()
Dim sh As Excel.Worksheet
For Each sh In ActiveWorkbook.Sheets
If InStr(sh.Name, "Benefits") <> 0 Then
Call AddCheckBoxesRange
End If
Next sh
End Sub


Sub AddCheckBoxesRange()
'by Dave Peterson
'add Form checkboxes
Dim c As Range
Dim myCBX As CheckBox
Dim wks As Worksheet
Dim rngCB As Range
Dim strCap As String

Set wks = ActiveSheet
Set rngCB = wks.Range("B3:E3")
'Set rngCB = Selection
strCap = "Select Plan"

For Each c In rngCB
With c
Set myCBX = wks.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
End With
With myCBX
.Name = "cbx_" & c.Address(0, 0)
.LinkedCell = c.Offset(1, 0) _
.Address(external:=True)
.Caption = strCap
' .OnAction = ThisWorkbook.Name _
' & "!mycbxMacro"
End With
Next c

End Sub

最佳答案

Sub CheckSheets()
Dim sh As Excel.Worksheet
For Each sh In ActiveWorkbook.Sheets
If sh.Name Like "*benefits*" Then
Call AddCheckBoxesRange(sh.Name)
End If
Next sh
End Sub


Sub AddCheckBoxesRange(sName)
ActiveWorkbook.Sheets(sName).Activate
'by Dave Peterson
'add Form checkboxes
Dim c As Range
Dim myCBX As CheckBox
Dim wks As Worksheet
Dim rngCB As Range
Dim strCap As String

Set wks = ActiveSheet
Set rngCB = wks.Range("B3:E3")
'Set rngCB = Selection
strCap = "Select Plan"

For Each c In rngCB
With c
Set myCBX = wks.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Height:=.Height, Left:=.Left)
End With
With myCBX
.Name = "cbx_" & c.Address(0, 0)
.LinkedCell = c.Offset(1, 0) _
.Address(external:=True)
.Caption = strCap
'.OnAction = ThisWorkbook.Name _
' & "!mycbxMacro"
End With
Next c
End Sub

关于vba - If 语句中的 InStr,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42147874/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com