Add new sheet/change properties

[Zanatos1986]

I am writing code from a drop down box to add a new sheet to my workbook, expand upon a name and change properties of the new sheet.

I am on a sheet labeled "GTS Addition", on this page I have a dropdown list and a blank box to inset my info.

Dropdown List ($N$10) Options - X, Y
Blank Box ($N$12)

My code currently adds a new sheet with the name of the blank box. I need to change the color of the tab based upon the name of the dropdown list, I also need to add the name in the blank box to a predefined list on the main page "GTS Library"

I have listed my code below, I have tried several things but I am just not getting anywhere. All help is appreciated

' Add Company

Private Sub CommandButton2_Click()

Dim AddAsLastWorksheet()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Range("$N$12").Value

If Range($n$10).Value = X Then
    With ActiveWorkbook.Sheets("Test").Tab
        .Color = 10498160
        .TintAndShade = 0
    End With
End If

If Range($n$10).Value = Y Then
    With ActiveWorkbook.Sheets("Test").Tab
        .Color = 5287936
        .TintAndShade = 0
    End With
End If

End Sub

 

[gtche98]

Couple of things that I see with the code right off the bat is that your Xs and Ys in your if/then statements need to be in quotes (ie If Range($n$10).Value = "X")

Second thing is that the With statements are referring to "Test" sheet, and not the sheet you added in the Worksheets.Add statement.

 

[Zanatos1986]

I added the quotes.
As far as the "test" sheet, I'm not sure how to re-access the sheet I just created after going back to the sheet that determines the properties of the new sheet... I am aware that is wrong, and that is where I am stuck

 

[gtche98]

You can either set up the sheet name as a string variable or add the new worksheet as a worksheet variable. Either one of these should work:

By the way - Not sure what you are trying to do with this line of code: "Dim AddAsLastWorksheet()"

'Add Company
 
Private Sub CommandButton2_Click()
 
 
Dim AddAsLastWorksheet()
Dim SheetName as String
 
SheetName = Range("$N$12").Value
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetName
 
If Range($n$10).Value = "X" Then
    With ActiveWorkbook.Sheets(SheetName).Tab
        .Color = 10498160
        .TintAndShade = 0
    End With
End If
 
If Range($n$10).Value = "Y" Then
    With ActiveWorkbook.Sheets(SheetName).Tab
        .Color = 5287936
        .TintAndShade = 0
    End With
End If
 
End Sub

or:

Private Sub CommandButton2_Click()
 
 
Dim AddAsLastWorksheet()
Dim WS as Worksheet
 
SheetName = Range("$N$12").Value
WS.Add(After:=Worksheets(Worksheets.Count)).Name = Range("$N$12").Value
 
If Range($n$10).Value = "X" Then
With WS.Tab
.Color = 10498160
.TintAndShade = 0
End With
End If
 
If Range($n$10).Value = "Y" Then
With WS.Tab
.Color = 5287936
.TintAndShade = 0
End With
End If
 
End Sub