Run a macro from info on another sheet

[austenr]

Currently I have a a macro and the information it reads from on the same worksheet. What I want to happen is that I want to move the information to another sheet (sheet2) and leave the macro on sheet1. What do I have to modify in the code to accomplish this? File attached.

Perhaps I need to post the code...

Private Sub ListBox1_Click()
    With ActiveSheet.Range("B:B")
    Set c = .Find(ListBox1, LookIn:=xlValues)
    If Not c Is Nothing Then
        TextBox1 = c.Offset(0, 0).Text
        TextBox2 = c.Offset(1, 0).Text
        TextBox3 = c.Offset(2, 0).Text
        TextBox4 = c.Offset(3, 0).Text
        TextBox5 = c.Offset(4, 0).Text
    End If
End With
End Sub

Private Sub UserForm_Initialize()
    For Each cel In Intersect(Range("Listing"), ActiveSheet.UsedRange)
        If cel.Text = "Group:" Then
           ListBox1.AddItem cel.Offset(0, 1).Text
        End If
    Next
End Sub

[Zack Barresse]

Hi Austen,


Without looking at your download, just at your code, you could adapt it as such ...

Private Sub ListBox1_Click()
    Dim ws As Worksheet
    Set ws = Worksheet("Sheet2")
    With ws.Range("B:B") 
        Set c = .Find(ListBox1, LookIn:=xlValues) 
        If Not c Is Nothing Then 
            TextBox1.Value = c.Offset(0, 0).Text 
            TextBox2.Value = c.Offset(1, 0).Text 
            TextBox3.Value = c.Offset(2, 0).Text 
            TextBox4.Value = c.Offset(3, 0).Text 
            TextBox5.Value = c.Offset(4, 0).Text 
        End If 
    End With 
End Sub 
 
Private Sub UserForm_Initialize() 
    Dim ws As Worksheet
    Set ws = Worksheet("Sheet2")
    For Each cel In Intersect(ws.Range("Listing"), ws.UsedRange) 
        If cel.Text = "Group:" Then 
            ListBox1.AddItem cel.Offset(0, 1).Text 
        End If 
    Next 
End Sub

[austenr]

Thanks Zock. I was all around it. That clears things up a little.