Open new excel and keep existing data

Printable View

12-22-2005, 06:15 AM
debauch

Open new excel and keep existing data

I have implemented a vba form at work, that requires people to input necessary information. There have been a few complaints about the form, and I want people to continue using it, becasue it really saves our dept. time.

My Question is, can I have the vba code, open and run the form in a *new excel if they already have excel open with a current worksheet. Most people here need to refer to their worksheets to get the info, but I currently have the excel application hidden, until they have finished their request.

Make sense ?
12-22-2005, 06:33 AM
Bob Phillips

Quote:

Originally Posted by debauch

I have implemented a vba form at work, that requires people to input necessary information. There have been a few complaints about the form, and I want people to continue using it, becasue it really saves our dept. time.

My Question is, can I have the vba code, open and run the form in a *new excel if they already have excel open with a current worksheet. Most people here need to refer to their worksheets to get the info, but I currently have the excel application hidden, until they have finished their request.

Make sense ?

Why not just not hide Excel?
12-22-2005, 06:37 AM
debauch

I am. And when they close the form, the previous worksheet comes back up for them. What they are complaining about is, all the information they need, are on excel sheets, but the vba form, hides it all while they are filling out this form. Then it sends us the info via email.

If I don't use the vba form, they only send us a request via email, and they don't include all info needed.
12-22-2005, 07:08 AM
Bob Phillips

Quote:

Originally Posted by debauch

I am. And when they close the form, the previous worksheet comes back up for them. What they are complaining about is, all the information they need, are on excel sheets, but the vba form, hides it all while they are filling out this form. Then it sends us the info via email.

If I don't use the vba form, they only send us a request via email, and they don't include all info needed.

No, I said don't hide Excel (albeit in a clumsy way). What benefit do you get by hiding it?
12-22-2005, 07:12 AM
debauch

sorry, my mistake. I could, but then they can only see a blank white sheet. The vba form inputs onto the blank white form, and lotus grabs it. // If they were able to switch sheets while putting info in the form, then it would get confused where to grab the info wouldn't it ? oh man , im so confused right now. i thoguht i had this thing tweaked just right. i would post the file, but i think it is above size limit
12-22-2005, 07:22 AM
debauch

Ok, here is the form , with a password of 'login9', could you take a look, and see if it is possible, when opening, to open an entirely new excel application, & running the code in the second excel, while keeping existing worksheets open to view.
12-22-2005, 07:23 AM
Bob Phillips

Quote:

Originally Posted by debauch

sorry, my mistake. I could, but then they can only see a blank white sheet. The vba form inputs onto the blank white form, and lotus grabs it. // If they were able to switch sheets while putting info in the form, then it would get confused where to grab the info wouldn't it ? oh man , im so confused right now. i thoguht i had this thing tweaked just right. i would post the file, but i think it is above size limit

To answer the question rather than confuse you, you can create another instance of Excel with

Set newApp = CreateObject("Excel.Application")

but your code doesn't exist in there, and if you open a workbook in there with code, how do you invoke it (maybe Workbook_Open).

Do you have VB, where you could create a application driving the form that talks to your Excel instance?
12-22-2005, 07:27 AM
debauch

I would love to have this as a stand alone application in vb, however I am restricted to use vba/excel, as it runs against our work policy.

The 'set new app....' looks like it will work well. I will try that out and see what happens.
12-22-2005, 09:38 AM
Bob Phillips

Quote:

Originally Posted by debauch

I would love to have this as a stand alone application in vb, however I am restricted to use vba/excel, as it runs against our work policy.

Not arguing with you, but that seems a bit odd as VB would be a free-standing executable, of which you must already have many. I assume it is not an anti-VB policy, otherwise why permit VBA?
12-22-2005, 01:08 PM
debauch

My work only allows what is already installed on the machines. Any new additions is not permitted. Since we already have excel, I am able to do vba apps.

Im not arguing either, I really wish I could do it that way, it would be much easier.
12-22-2005, 03:38 PM
johnske

Quote:

Originally Posted by debauch

I am. And when they close the form, the previous worksheet comes back up for them. What they are complaining about is, all the information they need, are on excel sheets, but the vba form, hides it all while they are filling out this form. Then it sends us the info via email.

If I don't use the vba form, they only send us a request via email, and they don't include all info needed.

Why not show the workbook, have a Workbook_Open event to display the Userform with "Userform.Show False" (so they still have access to the details on the worksheet, disable the X and code it so the userform can only be unloaded when all the details on the form are completed? (all pretty straight-forward)