Change Textbox & Combobox to Default names

[Amro El ghazawei]

Hi,

i work in a Project and i wasn't familiar with VBA when i started this project, so i rename the textbox & Combo boxes to with different names, but currently when i need to add new textbox or combo box , i had to make many changes manually to the code, so i am thinking i need to re change the textbox & combo box to their default names , so is there any codes to do so.

thanks.

 

[Amro El ghazawei]

Amro, I have downloaded your workbook and have had a first look at it. If I understand you right, this is your own project that started out simple but has grown beyond your first thoughts. The form design and programming would seem to support that impression. If this were my project I would seriously consider stepping back and redesigning the project. The forms and the worksheets are very interconnected and any wholesale changes (such as renaming form controls) will cause some serious problems. Has this project been accomplishing what it was designed to do? How much more needs to be done to finish it? Is this a personal project or one for you company? To help you I will need to have more detailed discussions with you to first understand the project and then to help you formulate a better solution.

HI

Thanks for your reply, let me reply on your points

1- This project i designed it for Project reports in our company to collect all data for all projects in one sheet i stared with an excel sheet , then i thought about VBA and user from.

2- i think i am almost finished with 90% of my design

2- this is my project.

 

[Zot]

No need to redo whole thing. I think I need to come up with list on what have been changed. Otherwise to run macro to modify macro which I have little experience ?

 

[Zot]

Your form has many pages. The code loop by pages so that the sequence of TextBox and ComboBox naming goes by by page. I see the page numbering is also jumping. SO, the page is also renamed.

To keep track of what changed. I created as Change Summary sheet to list down all changes. I only check few object but not thoroughly but I think it should be correct. I also did not loop through Userform. The code is written for frmForm. You can easily change for TRICOM_MGT

Sub RenameObject()

Dim m As Long, n As Long, p As Long
Dim row As Long, col As Long
Dim uf As Variant, pg As Variant
Dim ctl As Control
Dim wsSummary As Worksheet

Set uf = Application.VBE.ActiveVBProject.VBComponents("frmForm")

ActiveWorkbook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Change Summary"
Set wsSummary = ActiveWorkbook.Sheets("Change Summary")

For Each pg In uf.Designer.MultiPage1.Pages
    pg.Name = "Pg" & pg.Name
Next
p = 0
For Each pg In uf.Designer.MultiPage1.Pages
    p = p + 1
    pg.Name = "Page" & p
Next

col = 0
For Each pg In uf.Designer.MultiPage1.Pages
    row = 1
    col = col + 1
    wsSummary.Cells(1, col) = pg.Name
    For Each ctl In uf.Designer.MultiPage1.Pages(pg.Name).Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                row = row + 1
                wsSummary.Cells(row, col) = ctl.Name
                ctl.Name = "TB" & ctl.Name
            Case "ComboBox"
                row = row + 1
                wsSummary.Cells(row, col) = ctl.Name
                ctl.Name = "CB" & ctl.Name
        End Select
    Next
    col = col + 1
Next

m = 0
n = 0
col = 1
For Each pg In uf.Designer.MultiPage1.Pages
    row = 1
    col = col + 1
    For Each ctl In uf.Designer.MultiPage1.Pages(pg.Name).Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                n = n + 1
                ctl.Name = "TextBox" & n
                row = row + 1
                wsSummary.Cells(row, col) = wsSummary.Cells(row, col) & ctl.Name
            Case "ComboBox"
                m = m + 1
                ctl.Name = "ComboBox" & m
                row = row + 1
                wsSummary.Cells(row, col) = wsSummary.Cells(row, col) & ctl.Name
        End Select
    Next
    col = col + 1
Next

End Sub