Disable form button based on cell value

[tezza]

Hi

How would I go about disabling a Form button if the value on sheet 1 at Cell M29 is filled?

 

[iggydarsa]

Private Sub UserForm_Activate()
    If Range("M29").Value <> "" Then
        CommandButton1.Enabled = False
    Else
        CommandButton1.Enabled = True
    End If
End Sub

 

[tezza]

Private Sub UserForm_Activate()
    If Range("M29").Value <> "" Then
        CommandButton1.Enabled = False
    Else
        CommandButton1.Enabled = True
    End If
End Sub

That doesn't appear to run. It's just runs as normal.

I changed CommandButton1 to NextNo as that's the macro name. Is that right?

 

[iggydarsa]

No it is the button name.

 

[tezza]

No it is the button name.

I've tried changing CommandButton1 to CommandButton12 and the the text on the button but neither work, what am I missing?

 

[tezza]

This works, however, it hides the button instead of disabling it:

Private Sub Worksheet_Change(ByVal Target As Range)
      If Range("M29") <> "" Then
          Buttons("Button 12").Visible = False
      ElseIf Range("M29") = "" Then
      Buttons("Button 12").Visible = True
End If
End Sub

I didn't realise it also had to be on the Worksheet and not in a module.

 

[iggydarsa]

if it allows you try .Enabled, not .Visible

 

[tezza]

if it allows you try .Enabled, not .Visible

Sadly .Enabled doesn't work.

 

[iggydarsa]

Not sure how to disable it but as a workaround, In the macro assigned to that button you can skip the code

Sub Button1_Click()
    'Put this code above your original code
    If Range("M29") <> "" Then Exit Sub
    
    'Your original code goes here
    
End Sub

 

[tezza]

Not sure how to disable it but as a workaround, In the macro assigned to that button you can skip the code

Sub Button1_Click()
    'Put this code above your original code
    If Range("M29") <> "" Then Exit Sub
   
    'Your original code goes here
   
End Sub

Yep, that works, thank you