Userform as printer interface for a protected workbook.

Good evening. I'm closing in on the final stage of a large project and I've run into a road block. The following line of code is preventing my users from being able to select specific page ranges when using the Print Entire Workbook option in the print menu.

Please Login or Register  to view this content.

I've attached the latest version of my form (with significant support by jaslake) to help explain what I'm attempting here. The attached form has several macros that activate upon open; including a user form. The password for the workbook/worksheets/VBE is "j".

I started last night to explore ways a user form might be able to tackle a few issues in my form and I got myself turned all around. Tonight it was suggested to me that a user form might be able to tackle the printer issue I mentioned above.

So, has anyone ever used a user form as a printer interface and liked the results? Would anyone be willing to steer me in the direction I might go to start coding the user form that would give my users printing options?

Thanks in advance.

Matthew

Hi Matthew

The answer to this is yes, assuming one knows what a DV is and the location of the DV List and the variablse related to the chosen DV (whatever a DV might happen be).

Is it possible to code an OB on the userform to select a specific value from a DV list in a worksheet? BTW...When a value is selected directly from the DV list in the worksheet, that selection prompts other cells to auto-populate with specified values.

Hi Matthew

Here's a "Start" on your Start Button to deal with this

Is it possible to code an OB on the userform to select a specific value from a DV list in a worksheet? BTW...When a value is selected directly from the DV list in the worksheet, that selection prompts other cells to auto-populate with specified values.

NA# will need to be dealt with if neither Option Button 4 nor 6 are True.

Please Login or Register  to view this content.

This is a MAJOR roadblock

print section in the userform to allow the user to print ...duplex

Hi Matthew

I've lost my wireless network and am now hard wired to the cable modem...may be offline for a while until I can get that resolved. Keep truckin'...keep me posted. Get back to you soon as I can.

John,

I hit a wall! What I believed would be a straight forward fix turned out to be more complicated than I could figure out. Here's what I was trying to do:

On Sheet ITP Goals & Obj. we have a button titled "ADD OBJECTIVE INDICATOR". One of my users brought it to my attention that the user needs to be able to select the row where they would like the new Objective Indicator to be placed instead of a new row being added to the bottom of the list of Objective Indicators. I looked at the code for the button labled "ADD OBJ" on the same sheet since this button allows the user to select a specific row where they would like to add a new Obj. so I thought I could adapt this code for my purposes.

Here's the code currently in use for the ADD OBJECTIVE INDICATOR button:

Please Login or Register  to view this content.

Here's the code we currently have in place for the ADD OBJ button:

Please Login or Register  to view this content.

I'll keep working on it in case I can figure it out but if you have a chance to give me a nudge in the right direction that would be great. Thanks.

Matthew

Hi Matthew...glad the training went well. I'm installing a new router...get back to this later in the afternoon.

Hi Matthew

I'm currently having Forum issues. I'm going to work on this issue as I believe it's more complex than the printing issue

the user needs to be able to select the row where they would like the new Objective Indicator to be placed instead of a new row being added to the bottom of the list of Objective Indicators

I have several Links I'll post that I believe will resolve the printing issue.

I hope I don't become "infamous" in Bangor

Hi Matthew

Still having Router and Forum issues. Working on your issue(s).

I don't travel much these days so probably will not get to ME...thanks for the offer. However, should you be in Mid NE Ohio let me know.

Hi Matthew

Try the Code in the attached for this issue

the user needs to be able to select the row where they would like the new Objective Indicator to be placed

Hi Matthew

Try the UserForm Print Code. Please note, I've commented out the Before Print Code in ThisWorkbook Module and added Module Print_Routines.

John,

I've had a little time to try out the new code. I won't be able to tell exactly how the print controls work until I'm back in the office and can actually print but from the looks of it things are looking good for a resolution of our print issues. I'll let you know what I find on Monday.

I'm running into a hiccup with the Show Toolbar and Hide Toolbar command buttons. After printing "All" from the userform my Show and Hide Toolbar buttons do not work until I manually click Esc. Is this the nature of the beast or can this be helped? Thanks.

Matthew

Hi Matthew

In Module 9 comment out the two indicated lines of Code...let me know

Please Login or Register  to view this content.

John,

That took care of Show/Hide Toolbar button issue but I need to do some research to determine why we added the "SendKeys..." line of code in the first place to make sure that commenting it out doesn't affect the reason we originally added it.

BTW, with the newest lines of code added, is it intended for my users to just use the userform to print or will they be able to print via the userform and the regular print options offered by Excel? The reason I'm asking, I attempted to print a select number of pages (for instance 1-3) using Print Entire Workbook and my buttons in the form disappeared.

