October 18, 2014

Automaticcally Hide Workbook View User Form

Is there a way to hide the workbook in the background so people only see the user form? This is a question asked by many website visitors in different formats. What is the user trying to do?

  • The user wants to create a user-form
  • He wants to transfer data from the user-form to the Excel worksheet
  • During the process of data entry into the user-form and transfer of data to the Excel worksheet he doesn’t want the data entry operator to be able to view the worksheet or workbook
  • He wants to close the file after the data entry is over
  • Before the file closes it should be saved with a specific file-name

How do we solve this problem quickly and easily?

We code the workbook when it opens using the following code. The VBA code makes the workbook invisible and displays only the user-form:

Private Sub Workbook_Open()
Application.Visible = False
End Sub

The submit button helps the user to transfer the data to the next available blank row. No alerts are displayed while the data entry and transfer of data is performed to have a perfect automation without any user intervention. The VBA code given below finds the next blank row, transfers the data from the user-form to the Excel worksheet, clears the text-boxes to prepare them for more entries and saves the data with a specific file name. The data is automatically saved to the default folder ‘My Documents’. We can of course code in such a way that the data is saved to a specific folder by defining a folder path.

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Text
Cells(erow, 2) = TextBox2.Text
TextBox1.Text = “”
TextBox2.Text = “”
ActiveWorkbook.SaveAs “userformdata”
Application.DisplayAlerts = True
End Sub

The close button makes the workbook visible so that we can confirm the correctness of our operations. You may adopt another strategy if you don’t wish to display the workbook at all. The VBA code for the close command button is given below:

Private Sub CommandButton2_Click()
Application.Visible = True
Unload Me
End Sub

Watch the training video below to learn how to hide a workbook and display only the user-form:

View the video on YouTube.

One thought on “Automaticcally Hide Workbook View User Form

  1. how to print 01worksheet out of five hided worksheet with command button =vba code may be provided

Comments are closed.