CODEWITHSUNDEEP
vbaexcelcombobox
diem_L
diem_L

Reputation: 399

VBA Name a Sheet with ComboBox

I have a commandButton which opens a UserForm to add a new Worksheet. In this Userform is a ComboBox, where i can choose the machine type.

Now i want to create a new Worksheet with the Name of the machine type which was selected in the ComboBox.

This is my Code:

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Sheets("Sheet1").Copy Before:=Sheets(10)
    ws.Name = ComboBox1
    [UserForm1].Hide
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = Array("Machine Type 1", "Machine Type 2")
End Sub

Is there a way to create a new sheet, which is a copy from Sheet1 and name it like the machine type from the ComboBox1?

Thanks for your help.

Upvotes: 0

Views: 722

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

Try the code below, see notes in the code's comments:

Private Sub CommandButton1_Click()

    Dim ws As Worksheet

    Sheets("Sheet1").Copy Before:=Sheets(10)
    Set ws = ActiveSheet ' <-- you need to set the worksheet object to the latest copied sheet
    ws.Name = ComboBox1.Value '<-- now you can modify the name using the worksheet object
    Me.Hide '<-- hide the user-form        
End Sub

Upvotes: 1

DisplayName
DisplayName

Reputation: 13386

use

Private Sub CommandButton1_Click()
    Sheets("Sheet1").Copy Before:=Sheets(Sheets.Count)
    ActiveSheet.Name = ComboBox1.Value
    Me.Hide
End Sub

Upvotes: 1

Related Questions