Solved: How do I?

[wildpianist]

Hello again

I have a spreadsheet with 7 Books (days of the week)

What I want to achieve is having a user form (opened in a completely different spreadsheet altogether) reading the details from say A1:A12 from Book1 (eg being Monday)from the spreadsheet with all the data in and then displaying it on the userform on the new spreadsheet as maybe a label or just some text on the userform

How would I go about doing that? If at all possible?

Cheers

[Bob Phillips]

You have a book (or a spreadsheet) with sheets, but not a spreadsheet with books. And you don't have userforms on spreadsheets. SO what exactly do you mean?

Can't you post the workbook.

[wildpianist]

Ok.

The 1.xls file has 6 tabs Monday to Sunday

On my 2.xls file I have a macro that will load up UserForm1 that dependant on what option on drop down box I have selected it will display A1:A12 of that specific day from 1.xls

[Bob Phillips]

[vba]

Private Sub ListBox1_Click()
With Me
.TextBox1.Text = Workbooks("2.xls").Worksheets(.ListBox1.Value).Range("A1")
.TextBox2.Text = Workbooks("2.xls").Worksheets(.ListBox1.Value).Range("A2")
.TextBox3.Text = Workbooks("2.xls").Worksheets(.ListBox1.Value).Range("A3")
'etc.
End With
End Sub
[/vba]

A workbook would have helped it saves me tons of work.

[wildpianist]

Sorry xld

I'll remember next time I'm at work and I do not have access to my Excel here its pretty naff!

I keep asking them to give me internet access but they wont budge!

Thank you again

[wildpianist]

Hmmm still having problems. Nothing is showing.

I'll post a Workbook for you

[wildpianist]

1.xls contains the data for 3 people, Joe, Timmy and William

and what they are doing on monday and tuesday at specific times of the day.

[wildpianist]

2.xls contains the userform where the data from 1.xls will be shown.

What I want to achieve is that when the name of the specific person is selected from combobox1, and the day from combobox2, it brings up, say for Joe's case, range B4:K4 and populates it in a text box or on a label pretty much displaying what that person is doing that day, and only that person.

Only problem I have is that 1.xls will be closed and I'll need 2.xls to look through the data from 1.xls when it is closed

Any help would be greatly appreciated

[Bob Phillips]

I added some more textboxes to test it

[vba]

Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex <> -1 And _
Me.ComboBox2.ListIndex <> -1 Then
Call UpdateDetails
End If
End Sub

Private Sub ComboBox2_Change()
If Me.ComboBox1.ListIndex <> -1 And _
Me.ComboBox2.ListIndex <> -1 Then
Call UpdateDetails
End If
End Sub
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Joe Bloggs"
.AddItem "Timmy Mallett"
.AddItem "William Tell"
End With
With ComboBox2
.AddItem "Monday"
.AddItem "Tuesday"
'etc
End With


End Sub

Private Sub UpdateDetails()
Dim iRow As Long

With Me

On Error Resume Next
iRow = Application.Match(.ComboBox1.Value, Worksheets(.ComboBox2.Value).Columns(2), 0)
On Error GoTo 0
If iRow > 0 Then
TextBox1.Text = Workbooks("1.xls").Worksheets(.ComboBox2.Value).Range("B" & iRow)
.TextBox2.Text = Workbooks("1.xls").Worksheets(.ComboBox2.Value).Range("C" & iRow)
.TextBox3.Text = Workbooks("1.xls").Worksheets(.ComboBox2.Value).Range("D" & iRow)
.TextBox4.Text = Workbooks("1.xls").Worksheets(.ComboBox2.Value).Range("E" & iRow)
'etc.
End If
End With

End Sub

Private Sub UserForm_Click()

End Sub
[/vba]

[wildpianist]

Bloomin' Heck!

Thankyou so much! What a lot of code

[wildpianist]

What am I doing wrong? Its not populating any of the boxes

[wildpianist]

Ahhhhhhhh!

Something showed something then.

Basically I had to be in 1.xls then open 2.xls's VBE and run the UserForm then it populated it all.

Is there a way for it to populate this code when only 2.xls open and it accesses the file 1.xls that isn't actually open at all then populate it, then when i click close on 2.xls's UserForm1 it then closes both files?

[Bob Phillips]

You could use ADO to access a closed 1.xls (even more code), or just open it if it isn't open.

My suggestion is the latter, and if you are going to update 1.xls, I wouldn't even think about ADO.

[mdmackillop]

Hi WildPianist
Please use meaningful (searchable) titles when you post questions.
Regards
MD

[wildpianist]

Sorry mdmackillop Couldn't think of a title, i'll try harder next time

With that code - is there a way if say Monday - Friday's data is in one Sheet and the userform has 5 lines of text boxes so when you click someones name from the combo it will display the weeks worth in one UserForm.

I'll post another workbook

[wildpianist]

Pretty self explanatory what i'm trying to achieve

Just trying to show the whole week on one UserForm.

I'm sure its pretty similar to the other code.

Thanks again

[Bob Phillips]

That is unworkable.

You need to structure the textbox names, something that uses the day, and the time position, like

txtMon1, txtMon2,

txtTue1, txtTue2,

etc.

then come back

[wildpianist]

I found that out when I worked with the first version, so many TextBoxes

Thanks for helping brb

[wildpianist]

here he is :P

[Bob Phillips]

Your textbox names still need some work

[vba]

Private Sub ComboBox1_Change()
Dim i As Long, j As Long
Dim iRow As Long

With Me

On Error Resume Next
iRow = Application.Match(.ComboBox1.Value, Worksheets("Data").Columns(2), 0) - 3
On Error GoTo 0
If iRow > 0 Then
For i = 2 To 62 Step 15
For j = 1 To 25
.Controls("txt" & Left$(Worksheets("Data").Cells(i, "B").Value, 3) & j).Text = _
Worksheets("Data").Cells(i + iRow + 1, 2 + j)
Next j
Next i
End If
End With
End Sub

Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Joe Bloggs"
.AddItem "Timmy Mallett"
.AddItem "William Tell"
End With
End Sub
[/vba]