Thanks.

Matthew

Hi Matthew

As I previously mentioned

Please note, I've commented out the Before Print Code in ThisWorkbook Module and added Module Print_Routines

It's my opinion you can have either the UserForm Print routines or the native Excel Routines. If a User should use the native Excel Routines by mistake you'll need to provide them with a method to reinstate the Buttons. Since the Buttons have disappeared the Code would have to be called by a Key Board Shortcut.

For example, assign Refresh_Buttons to the Keyboard Shortcut CTRL + r. Using that combination of Keyboard strokes will run the Refresh_Buttons Code.

Let me know how you wish to proceed...

Edit: We added the Send Keys line of Code to fix this exact issue. In playing with the issue I think I found if you don't have this line of Code

Please Login or Register  to view this content.

you don't need this line of Code

' SendKeys "{ESC}", True '<----Comment out this line of Code

Hi Matthew

Let me do a bit of research...perhaps we can not allow the User to print from Native Excel (if that's acceptable). It'll be later today...off to Grandson's Soccer games.

Hi Matthew

For what purpose??? What's the issue???

trigger our Refresh Macro when any cell that's unlocked is clicked

Originally Posted by jaslake

For what purpose??? What's the issue???

My thought was that this might allow the user to print either through the userform interface or natively.

Here's how I envision it.....

1) the user prints natively
2) after print cell E9 on Demographics is always selected and once selected maybe we could set the code to automatically call the Refresh_Buttons macro


Maybe this wouldn't work. I'll do some digging and see what I can find. Thanks. Talk soon.

Matthew

Hi Matthew

If you wish to force the User to only print from the UserForm, add this Code to ThisWorkbook Module

Please Login or Register  to view this content.

Replace the Print_Routines Module Code with this

Please Login or Register  to view this content.

Hi Matthew

This could probably be implemented but the Code will be called ANYTIME the Target Cell is clicked. I'd not recommend it.

Please Login or Register  to view this content.

Hi Matthew

To do this

how can i have the checkboxes in the print portion of the userform uncheck after the print button is clicked

Add this Code to the bottom of Private Sub Print_ITP_Click()

Please Login or Register  to view this content.

To do this

stop the pop up box from appearing when my user prints a single worksheet from the userform

Replace the Private Sub Workbook_BeforePrint Code with this

Please Login or Register  to view this content.

and Replace the Print_Routines Module Code with this

Please Login or Register  to view this content.

John,

I just added the code you posted above. I accidentally selected ALL and Demographics at the same time (out of the menu in the userform) and I got a run time error 9 "Subscript out of range". Not sure what this is about.

Also, now when I print entire workbook from the native print menu I don't see the little popup box in the middle of the screen that quickly appears as the pages are printed. Should I see that popup box still? Thanks.

Matthew

Hi Matthew

I have this fixed

I accidentally selected ALL and Demographics at the same time (out of the menu in the UserForm) and I got a run time error 9 "Subscript out of range"

The native Excel 2010 print mode, to me, is squirrelly. If you ONLY want to print the ENTIRE Workbook it works fine.

You CANNOT print selected Worksheets ( eg: 1,3,5) in native Excel 2010...it's simply not an option...requires Code.

Supposedly you CAN print sequential Worksheets in native Excel 2010 (eg: 1-3) but I can't make it work on my platform.

So, I need you to tell me where you wish to go with this. Please let me know what functionality you wish based on the above.

All functionality is available through the UserForm. Only Entire Workbook, on my platform, is available in native Excel.

Let me know.

I'm having terrible response time with the Forum...perhaps router related...don't know...frustrating.

Edit: I'll be happy to do this...first define functionality you require.

explain about the flow of the code and/or how it's all connected

Hi Matthew

The Code in the attached has been modified and annotated as requested. The Code allows the User to print from Native Excel or from the User Form. This Code below checks to see where the Print Request came from, the User Form or Native Excel. If the Request came from the User Form the Code allows the Print_Routine Code to run. If the Request came from Native Excel the Code runs from the Print Dialog and reminds the User to reset The Buttons (CTRL + r).

Please Login or Register  to view this content.

The User Form Print Code has been modified also such that if ALL is selected it ignores any Individual Sheets that have been selected. This avoids the Error you reported

run time error 9 "Subscript out of range"

Please Login or Register  to view this content.

And finally, the Print Routines have been modified. Print All is essentially the same. Print Selection now numbers the Pages.

Any questions, let me know...

Please Login or Register  to view this content.