Visible/Invisible Several Button, 3 Criteria

Hello blues_oo7,

This proved to be an interesting challenge. It seemed straightforward in the beginning but took some unexpected turns. User Defined Functions are tricky. When run in VB environment there are no restrictions. However, when used on a worksheet, things get interesting.

To make this hide an unhide the button, the UDF have to be made volatile. Rather than executing when needed, it is executed every time a formula on the worksheet is recalculated. This can slow you down.

The UDF takes four arguments: The Button Index Number, the column "F" cell, the column "D" cell, and $G$2. The UDF returns either TRUE or a Blank. The UDF formula is behind each button.

Here is the UDF code:

Function ShowHideButton(ByVal ButtonIndex As Integer, Cond1 As Range, Cond2 As Range, Cond3 As Range)

  Dim Btn As Excel.Shape
  Dim R As Long
  Dim Res As Boolean
  
    Application.Volatile
    
      Res = True
      Set Btn = ActiveSheet.Shapes("Button " & ButtonIndex)
      
        If Cond1.Value = "PAID" Or Cond2.Value >= 999 Then
           Res = False
        End If
        
       'Ignore any link errors
        On Error Resume Next
          If Cond3 = 9999 Then Res = False
        On Error GoTo 0
        
      If Res = True Then ShowHideButton = Res Else ShowHideButton = ""
      
      Btn.Visible = Res
      
End Function

Hello blues_oo7,

What other buttons? I only saw "PAID" buttons on the worksheet.

I think this one-liner suffices:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect([D:D, F:G], Target) Is Nothing Then Sheets(1).Buttons(Target.Row - 2).Visible = Not (Cells(Target.Row, 4) >= 999 Or Cells(Target.Row, 6) = "PAID" Or Cells(Target.Row, 7) = 9999)
End Sub