CODEWITHSUNDEEP
vbaexceluserform
Gary Carroll
Gary Carroll

Reputation: 71

How to Loop through several worksheets in a Workbook Using a VBA UserForm

Just updated My data so you could see. It wroks for 1 worksheet since I am referencing that worksheet. Still having a hard to figuring out how to reference the whole work book so it will look in all 31 worksheets for the name. I am also haveing trouble figuring out how to go to the next record if the Patient is in there more than once a year. Any help is greatly appriciated.

Okay, I am having an issue looping through all the worksheets in my workbook. Goals I am trying to accomplish.

  1. Search for a record by Patients name. Once found all cells in that record will be imported into the usrform.
  2. be able to edit any information that I need to and it be saved back into the same record.

I found a Youtube video on how to to do it through a worksheet but not the entire workbook. Also this code will be going on an existing userform. It will need a function that it will allow me to select the next time the Patient shows up. So there could be multiple entries of that patient. Would like to be able to choose the year and Patient as the criteria to search.

      Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim found As Range
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("2012").Range("A" & row_number)

        If item_in_review = Patients_Name.Text Then
            Date_of_Incident.Text = Sheets("2012").Range("B" & row_number)
            Month.Text = Sheets("2012").Range("C" & row_number)
            Year.Text = Sheets("2012").Range("D" & row_number)
        End If

Loop Until item_in_review = ""

End Sub

Upvotes: 3

Views: 1060

Answers (1)

bamblack
bamblack

Reputation: 3779

To loop through all the worksheets in a workbook

Option Explicit
Dim ws As Worksheet

For Each ws in ThisWorkbook.Sheets
    '' do stuff with ws here
Next ws

There are also a few things you could improve about your code...

Sub TestStuff()

Dim ws              As Worksheet
Dim rng             As Range
Dim found           As Range
Dim firstAddress    As String

For Each ws In ThisWorkbook.Sheets
    '' set the range you want to search in
    Set rng = ws.Range("D1:D" & ws.Range("D" & ws.Rows.Count).End(xlUp).Row)
    '' see if it contain's the patient's name (can make this case insensitive)
    Set found = rng.Find("Patient's Name Here", SearchDirection:=xlNext)

    '' if it found something
    If Not found Is Nothing Then
        firstAddress = found.address
        '' loop until we hit the first cell again
        Do
            '' set textbox values
            Date_Of_Incident.Text = found.Offset(,-3).Value
            Month_Of_Incident.Text = found.Offset(,-2).Value
            Year_Of_Incident.Text = found.Offset(,-1).Value

            Set found = rng.Find("Patient's Name Here", SearchDirection:=xlNext, After:=found)
        Loop While Not found Is Nothing And found.address <> firstAddress
    End If

Next ws

End Sub

As a note, if there's multiple entries in the spreadsheet for one patient this will find all of them, but only the information for the last one will be displayed.

If you want my two cents, I would use a ListBox that lists the date of all of the appointments the patient has, that way you can see "Oh this user has had 4 appointments, and this is the one I want to look at." You then click on the ListBox entry you want, and it has some information stored in it that says, this ListBox entry corresponds to this entry in the worksheet. It then pulls that info from the worksheet and populates the UserForm. Again, just my two cents based on what I've read.

After discussing in chat, the final code was this:

Option Explicit
Private Sub AddWithValue(Text As String, Value As String)

    lbxAppointments.AddItem Text
    lbxAppointments.List(lbxAppointments.ListCount - 1, 1) = Value

End Sub


Private Sub btnSearch_Click()

    Dim ws          As Worksheet
    Dim search      As Range
    Dim found       As Range
    Dim patient     As String
    Dim lbxValue    As String
    Dim firstFind   As String

    lbxAppointments.Clear

    patient = tbxPatientName.Text

    For Each ws In ThisWorkbook.Sheets
        '' define our search range (Column A)
        Set search = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
        '' search that range for the patient's name
        Set found = search.Find(patient, SearchDirection:=xlNext)

        '' test if we found anything
        If Not found Is Nothing Then
            firstFind = found.Address

            Do
                '' found something, add it to the text box
                lbxValue = "'" & found.Parent.Name & "'!" & found.Address(External:=False)
                AddWithValue found.Offset(, 1).Value, lbxValue

                Set found = search.Find(patient, SearchDirection:=xlNext, After:=found)
            Loop While Not found Is Nothing And found.Address <> firstFind
        End If
    Next ws

End Sub

Private Sub lbxAppointments_Change()

    Dim rng     As Range

    With lbxAppointments
        If .ListIndex <> -1 Then
            Set rng = Range(.List(.ListIndex, 1))
            '' now get all of the offsets of it here and you can populate textbox controls with the info
            '' rng.Offset(,1) = Column B
            '' rng.Offset(,2) = Column C
            '' rng.Offset(,3) = Column D, so on and so forth
        End If
    End With

End Sub

Upvotes: 5

Related Questions