How do you use VBA to enable/disable form control button based on change in a cell.

[Wontonjon]

Hello, so tbh I’m very new at this, in fact I just started using VBA yesterday. I’m trying to create a form that inserts new data into another sheet by a push of a macro enabled button. This part I’ve actually already accomplished.

What I’d like to do with the button is to only enable it when someone changes the date on the form, and once it’s been pressed, disable it again so only one entry per new date is allowed for this form, to avoid duplicate data.

Below is where I have ended up so far. This doesn’t seem to work at all haha.

I have even gone as far as replacing the If Target.Addess = “$C$7” with If range (“$C$7”).value = 10 and just input a value of 10 in C7, and this doesn’t seem to even disable the button.

So again, changing the cell with the date would enable the button, and the action of pressing the button itself would once again disable the button, is what I’m hoping.

FYI, LOGFORM, is the name of the form control command button.


Private Sub Worksheet Change(ByVal Target As Range)

If Target.Address= "$C$7" Then

Sheets("Sheet12").LOGFORM.Enabled = True

Else

Sheets("Sheet12").LOGFORM.Enabled = False

End If
End Sub

—————————————————————
Private Sub CommandButton1 Click()

End Sub

 

[6StringJazzer]

Right direction. Your code disables the button if you make a change to any cell other than C7. You don't want to do that. Once you enable the button you want it to stay enabled until you click it. Then your Click handler has to disable it, although you also need whatever code you want for clicking the button.

You have spaces where you need underscores in your code. This code does not compile.

This code must go into the VBA module for the sheet you want to work on, which appears to be Sheet12. If you put it in a standard module (like Module1) it won't do anything. I suspect that is why your code doesn't do anything.

I highly recommend that after you paste code into a post, you select the code and click the VBA button in the edit panel to add code tags. This will preserve the formatting of the code.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = "$C$7" Then
      Sheets("Sheet12").LOGFORM.Enabled = True
   End If
  
End Sub

Private Sub CommandButton1_Click()

   Sheets("Sheet12").LOGFORM.Enabled = False

End Sub

 

[Wontonjon]

Right direction. Your code disables the button if you make a change to any cell other than C7. You don't want to do that. Once you enable the button you want it to stay enabled until you click it. Then your Click handler has to disable it, although you also need whatever code you want for clicking the button.

You have spaces where you need underscores in your code. This code does not compile.

This code must go into the VBA module for the sheet you want to work on, which appears to be Sheet12. If you put it in a standard module (like Module1) it won't do anything. I suspect that is why your code doesn't do anything.

I highly recommend that after you paste code into a post, you select the code and click the VBA button in the edit panel to add code tags. This will preserve the formatting of the code.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = "$C$7" Then
      Sheets("Sheet12").LOGFORM.Enabled = True
   End If
 
End Sub

Private Sub CommandButton1_Click()

   Sheets("Sheet12").LOGFORM.Enabled = False

End Sub

Hey thank you for the feedback. Yup I actually do have it on the worksheet and not a separate module and sounds good on the VBA button. I’ll give this a try!

 

[Wontonjon]

It worked! This is actually what I ended up with, FYI (with a additional code to run macro and pull up a message box). Thanks again for the input!

Private Sub Worksheet Change (ByVal Target As Range)

If Target.Address ="SC$7" Then

Sheets("InstrumentVerification").ControlButton1.Enabled = True

End If
End Sub
——————————————————————
Private Sub ControlButtonl Click()

Call data_input

Sheets("InstrumentVerification").ControlButton1.Enabled = False

If Sheets ("InstrumentVerification").ControlButton1.Enabled = False Then

MsqBox "New date must be entered before form can be logged"

End If
End Sub

 

[6StringJazzer]

How are you grabbing this code? It is still missing "_" characters in the Sub names.

 

[Wontonjon]

I’m actually taking a picture of it and grabbing the text off of the picture with my iPhone lol. The code is on my work laptop that I don’t browse external sites with.