VBA Replace multiple ranges with different text-CodePudding

Im not very familiar with VBA code and was hoping someone could help me with this code.

Im trying to replace 3 different ranges with 3 different values.

The current code ive got seems to only execute the first line and the last 2 lines are ignored

Private Sub CommandButton3_Click()
Range("B2, F1, F3:f5, F20, F22:F24") = "Enter Text Here..."
Range("C2, F2, F21") = "Select 1"
Range("C3:C10, B13:B19, B22:B25, A29:D33,F7:F16, F26:F35").ClearContents
End Sub

Any help or direction would be appreciated! Thanks in advance

Heres the whole code;

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    If Me.Range("B56").Value = 0 Then
    Dim xsheet As Worksheet
    Set xsheet = Sheets("Heroes_list")
        
            If xsheet.Name <> "Definitions" And xsheet.Name <> "fx" And xsheet.Name <> "Needs" Then
                xsheet.Range("A2:AC2").Copy
                xIntR = xsheet.UsedRange.Rows.Count
                xsheet.Cells(xIntR   1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                MsgBox "Saved Successfully!"
            End If

        Application.ScreenUpdating = True
        
Else
    If Me.Range("B56").Value = 1 Then _
        
    MsgBox "Not allowed, please revise costs, conflicts and all fields are filled correctly."
     End If

        Application.ScreenUpdating = True
 End If

        Application.ScreenUpdating = True
End Sub


    


Private Sub CommandButton2_Click()

Application.DisplayAlerts = False
Worksheets("Heroes_list").Copy '  golly im good
With ActiveWorkbook
    ChDir "C:\Users\Evane\Documents\Calix\Photoshop_excel_imports\"
    .SaveAs Filename:="C:\Users\Evane\Documents\Calix\Photoshop_excel_imports\hero" & ".txt", FileFormat:=xlText, CreateBackup:=False
    .Close False
End With
Application.DisplayAlerts = True
MsgBox "Exported successfully!"

End Sub

Private Sub Worksheet_Calculate()
If Me.Range("B52").Value = 1 Then _
MsgBox "The current effect combination for this PRIMARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
End

If Me.Range("B54").Value = 1 Then _
MsgBox "The current effect combination for this SECONDARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
End


If Me.Range("A49").Value > 1 Then _
MsgBox "Element conflict! The same element cannot be listed under both weakness and resistance!"
End
End Sub

Public Sub PasteasValue()
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("b56") = 1 Then Sheets("Hero_creation").CommandButton1.BackColor = RGB(255, 0, 0) 'Red!
If Range("b56") = 1 Then Sheets("Hero_creation").CommandButton1.Font.Strikethrough = True  'strikethrough text
If Range("b56") = 0 Then Sheets("Hero_creation").CommandButton1.BackColor = RGB(0, 255, 0) 'Green!
If Range("b56") = 0 Then Sheets("Hero_creation").CommandButton1.Font.Strikethrough = False 'No strik through text
End Sub

'New Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.address = "$B$2" Or Target.address = "$F$1" Or Target.address = "$F$20" Or Target.address = "$C$2" Or Target.address = "$F$2" Or Target.address = "$F$3" Or Target.address = "$F$4" Or Target.address = "$F$5" Or Target.address = "$F$21" Or Target.address = "$F$22" Or Target.address = "$F$23" Or Target.address = "$F$24" Or Target.address = "$C$3" Or Target.address = "$C$4" Or Target.address = "$C$5" Or Target.address = "$C$6" Or Target.address = "$C$7" Or Target.address = "$C$8" Or Target.address = "$C$9" Or Target.address = "$C$10" Then
        If Target.Value = "" Then  '  case a cell was emptied
         Let Application.EnableEvents = False
         Let Target.Value = "Enter Text Here..."
         Let Application.EnableEvents = True

        Else  '  case a text was entered
            With Target.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            End With
        End If
    Else
    ' Target is Not a cell to be acted on
    End If
End Sub

Private Sub CommandButton3_Click()
    Range("B2, F1, F3:f5, F20, F22:F24") = "Enter Text Here..."
    Range("C2, F2, F21") = "Select 1"
    Range("C3:C10, B13:B19, B22:B25, A29:D33,F7:F16, F26:F35").ClearContents
    End Sub

CodePudding user response：

In your Worksheet_Calculate event, you are missing the If in your Ends which ends up as a End statement, this stops the entire procedure immediately

I have also removed _ after Then.

Full code below:

Private Sub Worksheet_Calculate()
    If Me.Range("B52").Value = 1 Then
        MsgBox "The current effect combination for this PRIMARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
    End If
    
    If Me.Range("B54").Value = 1 Then
        MsgBox "The current effect combination for this SECONDARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
    End If
        
    If Me.Range("A49").Value > 1 Then
        MsgBox "Element conflict! The same element cannot be listed under both weakness and resistance!"
    End If
End Sub

I assume Worksheet_Calculate is triggered because you have formula(s) from other cells that are affected by the change so if you do not want that to be triggered, manipulate Application.EnableEvents property:

Private Sub CommandButton3_Click()
    Application.EnableEvents = False
    Range("B2, F1, F3:f5, F20, F22:F24") = "Enter Text Here..."
    Range("C2, F2, F21") = "Select 1"
    Range("C3:C10, B13:B19, B22:B25, A29:D33,F7:F16, F26:F35").ClearContents
    Application.EnableEvents = True
End Sub

CodePudding user response：

Try using: Range().Value = ""