CODEWITHSUNDEEP
excelvba
csmith711
csmith711

Reputation: 13

Name excel sheets with variables in VBA

I am trying to write a program in VBA that takes up to 22 user inputs and creates and names a sheet for every input.

I have a Userform that sets the variable to nothing when it initializes. I am having issues trying to:

  1. create a new sheet only if the user puts anything in that variable and
  2. Naming the sheet with their input.

Here is what I have so far

Public Part(1 to 22) as String

 Sub Start()

Application.ScreenUpdating = False

For i = 1 To 22
    If Part & i = "" Then

    Else
        Sheets.Add , after:=Worksheets(Worksheets.Count - 2)
        ActiveSheet.Name = Part & i            
    End If

Next i

Application.ScreenUpdating = True

End Sub

Here is where the values are added to the variable. PartNumber1 - 22 are names of textboxes on the user form

    Private Sub UserForm_Initialize()

PartNumber1.Value = ""
PartNumber2.Value = ""
PartNumber3.Value = ""
PartNumber4.Value = ""
PartNumber5.Value = ""
PartNumber6.Value = ""
PartNumber7.Value = ""
PartNumber8.Value = ""
PartNumber9.Value = ""
PartNumber10.Value = ""
PartNumber11.Value = ""
PartNumber12.Value = ""
PartNumber13.Value = ""
PartNumber14.Value = ""
PartNumber15.Value = ""
PartNumber16.Value = ""
PartNumber17.Value = ""
PartNumber18.Value = ""
PartNumber19.Value = ""
PartNumber20.Value = ""
PartNumber21.Value = ""
PartNumber22.Value = ""


End Sub

Private Sub PartNumDone_Click()


Part(1) = PartNumber1.Value
Part(2) = PartNumber2.Value
Part(3) = PartNumber3.Value
Part(4) = PartNumber4.Value
Part(5) = PartNumber5.Value
Part(6) = PartNumber6.Value
Part(7) = PartNumber7.Value
Part(8) = PartNumber8.Value
Part(9) = PartNumber9.Value
Part(10) = PartNumber10.Value
Part(11) = PartNumber11.Value
Part(12) = PartNumber12.Value
Part(13) = PartNumber13.Value
Part(14) = PartNumber14.Value
Part(15) = PartNumber15.Value
Part(16) = PartNumber16.Value
Part(17) = PartNumber17.Value
Part(18) = PartNumber18.Value
Part(19) = PartNumber19.Value
Part(20) = PartNumber20.Value
Part(21) = PartNumber21.Value
Part(22) = PartNumber22.Value



End Sub

Thanks!

Upvotes: 0

Views: 92

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Use arrays. Here is an example. PseudoUserform() is run first:

Public Part(1 To 22) As String

Sub PseudoUserform()
    Part(6) = "James"
    Part(8) = "Ravenswood"
End Sub

Sub Start()
Application.ScreenUpdating = False
For i = 1 To 22
    If Part(i) = "" Then
    Else
        Sheets.Add after:=Worksheets(Worksheets.Count - 2)
        ActiveSheet.Name = Part(i)
    End If
  Next i
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions