UserForm referring to another excel file
 

I currently use PERSONAL.xls to hold all of my macros and my one user
form. Every day I open a file exported from excel (titled
ListingsMM-DD-YYYY.xls). I use a macro from PERSONAL to format
everything on the Listings file. I'm now trying to implement new things
that use another macro to in PERSONAL to start the userform, gather
what variables the user wants to graph (a list of what they can pick is
in a sheet called wksInfo- names of their ranges have already been
created- "Letter" and "Yaxis"), and then to graph certain things from
another worksheet in the Listings file based off of the user's input.
I would only start the user form when I already have the Listings file
opened.

I think I just need somehow to make this user form inside of my
PERSONAL.xls refer to the Listings file correctly. I've incorrectly put
"Files("Listings11-5-2005.xls")." in to try to fix it and even
hard-coded the date in to see if it worked- nothing. I have been unable
to find much relevant info about my problem, but I'm sure that visual
basic can manipulate multiple files somehow. Is there some way I could
make it refer to the Listings file I have open or the most recent
Listings file in a certain folder?


Here's where my form's code is messing up:

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim cYok As Range


For Each cLoc In
Files("Listings11-5-2005.xls").Sheets("wksInfo").Range("Letter")
With Me.letter
.AddItem cLoc.Value
.List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
End With
Next cLoc

For Each cYok In
Files("Listings11-5-2005.xls").Sheets("wksInfo").Range("Yaxis")
With Me.yAxis
.AddItem cYok.Value
.List(.ListCount - 1, 1) = cYok.Offset(0, 1).Value
End With
Next cYok

Me.letter.SetFocus

End Sub



I'm running Excel 2002- I'm still pretty new at all of this visual
basic/excel/macro stuff, so thanks for your help.

Regards,
Brett

UserForm referring to another excel file
 

Use.....Workbooks not Files to refer to an open workbook (file name) ....

Workbooks("Listings11-5-2005.xls").Sheets("wksInfo").Range("Letter")


--
Cheers
Nigel



"B Smith" wrote in message
oups.com...
I currently use PERSONAL.xls to hold all of my macros and my one user
form. Every day I open a file exported from excel (titled
ListingsMM-DD-YYYY.xls). I use a macro from PERSONAL to format
everything on the Listings file. I'm now trying to implement new things
that use another macro to in PERSONAL to start the userform, gather
what variables the user wants to graph (a list of what they can pick is
in a sheet called wksInfo- names of their ranges have already been
created- "Letter" and "Yaxis"), and then to graph certain things from
another worksheet in the Listings file based off of the user's input.
I would only start the user form when I already have the Listings file
opened.

I think I just need somehow to make this user form inside of my
PERSONAL.xls refer to the Listings file correctly. I've incorrectly put
"Files("Listings11-5-2005.xls")." in to try to fix it and even
hard-coded the date in to see if it worked- nothing. I have been unable
to find much relevant info about my problem, but I'm sure that visual
basic can manipulate multiple files somehow. Is there some way I could
make it refer to the Listings file I have open or the most recent
Listings file in a certain folder?


Here's where my form's code is messing up:

Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim cYok As Range


For Each cLoc In
Files("Listings11-5-2005.xls").Sheets("wksInfo").Range("Letter")
With Me.letter
.AddItem cLoc.Value
.List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
End With
Next cLoc

For Each cYok In
Files("Listings11-5-2005.xls").Sheets("wksInfo").Range("Yaxis")
With Me.yAxis
.AddItem cYok.Value
.List(.ListCount - 1, 1) = cYok.Offset(0, 1).Value
End With
Next cYok

Me.letter.SetFocus

End Sub



I'm running Excel 2002- I'm still pretty new at all of this visual
basic/excel/macro stuff, so thanks for your help.

Regards,
Brett