UserForm to and run VBA - unable to find answer

[Phoenix_p]

I have a weekly schedule which I duplicate often. I have Frankensteined a vba (from too many sources to list) that duplicates the first worksheet, names the sheet, and inserts the date. I get information for the variables from 3 InputBoxs. To simplify this I wanted to create a UserForm that has all the input fields for the variables (already created). However I do not know how to have these textboxes transfer the content to the variables and run the script.

I have googled this and searched here, however the only guides I can find shows how to link the UserForm to cells in the table. Can someone help me with how to do this?

Thank you for any help anyone can offer


This is the code I have managed to put together

Sub Create6()
'Updateby Extendoffice
    Dim I As Long
    Dim xNumber As Integer
    Dim xCurrent As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    Dim StartDate As Date
    Dim nSheets As Integer
    On Error Resume Next
    Application.ScreenUpdating = False
    ' Number of start worksheet
    nSheets = ThisWorkbook.Sheets.Count
    Set xActiveSheet = ActiveSheet
    StartDate = InputBox("Start date")
    xCurrent = InputBox("Start Week")
    xNumber = InputBox("End Week")
    

    
    For I = xCurrent To xNumber
        xName = ActiveSheet.Name
        ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
        ActiveSheet.Name = "W-" & I
        Range("A2").Value = ActiveSheet.Name
        Range("A3").Value = ThisWorkbook.Sheets.Count
        Range("A4").Value = nSheets

        If ThisWorkbook.Sheets.Count = nSheets + 1 Then
        Range("D4").Value = StartDate
        Range("D5").Value = Range("D4").Value + 1
        Range("D6").Value = Range("D5").Value + 1
        Range("D7").Value = Range("D6").Value + 1
        Range("D8").Value = Range("D7").Value + 1
        Range("D9").Value = Range("D8").Value + 1
        
        Else
        
        Range("D4").Value = Worksheets(xIndex - 1).Range(D4) + 7
        Range("D5").Value = ActiveSheet.Previous.Range(D5) + 7
        Range("D6").Value = 3
        Range("D7").Value = 4
        Range("D8").Value = 5
        Range("D9").Value = DateAdd("d", 7, "ActiveSheet.Previous.Range(D5)")
        Range("B14").Value = ActiveSheet.Index
        Range("B15").Value = ActiveSheet.Index - 1
        End If
        

    Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

 

[breynolds0431]

You could just use the userform to run the entire code. However you currently start the Create6 sub you could instead have ufCreate6.show to show the userform with the three text boxes. Then use a command button on the form to call your code - note the variables now using the textbox info.

Here's a picture of the userform with the object names (i.e; text boxes and command button).

Option Explicit

Private Sub cbProcess_Click() 'cbProcess is the name of the command button
'Updateby Extendoffice
    Dim I As Long
    Dim xNumber As Integer
    Dim xCurrent As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    Dim StartDate As Date
    Dim nSheets As Integer
    On Error Resume Next
    Application.ScreenUpdating = False
    ' Number of start worksheet
    nSheets = ThisWorkbook.Sheets.Count
    Set xActiveSheet = ActiveSheet

    'Variables with userform text boxes
    'The tbStartDate, tbStartWeek, and tbEndWeek are the names I gave to each textbox on the UF
    StartDate = Me.tbStartDate 
    xCurrent = Me.tbStartWeek
    xNumber = Me.tbEndWeek
        
    For I = xCurrent To xNumber
        xName = ActiveSheet.Name
        ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
        ActiveSheet.Name = "W-" & I
        Range("A2").Value = ActiveSheet.Name
        Range("A3").Value = ThisWorkbook.Sheets.Count
        Range("A4").Value = nSheets

        If ThisWorkbook.Sheets.Count = nSheets + 1 Then
        Range("D4").Value = StartDate
        Range("D5").Value = Range("D4").Value + 1
        Range("D6").Value = Range("D5").Value + 1
        Range("D7").Value = Range("D6").Value + 1
        Range("D8").Value = Range("D7").Value + 1
        Range("D9").Value = Range("D8").Value + 1
        
        Else
        
        Range("D4").Value = Worksheets(xIndex - 1).Range(D4) + 7
        Range("D5").Value = ActiveSheet.Previous.Range(D5) + 7
        Range("D6").Value = 3
        Range("D7").Value = 4
        Range("D8").Value = 5
        Range("D9").Value = DateAdd("d", 7, "ActiveSheet.Previous.Range(D5)")
        Range("B14").Value = ActiveSheet.Index
        Range("B15").Value = ActiveSheet.Index - 1
        End If
        

    Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
    Unload Me 'closes the userform
End Sub

 

[Phoenix_p]

Thank you that was exactly what I was after.

I realised when I came back here I had posted a previous version of my code so I have included the end result here. Well it's not the end result I need to finalise which cells it will be goin into whne I finish up the new worksheet template, but that's for another day now.

Option Explicit


Private Sub cbProcess_Click()
 
    Dim I As Long
    
    Dim xName As String
    Dim xActiveSheet As Worksheet
    Dim nSheets As Integer
    
    Dim StartDate As Date
    Dim xCurrent As Integer
    Dim xNumber As Integer
    
    On Error Resume Next
    
    Application.ScreenUpdating = False
    
    ' Number of start worksheet
    nSheets = ThisWorkbook.Sheets.Count
    Set xActiveSheet = ActiveSheet
  
    'Variables with userform text boxes
    'The tbStartDate, tbStartWeek, and tbEndWeek are the names I gave to each textbox on the UF
    StartDate = Me.tbStartDate
    xCurrent = Me.tbStartWeek
    xNumber = Me.tbEndWeek

    
    For I = xCurrent To xNumber
        xName = ActiveSheet.Name
        ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
        ActiveSheet.Name = "Week " & I
        Range("A1").Value = ActiveSheet.Name

        If ThisWorkbook.Sheets.Count = nSheets + 1 Then
        Range("D4").Value = StartDate
        Range("D5").Value = Range("D4").Value + 1
        Range("D6").Value = Range("D5").Value + 1
        Range("D7").Value = Range("D6").Value + 1
        Range("D8").Value = Range("D7").Value + 1
        Range("D9").Value = Range("D8").Value + 1
 
        ActiveSheet.Shapes("CommandButton1").Delete
        
        Else
        
        Range("D4").Value = ActiveSheet.Previous.Range("D4") + 7
        Range("D5").Value = ActiveSheet.Previous.Range("D5") + 7
        Range("D6").Value = ActiveSheet.Previous.Range("D6") + 7
        Range("D7").Value = ActiveSheet.Previous.Range("D7") + 7
        Range("D8").Value = ActiveSheet.Previous.Range("D8") + 7
        Range("D9").Value = ActiveSheet.Previous.Range("D9") + 7

        ActiveSheet.Shapes("CommandButton1").Delete

        End If
        

    Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
    Unload Me 'closes the userform
    

End Sub