Text in a cell visable on screen but BUT doesn't get printed

[T.I.M.]

[Fixed]
How can I show the 1st 3 rows off the ,,first'' worksheet, on each new worksheet?

So it's some sort of Header?
[Fixed]


And another question:
How can I enter text in a cell that I can see on screen but doesn't get printed?
I don't like that comment thing in Excel, because you only see the/that comment when the corresponding cell is active.



great thanks,
Tim

 

[Scott Huish]

Re: Text in a cell visable on screen but BUT doesn't get pri

If you don't mind running it from a macro instead, this might work:

Sub nocomment()
Rows("2:3").Font.Color = vbWhite
Application.Dialogs(xlDialogPrint).Show
Rows("2:3").Font.Color = vbBlack
End Sub

 

[Chas17]

Re: Text in a cell visable on screen but BUT doesn't get pri

Tim,
You may want to try using a "Text box" from either the Forms toolbar or the controls toolbar. ( If you use Controls, these are activex and you have to turn off design mode when you are done.)
Rather than put comments in Rows 2, 3, place the comments in a text box, righ click text box and uncheck the print object checkbox. A lable from the Forms toolbar will also do this.
Also, you can show comments all the time by selecting Tools-Options-View tab, select "Comment&Indicator" to show al the time. You would have to place this in code to turn it on for others using the sheet.
In workbook open:
Application.DisplayCommentIndicator = xlCommentIndicatorOnly

In worbook close:
Application.DisplayCommentIndicator = xlNoIndicator

Chas

 

[T.I.M.]

Re: Text in a cell visable on screen but BUT doesn't get pri

@Chas17
Yeah That Label Thing Works...Nice!
that other thing, "Text box" can't get that one to work, can't get any text in it :S
I can't align this label? Horizontal and Vertical???
I merged a2 to f3 (it's 1 big cel now) and I would like to align the ,,comment'' text in the middle/center.


Great! and Thank you very much,
Tim



@HotPepper
It doesn't work? it doesn't do anything at all?
nor in Worksheet , nor in Workbook.

kind regards,
Tim

 

[Scott Huish]

Re: Text in a cell visable on screen but BUT doesn't get pri

When you hit ALT-F8 and run the nocomment macro, it doesn't do anything?

 

[Chas17]

Re: Text in a cell visable on screen but BUT doesn't get pri

Try the Text box from the Drawing Toolbar. Right Click it.

 

[T.I.M.]

Re: Text in a cell visable on screen but BUT doesn't get pri

@HotPepper

It works but I would like to run it automatically:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Rows("2:3").Font.Color = vbWhite
Application.Dialogs(xlDialogPrint).Show
Rows("2:3").Font.Color = vbBlack
End Sub

Opens the print diaglog again when I maunual choose Print to screen / print.
...As expected, so I have to do it otherwhise:

Works NICE!
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Rows("2:3").Font.Color = vbWhite

NOW...isn't there something like:
Application.Dialogs(xlDialogPrint).Close????????
Rows("2:3").Font.Color = vbBlack

End Sub

 

[T.I.M.]

Re: Text in a cell visable on screen but BUT doesn't get pri

@Chas17

Sorry Chas17...there was ,,just'' something wrong with my Excel,
 I got it to get to work and very nice, BUT one thing I don't seem to get to work and that is how to get Multiline in the text box?????

I put Multiline on true in the properties, but nothing happens???
I want to ,,Enter'' (Break) put 2 Lines in the text box.

 

[T.I.M.]

Re: Text in a cell visable on screen but BUT doesn't get pri

I Figured it out:

Properties -> EnterKeyBehavior: True


Kan I protect the Text box, I mean when I click (When not in design modus) on it I can change the content, is there an option to prevent this?



greetings,
Tim

 

[T.I.M.]

Text Box and Formulas and Protection

Kan I protect the Text box, I mean when I click (When not in design modus) on it, nothing happens? and/or can't change the content, is there an option to prevent this?

Is it possible to use formulas in the text box?
so I could for example use  =SUM(A1+B1)   ?



greetings,
Tim

 

[Scott Huish]

Re: Text in a cell visable on screen but BUT doesn't get pri

Is it possible to use formulas in the text box?
so I could for example use =SUM(A1+B1) ?

You could do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$B$1" Then
myrange = ActiveSheet.Range("A1:B1")
TextBox1.Text = Application.WorksheetFunction.Sum(myrange)
End If
End Sub