Delete Cell Content at the end of code.

[bamaisgreat]

Thanks for looking at my post. I am trying to figure out how delete the text typed in cell AE30 at the end of the code below.

Sub worksheet_change(ByVal target As Range)
Set target = Range("AE30")


If target.Value = "unprotect" Then
Call up_all_sheets
End If
If target.Value = "protect" Then
Call p_all_sheets
End If
If target.Value = "add" Then
Call UserForm7.Show


End If


End Sub

 

[mole999]

range("EA30")="" or .ClearContents or VBNullString

 

[Joe4]

You do not set "Target" to anything. "Target" is simply the cell that was updated that triggered this code to run.
So, it is already pre-defined and pre-set at the time the code runs.

If you want clear cell AE30 at the end, just add the line:

Range("AE30").ClearContents

 

[bamaisgreat]

I believe I have the code in the wrong location . Where should it go?

Sub worksheet_change(ByVal target As Range)
Set target = Range("AE30")


If target.Value = "unprotect" Then
Call up_all_sheets
End If
If target.Value = "protect" Then
Call p_all_sheets
End If
If target.Value = "add" Then
Call UserForm7.Show


Range("AE30").ClearContents


End If


End Sub

 

[Joe4]

You placed it within an IF statement, namely:

If target.Value = "add" Then
Call UserForm7.Show


Range("AE30").ClearContents


End If

That means that line will only run if they value updated is "add".
If you want it to run no matter what, place it under the last "End If" statement.

By the way, do you want this code to only run when a particular cell is updated to "unprotect", "protect", or "add"?
Because right now, the way you have it written, this code will run when ANY cell on your sheet is updated to these values.

 

[bamaisgreat]

Oh wow I thought it only worked if the text was enterd in cell AE30. Can you help with that? Below is the entire code I have in the sheet.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(target, Range("AE29")) Is Nothing Then
            Call PrintUserformShow
        End If
    End If
End Sub

Sub worksheet_change(ByVal target As Range)
Set target = Range("AE30")


If target.Value = "unprotect" Then
Call up_all_sheets
End If
If target.Value = "protect" Then
Call p_all_sheets
End If
If target.Value = "add" Then
Call UserForm7.Show
End If
End Sub

 

[Joe4]

Oh wow I thought it only worked if the text was enterd in cell AE30. Can you help with that? Below is the entire code I have in the sheet.

I thought that was maybe what you were trying to do. You need to check to see if the cell just updated (Target) is cell AE30. Here is one way to accomplish what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if cell AE30 was updated
    If Target.Address = Range("AE30").Address Then

'       Determine what to do
        Select Case Target.Value
            Case "unprotect"
                Call up_all_sheets
            Case "protect"
                Call p_all_sheets
            Case "add"
                Call UserForm7.Show
        End Select

'       Clear target cell (AE30)
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    
    End If

End Sub

I also used a Case statement instead of a series of IF/THEN statements. I think they are easier to follow.
See: https://www.techonthenet.com/excel/formulas/case.php

 

[bamaisgreat]

Perfect. Thanks for the help. That would have caused much pains in the future if you wouldnt have told me.

 

[Joe4]

You are welcome!