Macro help

[Fanwood]

I am going to assign a macro to a check box.

right now I have it so that the when the check box is checked F5 will say true, and if unchecked it says False.

Now what I want to do is:

if F5 says True then C5,D5,E5 will remove any text in the cell and populate with N/A.
If F5 says False then the box will be blank and any other text can be written,pasted etc.

Ive tried using a formula
=IF(F5=TRUE,"N/A","")
But if I need to write in the cell it deletes the formula. So I think the best option is to assign the macro to the check box.

 

[Fanwood]

I actually figured most of it out. I just dont know how to end it. It gives me a compile error "COMPILE ERROR: Block If without End if" I am not sure what that means.

Sub Macro4()
'
' Macro4 Macro
'
If ActiveSheet.Range("F5").Value = "False" Then
ActiveSheet.Range("C5:E5").Value = "N/A"
If ActiveSheet.Range("F5").Value = "True" Then
ActiveSheet.Range("C5:E5").Value = ""
'
End Sub

 

[AngelJ]

Whenever you start an 'If Block' you have to end it with an 'End If'. This way the computer knows what exactly to do if the condition you specified is satisfied. In your code above it would look like this (the indentation helps distinguish the If Blocks):

Sub Macro4()
'
' Macro4 Macro
'
    If ActiveSheet.Range("F5").Value = "False" Then
        ActiveSheet.Range("C5:E5").Value = "N/A"
    End If
    
    If ActiveSheet.Range("F5").Value = "True" Then
        ActiveSheet.Range("C5:E5").Value = ""
    End If
'
End Sub

 

[Fanwood]

I just figured it out lol.

Thank you for your repsonse! Boy do I feel like a dummy.

 

[AngelJ]

Thank you for your repsonse! Boy do I feel like a dummy.

You're welcome! We all start somewhere.

 

[Fanwood]

Actually have an additional question if you are able to help.

I would also like to add into the macro, locking the cells.
If F5 = false then N/A and lock.
If F5 = True then blank and unlock.

 

[AngelJ]

Here you go. This will do it in fewer lines of code as well.

Sub Macro4()
    Range("C5:E5").Locked = (Range("F5") = False)
    Range("C5:E5").Value = IIf(Range("F5") = False, "N/A", Empty)
End Sub

 

[wigi]

Here you go. This will do it in fewer lines of code as well.

Sub Macro4()
    Range("C5:E5").Locked = (Range("F5") = False)
    Range("C5:E5").Value = IIf(Range("F5") = False, "N/A", Empty)
End Sub

If you want to go for less:

Sub Macro4()
    With Range("C5:E5")
          .Locked = Not Range("F5")
          .Value = IIf(Range("F5"), Empty, "N/A")
    End With
End Sub

 

[AngelJ]

If you want to go for less:

Sub Macro4()
    With Range("C5:E5")
          .Locked = Not Range("F5")
          .Value = IIf(Range("F5"), Empty, "N/A")
    End With
End Sub

Or, even 7 characters less!

Sub Macro4()
    With Range("C5:E5")
        x = Range("F5")
        .Locked = Not x
        .Value = IIf(x, Empty, "N/A")
    End With
End Sub

Edit: Also, changing the procedure name is not allowed!

 

[Fanwood]

thanks!!!!