VBA Replace multiple ranges with different text

Question

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

It works just fine when I test it. Do you have any other code in the workbook? — braX
– braX, Commented Oct 28, 2021 at 7:45
a fair bit of other stuff, do you think there could be a conflict? — Wonton Animal Chin
– Wonton Animal Chin, Commented Oct 28, 2021 at 7:57
That's what it seems like to me. Have you tried it in a workbook that has only that code in it? If so, i am pretty sure you will see that it's doing what you tell it. — braX
– braX, Commented Oct 28, 2021 at 7:58
just tried it in another workbook, youre right. Not sure what i can be conflicting with tho. Thanks for the help — Wonton Animal Chin
– Wonton Animal Chin, Commented Oct 28, 2021 at 8:03
I can almost guarantee that you'll get a better idea of where the problem lies, if you try stepping through the code line by line (per @RaymondWu) while keeping a close eye on what's being affected by each line of code. Here's a link to some excellent advice about debugging VBA from VBA guru Chip Pearson. (🎗I'd also recommended bookmarking his list of VBA solutions for future reference.) — ashleedawg
– ashleedawg, Commented Oct 28, 2021 at 9:08

Raymond Wu · Accepted Answer · 2021-10-28 09:04:27Z

1

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

edited Oct 28, 2021 at 9:04

answered Oct 28, 2021 at 8:57

Raymond Wu

3,4272 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ashleedawg Over a year ago

I bet Raymond figured that out by stepping through the code. 😉

Raymond Wu Over a year ago

@ashleedawg I'm lucky that nothing is at stake! The code stopping at End immediately got me curious and that's where I notice the "typo"

Wonton Animal Chin Over a year ago

Legend @RaymondWu! Thanks so much for the help, and thanks to everyone else too!

Collectives™ on Stack Overflow

VBA Replace multiple ranges with different text

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related