Excel With VBA

How To Hide And Unhide Sheets In Excel With VBA

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]

admin