my program runs from a userform and uses one workbook. there is a link
on the userform, however, that can open another workbook. if this
other workbook is opened, the userform will interact with that workbook
since it is the active one. I need the original workbook to be
activated before manipulating the form again. I was able to solve this
using the following code in every section that referred to the original
workbook:

Dim WB1 As Workbook
Set WB1 = Workbooks("<filename>.xls")
WB1.Activate

I then realized that I have a save as button on the userform and the
workbook will often be saved with a different name rendering the above
code useless, or worse. is there a way around this? thanks.

The problem may be that you are using a constant for the filename. Once the
file has been save with a new name then:

Dim s As String
s = Selection.Worksheet.Parent.Name

should represent the latest filename. Then:

Set WB1 = Workbooks(s)
--
Gary's Student


"Jacob" wrote:

> my program runs from a userform and uses one workbook. there is a link
> on the userform, however, that can open another workbook. if this
> other workbook is opened, the userform will interact with that workbook
> since it is the active one. I need the original workbook to be
> activated before manipulating the form again. I was able to solve this
> using the following code in every section that referred to the original
> workbook:
>
> Dim WB1 As Workbook
> Set WB1 = Workbooks("<filename>.xls")
> WB1.Activate
>
> I then realized that I have a save as button on the userform and the
> workbook will often be saved with a different name rendering the above
> code useless, or worse. is there a way around this? thanks.
>
>

thanks for your help. that should work, but now i am realizing that
there are a lot of other actions that need the original workbook to be
active. it seems silly to place this everywhere where there is
interaction. It seems that it would be best to some how activate the
workbook whenever that particular userform is being used. is there a
way to do this?



On Nov 9, 12:25 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> The problem may be that you are using a constant for the filename. Once the
> file has been save with a new name then:
>
> Dim s As String
> s = Selection.Worksheet.Parent.Name
>
> should represent the latest filename. Then:
>
> Set WB1 = Workbooks(s)
> --
> Gary's Student
>
> "Jacob" wrote:
> > my program runs from a userform and uses one workbook. there is a link
> > on the userform, however, that can open another workbook. if this
> > other workbook is opened, the userform will interact with that workbook
> > since it is the active one. I need the original workbook to be
> > activated before manipulating the form again. I was able to solve this
> > using the following code in every section that referred to the original
> > workbook:
>
> > Dim WB1 As Workbook
> > Set WB1 = Workbooks("<filename>.xls")
> > WB1.Activate
>
> > I then realized that I have a save as button on the userform and the
> > workbook will often be saved with a different name rendering the above
> > code useless, or worse. is there a way around this? thanks.

I suppose a better question would be: is there a way to associate the
workbook with the userform and all its contents and actions regardless
of the file name?


On Nov 9, 1:57 pm, "Jacob" <jlong...@gmail.com> wrote:
> thanks for your help. that should work, but now i am realizing that
> there are a lot of other actions that need the original workbook to be
> active. it seems silly to place this everywhere where there is
> interaction. It seems that it would be best to some how activate the
> workbook whenever that particular userform is being used. is there a
> way to do this?
>
> On Nov 9, 12:25 pm, Gary''s Student
>
> <GarysStud...@discussions.microsoft.com> wrote:
> > The problem may be that you are using a constant for the filename. Once the
> > file has been save with a new name then:
>
> > Dim s As String
> > s = Selection.Worksheet.Parent.Name
>
> > should represent the latest filename. Then:
>
> > Set WB1 = Workbooks(s)
> > --
> > Gary's Student
>
> > "Jacob" wrote:
> > > my program runs from a userform and uses one workbook. there is a link
> > > on the userform, however, that can open another workbook. if this
> > > other workbook is opened, the userform will interact with that workbook
> > > since it is the active one. I need the original workbook to be
> > > activated before manipulating the form again. I was able to solve this
> > > using the following code in every section that referred to the original
> > > workbook:
>
> > > Dim WB1 As Workbook
> > > Set WB1 = Workbooks("<filename>.xls")
> > > WB1.Activate
>
> > > I then realized that I have a save as button on the userform and the
> > > workbook will often be saved with a different name rendering the above
> > > code useless, or worse. is there a way around this? thanks.

