FORM - multiple questions

[itr674]

I have a form and on it I have shaded areas where I have folks input data.

This form can be printed on either regular bond paper (when the form is not available in the field), or a standard purchased form.

I need a macro that:

Form is area B6:C8 for this example...

1. when BUTTON 1 is pressed it will print all data on form but not the shaded color, it can print the text, but not the color.

2. when BUTTON 2 is pressed it would print only the text in the shaded area but nothing else, no borders, no headings, no color, just the "consignee" information.

3. BUTTON 3 whem pressed would make a copy of the sheet and put it at the end. The copy would need to have all the buttons copied too.

I tried doing this with the macro recorder but it kept putting the sheet after a particular sheet and not at the end and it would not copy the buttons.

Book1
	A	B	C	D	E	F	G	H	I	J
2
3		BUTTON1			BUTTON2			BUTTON3
4		PrintonReqularPaper			PrintonForm			CopythisForm
5
6		Consignee	test1
7			address1
8			state1
9
Sheet1

 

[Nimrod]

This will copy to the active sheet to the end of book..

Sub CopySheet()
ActiveSheet.Copy After:=Sheets(Sheets.Count)
End Sub

 

[Smitty]

For 1 & 2 why not record yourself removing the formatting & printing? Then add the formatting back. You can do the recording from another workbook and close the original after recording (without saving changes).

Hope that helps,

Smitty

 

[itr674]

Nimrod - thanks for "sheets.count"

PennySaver - I played with that but thought there might be a better way--maybe not--will work with that some more today...

 

[itr674]

How do you do an UNDO in a macro. I am using the macro recorder to work this form out and I was using the UNDO function to put everything back inplace after the print action--the problem is that is appears the macro recorder is doesn't write that command????

 

[Nimrod]

How about using different hidden sheets for the different formats instead of changing sheet ?

For example you push your command button it :
Copies data to hidden sheet called FormatType1, the same macro then prints out that particular sheet .

The following code copies from the active sheet , to a hidden sheet called "NOFormat" and then prints out that sheet. NOTE: You need to create the sheet called "NOFormat" before running this code

Public Sub PrintNoFormat()
On Error GoTo TheEnd
Application.ScreenUpdating = False
With Sheets("NOFormat")
.Visible = xlSheetVisible
.Range("B6:C8").Value = ActiveSheet.Range("B6:C8").Value
.PrintOut Copies:=1, Collate:=True
.Visible = xlSheetHidden
End With
TheEnd:
Application.ScreenUpdating = False
End Sub

 

[itr674]

Nimrod - I think that might work, thanks, will get on it tomorrow...

 

[itr674]

Nimrod - that works good. Have another question though.

We have to put XXXs over two seperate boxes on this form, so how would I take them to the hidden form so they could be printed.

I made the XXXs in Paint so they are objects in the file. There are two XXX objects on a sheet and we just move one over the applicable cell that needs to be Xed out.