In this post I’m going to show you how to hide and unhide sheets in excel with vba. We are also going to load
the data from the active sheet into the text boxes of the UserForm.
Essentially, we have 5 worksheets with the same layout. It’s just that each patient has their own spreadsheet.
Then we will load all of the data on the selected worksheet into the text boxes. The UserForm is launched in the previous image.
Here is the code to load all the worksheets:
Private Sub UserForm_Initialize() LoadBoxes End Sub Sub LoadBoxes() Dim intCounter As Integer 'load the combo with the sheet names With Me.cboWorsheets For intCounter = 1 To ActiveWorkbook.Sheets.Count .AddItem ActiveWorkbook.Sheets(intCounter).Name Next intCounter End With End Sub
When a worksheet is selected in the combobox, and “Go” is clicked, the textboxes are filled in, and the other sheets are hidden (take a look at the code)
Private Sub btnGo_Click() Dim strSheetName As String strSheetName = Me.cboWorsheets.Value 'show all the worksheets first For intCounter = 1 To ActiveWorkbook.Sheets.Count ActiveWorkbook.Sheets(intCounter).Visible = True Next intCounter 'Hide the worksheets not selected in the combo For intCounter = 1 To ActiveWorkbook.Sheets.Count If ActiveWorkbook.Sheets(intCounter).Name <> strSheetName Then ActiveWorkbook.Sheets(intCounter).Visible = False End If Next intCounter 'Load the values in the textboxes Me.txtName = ActiveWorkbook.Sheets(strSheetName).Range("B1") Me.txtAddress = ActiveWorkbook.Sheets(strSheetName).Range("B2") Me.txtPmt1 = ActiveWorkbook.Sheets(strSheetName).Range("B3") Me.txtPmt1Date = ActiveWorkbook.Sheets(strSheetName).Range("B4") Me.txtPmt2 = ActiveWorkbook.Sheets(strSheetName).Range("B5") Me.txtPmt2Date = ActiveWorkbook.Sheets(strSheetName).Range("B6") 'show the "Start" worksheet always For intCounter = 1 To ActiveWorkbook.Sheets.Count If ActiveWorkbook.Sheets(intCounter).Name = "Start" Then ActiveWorkbook.Sheets(intCounter).Visible = True End If Next intCounter End Sub
This is what happens when “Ana Trujillo” is selected:
Let me know if you have any questions.
Use this form to send me a message:
[simple_contact_form]