Have Userform read info from another sheet.

Hi everyone,

I have two sheets, the first is "Form" & the second is "Tracking". The "Form" sheet contains one button that, when clicked, brings up a userform. The userform has 11 checkboxes & two buttons ("View Next" & "View Previous"). The "Tracking" sheet has 11 columns (A - K), which will contain either "True" or "False".

I need the checkboxes in the userform to pull the values from the "Tracking" sheet. For example, the "Tracking" sheet has the following information listed:

A1 = True
B1 = True
C1 = True
D1 = False
E1 = True
F1 = False
G1 = False
H1 = True
I1 = False
J1 = True
K1 = True

Therefore, the checkboxes in the userform should be displayed as :

01 = Checked
02 = Checked
03 = Checked
04 = Empty
05 = Checked
06 = Empty
07 = Empty
08 = Checked
09 = Empty
10 = Checked
11 = Checked

"True" = a ticked checkbox & "False" = an empty checkbox.

What I also need, is that when the "View Next" button is clicked in the userform, the checkboxes change according to the new data. For example, when the userform is opened, it should default to the first row of data in the "Tracking" sheet, but when the "View Next" button is clicked, it should read from the second row of data, & so forth. The "View Previous" button should do the reverse, basically go from the second row to the first, or the fifth row to the fourth, & so on.

I've attached an example of my worksheet to this post. Please note that this example is a minor section taken out of a much larger workbook that I'm working on, however I can't post everything due to having company (that I work for) information in the full copy; thus, I do apologize if the example worksheet is a bit rough.

Any ideas

Thanks,

- I Love Lagar -

You can use the form.initialze event to assign controls initial values.
Then by using a public variable you can keep track of the column you are currently on. See your example I have posted with code added for details.

Here's an alternative that uses a procedure within the userform to load the checkboxes, this saves repeating the code. Also, by using a consistent naming for the checkboxes, the original didn't, the code uses a loop to load them, reducing the lines of code. It also enables/disables the navigation buttons dependant on the row selected

Dim Rw         As Long
Dim i          As Long
'View Next Button

Private Sub cmdViewNext2_Click()
    Rw = Rw + 1
    Me.cmdViewPrev2.Enabled = True
    If Rw = Sheets("Tracking").Cells(Rows.Count, 1).End(xlUp).Row Then Me.cmdViewNext2.Enabled = False
    loadBoxes

End Sub

'View Previous Button

Private Sub cmdViewPrev2_Click()
    Rw = Rw - 1
    Me.cmdViewNext2.Enabled = True
    If Rw = 1 Then
        Me.cmdViewPrev2.Enabled = False
    End If
    loadBoxes
End Sub

Private Sub UserForm_Initialize()
    Rw = 1
    loadBoxes
    Me.cmdViewPrev2.Enabled = False
End Sub

Sub loadBoxes()
    With Sheets("Tracking")
              For i = 1 To 11
            Me("cbo" & i).Value = .Cells(Rw, i).Value
        Next i
    End With
End Sub

See example in later post

Sorry about that, the way you had your second sheet set up made me think you were going by column. I didn't look closely enough. Here is a second example that goes by row. You can see the changes I made, basically just switching the coding to go by row instead of column.

I was getting strange results so i added a line to test it, then forgot to delete that line. Use this amended code

Sub loadBoxes()
    With Sheets("Tracking")
        For i = 1 To 11
            Me("cbo" & i).Value = .Cells(Rw, i).Value
        Next i
    End With
End Sub

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save