[已解决]用VBA 判断是否存在 工作表按钮

qdwfjmqj

Option Compare Text
Sub 存货编码()
On Error GoTo 200
Dim arr(), krr()
With Sheets(1)
r = .Cells(.Rows.Count, 1).End(xlUp).Row
If .Range("g1").Value <> "输入存货名称" Then
.Range("f1:m" & r).ClearContents
.Columns("b").NumberFormatLocal = "00000"
.Range("g1").Value = "输入存货名称"
.Range("h1").Value = "输入存货规格"
End If
If ActiveSheet.DrawingObjects.Count = 0 Then
ActiveSheet.Buttons.Add(788, 15, 59.25, 22.5).Select  '创建按钮
Selection.Text = "分析"            '按钮改名
Selection.OnAction = "sheet1.存货编码"    '设置按钮执行的宏名称
ActiveSheet.Buttons.Add(788, 40, 59.25, 22.5).Select  '创建按钮
Selection.Text = "清除"            '按钮改名
Selection.OnAction = "sheet1.qingc"    '设置按钮执行的宏名称
End If
q = .Range("g2").Value
qq = .Range("h2").Value
arr = .Range("b2:e" & r).Value
If q <> "" And qq = "" Then
shu = 2: yy = .Range("g2").Value: GoTo 100
ElseIf qq <> "" And q = "" Then
shu = 3: yy = .Range("h2").Value: GoTo 100
ElseIf q <> "" And qq <> "" Then
For ci = 1 To UBound(arr)
    If arr(ci, 2) Like "*" & q & "*" And arr(ci, 3) Like "*" & qq & "*" Then
       w = w + 1
       ReDim Preserve krr(1 To r, 1 To 4)
          krr(w, 1) = arr(ci, 1)
          krr(w, 2) = arr(ci, 2)
          krr(w, 3) = arr(ci, 3)
          krr(w, 4) = arr(ci, 4)
    End If
Next ci
      .Range("f2:i" & r).Value = krr
End If
100:
If shu = 2 Or shu = 3 Then
For ci = 1 To UBound(arr)
    If arr(ci, shu) Like "*" & yy & "*" Then
       w = w + 1
       ReDim Preserve krr(1 To r, 1 To 4)
          krr(w, 1) = arr(ci, 1)
          krr(w, 2) = arr(ci, 2)
          krr(w, 3) = arr(ci, 3)
          krr(w, 4) = arr(ci, 4)
    End If
Next
      .Range("f2:i" & r).Value = krr
End If
.Columns("f").NumberFormatLocal = "00000"
End With
200:
End Sub
Sub qingc()
With Sheets(1)
.Range("f2:i" & .Cells(.Rows.Count, 1).End(xlUp).Row).ClearContents
End With
End Sub

qdwfjmqj

上面是我写的代码，可以用，感觉不爽，也没必要去优化了，反正数据不多，就3W多行，还是很快，但就是判断按钮有点不爽，因为我的用法的原因，必须判断第一次添加了按钮后，第二次就不在添加按钮了，所以必须要个简单的判断！用的是工作表按钮

大灰狼1976

Sub test()
For Each c In Sheet1.Buttons
  If c.Text = "分析" Then ......
Next
End Sub

qdwfjmqj

大灰狼1976 发表于 2017-8-18 17:11
Sub test()
For Each c In Sheet1.Buttons
  If c.Text = "分析" Then ......

这个就是我需要的答案 谢谢，我现在的VBA只能算大半桶水技术哈哈哈！以后希望多多支持