Userform to add data to specific cell in a form

[joacro]

Hi there,

Hope someone will be able to assist me

I have a button that will copy one sheet and then create a new sheet with the copied content. Then the userform will also popup
The userform then needs to add the content to the newly created sheet. I don't seem to get it to work

Here is what I have so long

Sub copySheetSameWorkbook()
Dim sheetToCopy As Worksheet
Set sheetToCopy = Worksheets("Sheet1")
sheetToCopy.Copy After:=sheetToCopy
frmInput.Show
End Sub


Private Sub cmdAdd_Click()
With Sheets("sheetToCopy")
.Range("B1").Value = txtFamily.txt
.Range("H2").Value = Me.txtThreshold.txt
.Range("B2").Value = Me.txtM1.txt
.Range("C2").Value = Me.txtM2.txt
.Range("D2").Value = Me.txtM3.txt
.Range("E2").Value = Me.txtM4.txt
.Range("F2").Value = Me.txtM5.txt
frmInput.Close




End Sub

Also is there a way to force the client to rename the worksheet

Regards

 

[dmt32]

Hi,

see if these updates to your codes do what you want

Sub copySheetSameWorkbook()
    Dim sheetToCopy As Worksheet
    Dim SheetName As Variant
    
    Do
    SheetName = InputBox("Enter Sheet Name", "Sheet Name")
'cancel pressed
    If StrPtr(SheetName) = 0 Then Exit Sub
    Loop Until Len(SheetName) > 0
    
    Set sheetToCopy = Worksheets("Sheet1")
    sheetToCopy.Copy After:=sheetToCopy
    ActiveSheet.Name = SheetName
    frmInput.Show
End Sub

UserForm code

Private Sub cmdAdd_Click()
    With ActiveSheet
        .Range("B1").Value = txtFamily.Text
        .Range("H2").Value = Me.txtThreshold.Text
        .Range("B2").Value = Me.txtM1.Text
        .Range("C2").Value = Me.txtM2.Text
        .Range("D2").Value = Me.txtM3.Text
        .Range("E2").Value = Me.txtM4.Text
        .Range("F2").Value = Me.txtM5.Text
    End With
    Unload Me
End Sub

Dave

 

[joacro]

Hi there Dave,

Thank you so much for response

It does create the sheet name and open the userform but it does not add the data to the form

It gives me a runtime 424 error

 

[dmt32]

You don't say where the error occurs but the 424 object required error may be due to one of your userforms control names misspelt in your code.

Check the names in the code actually match the control names.

Dave

 

[joacro]

Hi Dave

It gives me a runtime error on

.Range("B1").Value = txt_Family.Text

I have double checked the spelling is correct

Regards

Olivia

 

[My Aswer Is This]

In Post 1 you have:

.Range("B1").Value = txtFamily.txt

In post 5 you have:

.Range("B1").Value = txt_Family.Text

These are not the same.​

_{^{<strike>
</strike>}}​

 

[joacro]

Thank you,

I found the error. I placed the code on the wrong cmd

I do however have one more question.

If the activesheet.Name = Sheet Name how can I get the sheet name to be automatically entered into the txt_Family.txt

The sheet name will also be the txt_Family.txt

Regards

Olivia

 

[dmt32]

Thank you,


If the activesheet.Name = Sheet Name how can I get the sheet name to be automatically entered into the txt_Family.txt



Olivia

If by automatically you mean when to form is shown, then use UserForm_Initialize event.

example

Private Sub UserForm_Initialize()
    With ActiveSheet
    If .Name = SheetName Then Me.txtFamily.Text = .Name
    End With
End Sub

Dave

 

[joacro]

Thank you for the reply Dave

I don't seem to get it right

The new sheet name should also be the txtFamily name and automatically entered into the userform field
Hope this makes sense

Also how do I go about to print the active sheet