Excel 2013 Userform macro issues. I am trying to get this user form I created to

Question

Excel 2013 Userform macro issues.

I am trying to get this user form I created to start on row 5 in column A. I have tried changing some of the code and it keeps out puting text to row 1 column A.

What this code is to do is that once the information is entered into the user box the person clicks next and it prints the information from the user box to the spread sheet starting on row 5 column A. It is also to take the information entered in on A5, B5, and C5 and open a new worksheet and print the same information to the new sheet in the following order A1 is equal to A5 from sheet1 but shows up with this on sheet2 ="*"&Sheet1!A5&"*", A2 is equal to B5 from sheet1 but shows up with this on sheet2 ="*"&Sheet1!B5&"*", A3 is equal to C5 from sheet1 but shows up with this on sheet2 ="*"&Sheet1!C5&"*". Once the user clicks on the next button it the user box clears out then starts all over again but this time enetering the info on row 6 and opening a new worksheet. So the amount of worksheets opened is depending on user. Also I have a print button that is suppose to print out every open sheet not counting the first main sheet.

I am new to programming and trying to figure out how to do all of this in vba I have found some coding to help me get to certain points but am getting confused. Thanks for the help I am including a copy of the Excel sheet in this post.

Test excel file

Thanks again.

This is my line of code.

Private Sub NPButton_Click()

Dim info As Long

'Make Main sheet active
Sheet1.Activate

'Determine info
info = WorksheetFunction.CountA(Range("5:5")) + 1

'Transfer information
Cells(info, 1).Value = LName.Value
Cells(info, 2).Value = FName.Value
Cells(info, 3).Value = DOB.Value
Dim Ctl As Control

For Each Ctl In Me.Controlss
    If TypeName(Ctl) = "TextBox" Then Ctl.Text = "**"
Next Ctl

End Subbb!!

Explanation / Answer

This is only for you to try, juge if its better then yours.

Take a copy of your workbook and replace the macro in the Userform with this one.

'Private Sub UserForm_Initialize() 'This macro is not used.

'Empty Last Name Box
'LName.Value = ""

'Empty First Name Box
'FName.Value = ""

' Empty Date of Birth Box
'DOB.Value = ""

'Set Focus on LNameBox
'LName.SetFocus

'End Sub
Private Sub NPButton_Click()
Dim info As Long

'Make DATA active
'Data.Activate

'Determine info
info = Cells(Rows.Count, 1).End(xlUp).Row + 1

'Transfer information
Cells(info, 1).Value = LName.Value
Cells(info, 2).Value = FName.Value
Cells(info, 3).Value = DOB.Value
LName.Value = ""
FName.Value = ""
DOB.Value = ""
LName.SetFocus

'For Each Ctl In Me.Controls  'not used
'    If TypeName(Ctl) = "TextBox" Then Ctl.Text = "**" 'Not used
'Next Ctl 'Not used

End Sub
Private Sub CloseButton_Click()
    'close the form (itself)))
Unload Me  
    'Me.Hide ' This command does not cclose the form, it only hide it.
End Subb!!

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.