Hello all, I have a spreadsheet 1

[ts2032]

Hello all,

I have a spreadsheet with some textboxes I have named tb1,tb2.... What I want to do is be able to add the values of the textboxes. I did not put the values into cells as I don't want the values to be printed, just the added value.

something like( I know this is a simple example)

=tb1+tb2+tb3+tb4

thanks in advance,

tsmith

 

[DaleWatson123321]

I would recommend that you do NOT use Text Boxes, as I don't expect Excel will be able to deal with the numbers.

What you should do, is to enter the numbers into cells, and if you don't want them printed, color the numbers "white".

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ts2032]

Dale,

Thank you for your suggestion. I was hoping I would not have to do that as the user has to input these values, but the values themselves are not required for printing only the total. The world will not end tomorrow if the values are printed, I just wanted to see if it could be done through the use of textboxes. Once again, thank you,

tsmith

 

[DaleWatson123321]

tsmith,

Another option, perhaps...

Set up your "Input" sheet as a "separate" sheet, and then on the sheet that you want to print, simply reference those input cells on the Input sheet.

On the Input sheet... If you want to have Text Boxes for displaying Text that the user needs to refer to when entering the numbers, you could have the text boxes "separate" - for example above or below or to the side of the cell(s) where the numbers are to be entered ?

The "easy" way to reference cells anywhere in a workbook, is to first assign "range names" to each cell or range-of cells.

The easy way of Assigning a Range Name:
1) Highlight the cell or range-of-cells
2) Hold down the <Control> key and hit <F3>
3) Type the name
4) Hit <Enter>

Caution: Do NOT create names that will conflict with cell coordinates or with VBA commands. For example, don't create a name like &quot;E14&quot; - instead use &quot;_E14&quot; or &quot;E_14&quot;. Also don't create a name like &quot;Sheet1&quot; - instead use one like &quot;Sheet_1&quot;.

Hope this can work.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ts2032]

Thanks for your help.

the situation is that they enter the data on the sheet that is printed. This is because there are 14 cells, each refer to number of hours worked that day. I can get this data the long way around--there are 26 sometimes 27 pay periods, 14 days and 8 different types of duty hrs for each day-- or have the individual enter the data as they work it. The sheet calls for hours worked in clock time (0700-1500) this data is unfortunately in 1 cell, and is totaled at the bottom. I was going to have the person enter the total duty hours worked for that day into a textbox so that when it printed, the tbox did not print. Now, I think I will just let them enter it into a cell close by.

Thanks once again,
tsmith

 

[DuckBill]

tsmith,

Just another suggestion; if you are having the users enter data into cells on a spreadsheet, but you don't want those cells printed out, why not create a macro to do the printing. You can then arrange that before it prints, the macro either changes the format of the cells (to white or using ;;; custom format) or hides appropriate column(s) or row(s) so that the offending cells &quot;disappear&quot; on the print. After printing, the macro justs resets the sheet.

I use this technique on a very wide spreadsheet to produce a more legible print out; the print macro checks to see which of various important columns have actually been used, and if they haven't, it hides them, prints, then unhides them again. If you switch off screen updating in the macro you don't get any &quot;jumping around&quot; on the screen whilst the macro performs.

Trust this is helpful; if you need any details, let me know.

Regards,

DuckBill

 

[ts2032]

Thanks, I may try that

tsmith

 

[ts2032]

Duckbill,

I used the before_print() event to hide the column. To unhide the column after printing? Now I have the code in the worksheet_selection_change() event. Do you know of a better event? One that is fired after printing?

thanks,

tsmith

 

[nat101]

Why not use a combination of ideas mentioned. You can still go with your original idea of textboxes, do have em reference cells, but whited out. So the tboxes will have the numbers and total but will not be seen on the sheet. (Furthermore you can protect those cells so that they not be inadvertantly entered.)

My $.02
-Nat

 

[wbishopjr]

I have yet another suggestion, set the print area by clicking the print preview button, and then &quot;Page Break Preview&quot; button at the top of the screen. Drag the blue lines to show what you want printed (so that the computed cells do not show up).

Hope this helps
WB

 

[DuckBill]

tsmith

First of all, wbishopjr may have the neatest idea if you can set the print area appropriately; I was assuming that the cells to be hidden were in the middle of the print area so that wasn't an option. If I am right, then going back to my original suggestion of using a macro...

In the example that I cited, I actually put my print macro on a button on the spreadsheet. If you don't want to do this you could try the following:

Public MarkerPrint As Integer

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If MarkerPrint = 0 Then
If ActiveSheet.Name = &quot;Sheet1&quot; Then
Columns(&quot;D:E&quot.Select
Selection.EntireColumn.Hidden = True
End If
MarkerPrint = 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
MarkerPrint = 0
If ActiveSheet.Name = &quot;Sheet1&quot; Then
Columns(&quot;D:E&quot.Select
Selection.EntireColumn.Hidden = False
End If
Range(&quot;A1&quot.Select
Cancel = True
End If
End Sub

This hides columns D and E on the worksheet called &quot;Sheet1&quot; whenever you try to print it using the normal menu commands. The purpose of the MarkerPrint flag is to enable the print command in the middle to function without activating the Cancel at the end of the BeforePrint event (which it also calls).

Hopes this helps,

DuckBill

 

[ts2032]

Duckbill,

Thanks, once again. Yes the column is in the middle of the page. I think your last idea will work fine for me. Your assistance is greatly appreciated.

I don't fully understand the MarkerPrint flag, though.
tsmith

 

[ts2032]

Ok, now I understand. This is good stuff....

I did, however, want to allow the user to have access to the, printer dialog box. In case they need to change the printer. I tried to show the printer dialog box with...

application.dialogs(xldialogprinter).show

but, I guess the beforeprint() event fires when the printpreview button is pressed. So that whenever the user clicks the printpreview button, The printer dialog box pops up. If you click ok--it doesn't print it just opens the preview box. So, I would think there is some way excel tells the difference between when to print and when to preview. If you click print it works fine.

Thanks,

tsmith

 

[DuckBill]

tsmith,

I'm glad that you found my suggestion useful. As for the printer dialog, I have not used the application.dialogs method. But when I use the code as listed and click the printpreview button, then click &quot;print&quot; from the preview, the printer dialog box opens anyway. So the user could change the printer by using that route.

Regards,

DuckBill