MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with user form


Posted by Paul on October 29, 2001 2:52 AM

Hi: Thanks for taking the time to help I am trying to learn VBA. I have made a user form with the code below, I want to hide sheet 2 so the persons entering data cannot see, or change it, but when I do I get and error because sheet 2 cannot be found. Can the code be changed so I can do this? Any other ideas? Again thanks for your help.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = UserForm1.TextBox1.Value
ActiveCell.Offset(0, 1).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(0, 2).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(0, 3).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(0, 4).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(0, 5).Value = UserForm1.TextBox6.Value
Application.ScreenUpdating = True


Posted by Tom Urtis on October 29, 2001 3:18 AM

Paul,

Here is your code with 2 lines added:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = UserForm1.TextBox1.Value
ActiveCell.Offset(0, 1).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(0, 2).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(0, 3).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(0, 4).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(0, 5).Value = UserForm1.TextBox6.Value
Sheets("Sheet2").Visible = False
Application.ScreenUpdating = True


Notice that lines 3 and 19 are to make your sheet visible before it is selected, and hidden at the conclusion of your procedure.

HTH

Tom Urtis

Posted by Paul on October 29, 2001 6:41 AM

Thanks Tom