How to use Excel While Running VBA Forms

[miken127]

I have an Excel VBA project application which is designed around User Forms for data entry and internal calculation, with some help from various worksheets in the workbook. There is a need for users to be able to access Excel while the User Form is active. This doesn't appear to be possible from within VBA. Am I missing anything?

 

[Zathras]

Beginning with Excel 2000 it is possible to show modeless forms. Excel 97 and earlier only support modal forms.

 

[bryanbayfield]

If you are in Excel 97, you might be able to get around this.

Why do the users need to access the spreadsheet? Is it to get a reference (if so, you can use a RefEdit control)?

If not, maybe you could add a button to the form which would hide the form/ insert a command button on the worksheet. Once the user has finished faffing about in Excel, they can press the command button which would tehn delete and the user form would then show itself again.

 

[miken127]

I should have mentioned that this is Excel 2000 and that users are asking to be able to access other Excel workbooks while using this application. In hind sight it would have been better to create the application as a stand-alone program, but many months have gone into this project, too much to just scrap.

I'm not aware of modal versus modeless forms. Can you expand on that a bit?

 

[tbl]

I'm probably totally missing the point with this, but any form from Excel 2000 onwards can be displayed as non-modal so that it floats above all other applications.

UserForm1.Show vbModeless instead of the more common UserForm1.Show
It leads to all sorts of problems if you try to display a combination of modeless and modal - sometimes it works and sometimes it doesn't - just stick to one modeless form and close it before using any other form.


Richard

 

[miken127]

I'm probably confusing this issue too much. The basic problem is that once I have a form displayed and allow the user to modify it as necessary, the printing function avaialble in a user form is only printform. This sends the output to the current default printer. What my user wants to do is send it to a .pdf using the pdfwriter add-in. I can get him to the button by hiding the form, but I can't make the form print to .pdf without going to the Printer Folder and changing the default to the pdf driver. There doesn't seem to be a way to get to a different printer through Excel for the form. The user complains that the work around of going out and changing the default printer and then having to change it back is too burdensome.

 

[Zathras]

Why can't you just record the steps required (using Excel's macro recorder) and then put that code as the first part of the action on the command button you are using to do the printing?

 

[miken127]

I tried that. But the macro recorder does not capture action outside of Excel.

 

[Zathras]

I'm not familiar with the pdfwriter add-in. I would suggest at this point that you start a new thread with the specific question of how to use VBA code to set properties for the pdfwriter add-in.