PDA

View Full Version : Solved: How do I?



wildpianist
10-31-2007, 07:18 AM
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
10-31-2007, 07:22 AM
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
10-31-2007, 07:26 AM
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
10-31-2007, 07:35 AM
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


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

wildpianist
10-31-2007, 07:38 AM
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
10-31-2007, 04:02 PM
Hmmm still having problems. Nothing is showing.

I'll post a Workbook for you :) :yay

wildpianist
10-31-2007, 04:18 PM
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
10-31-2007, 04:24 PM
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 :bow:

Bob Phillips
10-31-2007, 05:00 PM
I added some more textboxes to test it



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

wildpianist
10-31-2007, 05:11 PM
Bloomin' Heck!

Thankyou so much! What a lot of code :)

wildpianist
10-31-2007, 05:21 PM
What am I doing wrong? Its not populating any of the boxes :(

wildpianist
10-31-2007, 05:31 PM
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
11-01-2007, 01:14 AM
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
11-01-2007, 01:23 AM
Hi WildPianist
Please use meaningful (searchable) titles when you post questions.
Regards
MD

wildpianist
11-01-2007, 02:58 PM
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
11-01-2007, 03:44 PM
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
11-01-2007, 03:51 PM
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
11-01-2007, 04:06 PM
I found that out when I worked with the first version, so many TextBoxes

Thanks for helping :) brb

wildpianist
11-01-2007, 04:29 PM
here he is :P

Bob Phillips
11-01-2007, 05:11 PM
Your textbox names still need some work



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

wildpianist
11-01-2007, 06:52 PM
I get an object not found error.

What is the code actually looking for, I assume it has something to do with the names of my txtboxes?

Bob Phillips
11-02-2007, 01:37 AM
Because your textboxes still need some work. Some are named txtMon#, some TextMon#. Sort it out.

wildpianist
11-02-2007, 06:35 AM
What a fool I am. Changing the box names for 125 boxes is a bit hard work lol
:banghead:
Thank you xld

:beerchug:

Bob Phillips
11-02-2007, 09:20 AM
I know, that is why I pushed it back at you.

wildpianist
11-02-2007, 11:25 AM
lol thank you :P

wildpianist
11-02-2007, 11:43 AM
Sorry xld,

I've changed all the boxes to txtMon1 etc

but i'm still getting an object not found error,

also I only went up to 13:30 as an example is it easy to go up to 20:00 and amend the code?

I'll upload the sheet again.

I'm sure its me doing something wrong again!

Bob Phillips
11-02-2007, 01:36 PM
It is.

It is not my job to do your basic checking for you.

Look at the name for what should be txtMon12. There may be others.

wildpianist
11-02-2007, 02:28 PM
:banghead:

Maybe I've just had a long day.

I'm still checking and I'm still stuck!

It's probably blatently obvious!
I'm sorry for being a nause!

wildpianist
11-02-2007, 03:05 PM
Yep!

Its me, for some reason or other I messed some of the code up too somehow (however doesn't surprise me!)

Changed all the txtBoxes, and re copied the code and it works like a charm

You my friend are an absolute genius


:friends:

Would have worked first time if I wasn't such an idiot, but you live and learn! I'll remember to check my names of things lol