PDA

View Full Version : [SOLVED] Run a macro from info on another sheet



austenr
04-19-2005, 10:09 AM
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
04-19-2005, 10:52 AM
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
04-19-2005, 01:45 PM
Thanks Zock. I was all around it. That clears things up a little.