FORM - multiple questions

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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
ABCDEFGHIJ
2
3BUTTON1BUTTON2BUTTON3
4PrintonReqularPaperPrintonFormCopythisForm
5
6Consigneetest1
7address1
8state1
9
Sheet1
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
This will copy to the active sheet to the end of book..

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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
Nimrod - I think that might work, thanks, will get on it tomorrow...
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,686
Members
425,230
Latest member
DzOus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top