I'd like to put a control into a VBA form that would allow the user to select
an input file. I can do that with the common dialog control, or the
GetOpenFilename method.

But, a variety of other inputs are required, and sending the user to another
interface seems clunky.

Is there a good control for using right in a VBA form?

Thanks,
Mark

Why not just a commandbutton with a caption of Open.



mark wrote:
>
> I'd like to put a control into a VBA form that would allow the user to select
> an input file. I can do that with the common dialog control, or the
> GetOpenFilename method.
>
> But, a variety of other inputs are required, and sending the user to another
> interface seems clunky.
>
> Is there a good control for using right in a VBA form?
>
> Thanks,
> Mark

--

Dave Peterson

"Open" the file?

The further details are that there are a list of inputs that the user must
give, for different choices of what project to run the program (a comparison
of procurement info) on. At first, the system was the brain child of one
person, to be used by one department. As such, it was developed to use
sheets within the file itself, to take the input. Procurement could put in
the info once, and then just pick the sheet that described their run time
choices.

But after the system was mostly done, the Operations department got wind of
it, and the expanded system now does similar things for them.

But, the concern is that since the input is taken from sheets within the
Excel file that is accessible to everybody ("everybody" used to be just one
person), the input data/constraints/item change history is vunlerable to
other people deleting someone else's input info.

So, if the same info that is now on sheets within one file, were in seperate
files controlled by the multple users, they could each have their own input
files, without having it be subject to the deletion of another.

But I'm not sure how an "Open" command button would do what I'm looking for?
At best, it would take you to something else which then asked you, "Which
file is your input file?" Which leads to the multiple user interfaces that I
mentioned before. I can do it, but I was hoping for a better way, within the
"clean-ness" of one user interface.

"Dave Peterson" wrote:

> Why not just a commandbutton with a caption of Open.
>
>
>
> mark wrote:
> >
> > I'd like to put a control into a VBA form that would allow the user to select
> > an input file. I can do that with the common dialog control, or the
> > GetOpenFilename method.
> >
> > But, a variety of other inputs are required, and sending the user to another
> > interface seems clunky.
> >
> > Is there a good control for using right in a VBA form?
> >
> > Thanks,
> > Mark
>
> --
>
> Dave Peterson
>

I think I didn't explain well how the current system works.

the current system has a button on the first sheet, which opens the VBA form.

withink the VBA form, there is a listbox which lists the name of every other
sheet in the file.

The user selects which of those sheets is the report that he/she currently
wants to use as the input for their current report run.

Maybe you could have the users open their own file before they show the
userform. Then just update the activesheet.

It would be up to the user to make sure that they've opened the correct workbook
and activated the correct worksheet.

But you could check to make sure that the activesheet looked right--you could
check to see that the headers are correct before doing any processing.

But I'm not sure I understand the problem with having a button that will ask the
user to select the correct workbook (via an open button that uses
application.getopenfilename) and then allows the user to select the correct
worksheet.

mark wrote:
>
> "Open" the file?
>
> The further details are that there are a list of inputs that the user must
> give, for different choices of what project to run the program (a comparison
> of procurement info) on. At first, the system was the brain child of one
> person, to be used by one department. As such, it was developed to use
> sheets within the file itself, to take the input. Procurement could put in
> the info once, and then just pick the sheet that described their run time
> choices.
>
> But after the system was mostly done, the Operations department got wind of
> it, and the expanded system now does similar things for them.
>
> But, the concern is that since the input is taken from sheets within the
> Excel file that is accessible to everybody ("everybody" used to be just one
> person), the input data/constraints/item change history is vunlerable to
> other people deleting someone else's input info.
>
> So, if the same info that is now on sheets within one file, were in seperate
> files controlled by the multple users, they could each have their own input
> files, without having it be subject to the deletion of another.
>
> But I'm not sure how an "Open" command button would do what I'm looking for?
> At best, it would take you to something else which then asked you, "Which
> file is your input file?" Which leads to the multiple user interfaces that I
> mentioned before. I can do it, but I was hoping for a better way, within the
> "clean-ness" of one user interface.
>
> "Dave Peterson" wrote:
>
> > Why not just a commandbutton with a caption of Open.
> >
> >
> >
> > mark wrote:
> > >
> > > I'd like to put a control into a VBA form that would allow the user to select
> > > an input file. I can do that with the common dialog control, or the
> > > GetOpenFilename method.
> > >
> > > But, a variety of other inputs are required, and sending the user to another
> > > interface seems clunky.
> > >
> > > Is there a good control for using right in a VBA form?
> > >
> > > Thanks,
> > > Mark
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks for the idea. Yeah, I could do that. In fact, on several
applications, I have. If a workbook is open, it uses that as the input. If
no other workbook is open, it asks the user to select their input workbook.

Maybe it's just my concept of change in this area that's out of whack. This
whole thing started almost 10 months ago, and I had the first demo/cut done
pretty quickly... Think maybe it's just my head that's gotten too attached to
the existing form.

But anyway, after the sheet with the input parameters is selected, the rest
of the coding change is easy... the code uses 5 range variables which define
where the inputs are. It's written so that one set of inputs can kick of
multiple loops and in the end compare the whole bunch, if that's what the
user wants.

But anyway, changing the input is easy... just change the assignments on the
controlling range variables.

Thanks for the continued suggestion.