I don't know.

If I need to refer to both an original workbook and the current workbook, I
create two Public variables for the references and initialize them with the
workbook open event. I don't know if this will save you any time, however.
--
Gary's Student


"Jacob" wrote:

> I suppose a better question would be: is there a way to associate the
> workbook with the userform and all its contents and actions regardless
> of the file name?
>
>
> On Nov 9, 1:57 pm, "Jacob" <jlong...@gmail.com> wrote:
> > thanks for your help. that should work, but now i am realizing that
> > there are a lot of other actions that need the original workbook to be
> > active. it seems silly to place this everywhere where there is
> > interaction. It seems that it would be best to some how activate the
> > workbook whenever that particular userform is being used. is there a
> > way to do this?
> >
> > On Nov 9, 12:25 pm, Gary''s Student
> >
> > <GarysStud...@discussions.microsoft.com> wrote:
> > > The problem may be that you are using a constant for the filename. Once the
> > > file has been save with a new name then:
> >
> > > Dim s As String
> > > s = Selection.Worksheet.Parent.Name
> >
> > > should represent the latest filename. Then:
> >
> > > Set WB1 = Workbooks(s)
> > > --
> > > Gary's Student
> >
> > > "Jacob" wrote:
> > > > my program runs from a userform and uses one workbook. there is a link
> > > > on the userform, however, that can open another workbook. if this
> > > > other workbook is opened, the userform will interact with that workbook
> > > > since it is the active one. I need the original workbook to be
> > > > activated before manipulating the form again. I was able to solve this
> > > > using the following code in every section that referred to the original
> > > > workbook:
> >
> > > > Dim WB1 As Workbook
> > > > Set WB1 = Workbooks("<filename>.xls")
> > > > WB1.Activate
> >
> > > > I then realized that I have a save as button on the userform and the
> > > > workbook will often be saved with a different name rendering the above
> > > > code useless, or worse. is there a way around this? thanks.
>
>

Thanks for your help. I'm not sure if that is what I need though. I
started a new thread after doing a little brainstorming. I still don't
know the best way to go about this.

http://groups-beta.google.com/group/...353d9a9ca0faf5


On Nov 9, 3:43 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> I don't know.
>
> If I need to refer to both an original workbook and the current workbook, I
> create two Public variables for the references and initialize them with the
> workbook open event. I don't know if this will save you any time, however.
> --
> Gary's Student
>
> "Jacob" wrote:
> > I suppose a better question would be: is there a way to associate the
> > workbook with the userform and all its contents and actions regardless
> > of the file name?
>
> > On Nov 9, 1:57 pm, "Jacob" <jlong...@gmail.com> wrote:
> > > thanks for your help. that should work, but now i am realizing that
> > > there are a lot of other actions that need the original workbook to be
> > > active. it seems silly to place this everywhere where there is
> > > interaction. It seems that it would be best to some how activate the
> > > workbook whenever that particular userform is being used. is there a
> > > way to do this?
>
> > > On Nov 9, 12:25 pm, Gary''s Student
>
> > > <GarysStud...@discussions.microsoft.com> wrote:
> > > > The problem may be that you are using a constant for the filename. Once the
> > > > file has been save with a new name then:
>
> > > > Dim s As String
> > > > s = Selection.Worksheet.Parent.Name
>
> > > > should represent the latest filename. Then:
>
> > > > Set WB1 = Workbooks(s)
> > > > --
> > > > Gary's Student
>
> > > > "Jacob" wrote:
> > > > > my program runs from a userform and uses one workbook. there is a link
> > > > > on the userform, however, that can open another workbook. if this
> > > > > other workbook is opened, the userform will interact with that workbook
> > > > > since it is the active one. I need the original workbook to be
> > > > > activated before manipulating the form again. I was able to solve this
> > > > > using the following code in every section that referred to the original
> > > > > workbook:
>
> > > > > Dim WB1 As Workbook
> > > > > Set WB1 = Workbooks("<filename>.xls")
> > > > > WB1.Activate
>
> > > > > I then realized that I have a save as button on the userform and the
> > > > > workbook will often be saved with a different name rendering the above
> > > > > code useless, or worse. is there a way around this? thanks.