Find and replace string in VBA Sub

[detel]

Fellow Forum Members,
can someone out there please help me out with a VBA script that will do the following:

Open the Code in UserForm1 Private Sub UserForm_Initialize()
Find the string "ABC" and replace it with "123"

Thanks in advance
Detlef

'This is, where I need the code:

Private Sub CommandButton1_Click()

If Range("A2").Value = "Stormdoor with Top and or Bottom Panel" Then

   With Sheets("StDoTopBot")
        .Select
    End With
End If

'Find the string "ABC" and replace it with "123"


End Sub

'This is the User Form
Private Sub UserForm_Initialize()
    Dim r As Integer
    Dim preselect As Integer
    Dim finalrow As Integer
    
    kereshossz = 0
    
    
    Sheets("Blocks").Select
    
    finalrow = Sheets("Blocks").Cells(Rows.Count, 4).End(xlUp).Row
        
    If finalrow < 2 Then
        MsgBox "Something went wrong! No data found in Blocks sheet!", vbCritical + vbOKOnly, "Problem"
        btnSelect.Enabled = False
        Exit Sub
    End If
    
        
    With ListBox1
        .ColumnHeads = False
        .ColumnCount = 2
        .ColumnWidths = "115;25"
        .RowSource = ""
    End With
    
    For r = 2 To finalrow
    
        ListBox1.AddItem Cells(r, 4)
        ListBox1.List(ListBox1.ListCount - 1, 1) = r
    
        
    Next r
    
    Sheets("ABC").Select
    
    
    
End Sub

[TMS]

Why don't you declare a Global variable and put the sheet name in that?

Public gShName As String

Then, rather than use:

    Sheets("ABC").Select

use

    Sheets(gShName).Select

Then you can set it to an initial value of "ABC" wherever you want, and change it to "123" somewhere else.

No need to write code to change code.

Regards, TMS

[detel]

Hi TMS,
Thanks for your response.
But I'm a Newbie in VBA and don't know what you mean, or how to do it.

I have different products and whenever I choose another Product I need only to replace the "ABC"
with the name of my worksheet.

This what I want to do with my private sub command.

Maybe it is more complicate, but I understand what i'm doing.

If you can give me the code for this, I would be more than happy.

Thanks a lot
Detlef

[TMS]

Maybe it is more complicate, but I understand what i'm doing.

Yes it is and no you don't ... otherwise you wouldn't be asking the question.