Rename tabs based on cell values

[Manuprasad]

Hello,

Please help me in modifying the below code.

I have a code that will help me in renaming tabs based on values in 2 cells (drop down list). Now, I have to replace drop down list with Combo Box.
I m not sure how to modify the code that will help me to do same task I used to do using drop down list.

Sub RenameSheet()
    Dim worksh As Integer
    Dim worksheetexists As Boolean
    Dim ws As Worksheet
    worksh = Application.Sheets.Count
    worksheetexists = False
    
    For x = 1 To worksh
    If Worksheets(x).Name = ActiveSheet.Range("G17").Value & "-" & ActiveSheet.Range("G18").Value Then
            worksheetexists = True
            MsgBox ("Sheet with the name " + Worksheets(x).Name + " Already Exists!!!")
    ElseIf Worksheets(x).Name = ActiveSheet.Range("G17").Value & ActiveSheet.Range("G18").Value Then
            worksheetexists = True
            MsgBox ("Sheet with the name " + Worksheets(x).Name + " Already Exists!!!")
    Exit For
    End If
    Next x
    
    If worksheetexists = False Then
        If Range("G17").Value <> "" And Range("G18").Value <> "" Then
            For x = 1 To worksh
                 If ActiveSheet.Name = "MainSheet" Then
                    MsgBox "You Cannot Change Name of This Sheet!!!"
                 Else
                    ActiveWorkbook.Unprotect Password:="abc123"
                    ActiveSheet.Name = Range("G17").Value & "-" & Range("G18").Value
                    'Worksheets(UserForm1.txtNameSur.Value).Activate
                     ActiveWorkbook.Protect Password:="abc123"
                    'Debug.Print worksheetexists
                Exit For
                End If
                Next x
        ElseIf Range("G17").Value = "" Or Range("G18").Value = "" Then
                For x = 1 To worksh
                 If ActiveSheet.Name = "MainSheet" Then
                    MsgBox "You Cannot Change Name of This Sheet!!!"
                 Else
                    ActiveWorkbook.Unprotect Password:="abc123"
                    ActiveSheet.Name = Range("G17").Value & Range("G18").Value
                    ActiveWorkbook.Protect Password:="abc123"
                Exit For
                End If
                Next x
        End If
     End If
Application.DisplayAlerts = True
End Sub

 

[mrmmickle1]

You will need to change your code to incorporate the combobox values rather than your cell dropdowns in G18 & G19. You can retrieve combobox values like this:

[COLOR=#008000]'Replace G18 and G19 with values based on comboboxes in your code ie...[/COLOR]


[COLOR=#0000ff]Sub[/COLOR] Test1()
[COLOR=#008000]    'Form Control ComboBox[/COLOR]
    x = ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value
    myVal = ActiveSheet.Shapes("Drop Down 1").ControlFormat.List(x)
[COLOR=#0000ff]End Sub[/COLOR]


[COLOR=#0000ff]Sub[/COLOR] Test2()
[COLOR=#008000]    'ActiveX ComboBox[/COLOR]
    x = ActiveSheet.OLEObjects("ComboBox1").Object.Value
[COLOR=#0000ff]End Sub[/COLOR]