Change cell value and print via range list

Wickedbane

New Member
Joined
Dec 18, 2014
Messages
9
So I have been having a mental block when trying to figure out how to have excel do what I want/need it to do.

Basically I have a main sheet called “directory”. It accesses all other sheets via buttons. On this sheet I have a drop down list of order numbers based off the day selected. When the user selects a order number it fills in the order form on “accessory form” sheet. They can hit a button to take them to the “accessory form” sheet to view the order form or just print it. It works well for a few orders.

My current macro to print the selected order form.

Sub Printform
Sheets(“directory”).select
Sheets(“accessory form”).visible=true
Sheets(“accessory form”).Select
Activewindow.selectedsheets.printout copies:2, collate:=true, _
IgnorePrintArea=false
Sheets(“accessory form”).visible=false
Sheets(“directory”).select
Range(“n6”).select
End sub

When we get busy we could end up over 200 orders a day. Selecting each order number to print the order form can get old quick.

Id like to use vba to go through my list of order numbers and change the order number on the form and print.

On the accessory form the order number goes into cell L3. Based off L3 all other fields gets filled in with index matching order number. L3 is a merged cell L3:N3.

I have the order list starting at E2 on the accessory form sheet. Its a list that changes from day to day. 1 day can have 1 order and another can have 200. The list will pull all orders per users day selection.

I think to accomplish this task it would have to be in a loop. I dont have much experience with vba.

So in my head its like.
L3=E2
Print, then
L3=E3
Print, then
L3=E4
Print, etc
Till it finds a blank cell in column E.

Need to print 2 copies per order.

Need code to access the “accessory form” sheet which is hidden and run the code to print the order forms then hide “accessory form” sheet and return to the “directory” sheet when done.

Any help on this would be greatly appreciated.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try this to print All

Code:
Sub [COLOR=#0000ff]Printform_All[/COLOR]()
    Dim c As Range
    Sheets("directory").Select
    Sheets("accessory form").Visible = True
    Sheets("accessory form").Select
    For Each c In Range("E2", Range("E" & Rows.Count).End(xlUp))
        Range("L3").Value = c.Value
        ActiveWindow.SelectedSheets.PrintOut copies:=2, collate:=True, IgnorePrintArea:=False
    Next
    Sheets("accessory form").Visible = False
    Sheets("directory").Select
End Sub
 
Upvote 0
Your code works. It cycles through the orders and prints, but I doesn’t stop at a blank cell. It keeps going. How can I change the code to tell it to stop at a blank cell?
 
Upvote 0
Your code works. It cycles through the orders and prints, but I doesn’t stop at a blank cell. It keeps going. How can I change the code to tell it to stop at a blank cell?

Try this

Code:
Sub Printform_All()
    Dim c As Range
    Sheets("directory").Select
    Sheets("accessory form").Visible = True
    Sheets("accessory form").Select
    For Each c In Range("E2", Range("E" & Rows.Count).End(xlUp))
        [COLOR=#0000ff]if c.value = "" then exit for[/COLOR]
        Range("L3").Value = c.Value
        ActiveWindow.SelectedSheets.PrintOut copies:=2, collate:=True, IgnorePrintArea:=False
    Next
    Sheets("accessory form").Visible = False
    Sheets("directory").Select
End Sub
 
Upvote 0
I got a new issue. The sheet is normally protected. How to unprotected it when it has a password to print the forms then re-protect with password?
 
Upvote 0
I was able to find a answer.

Sub Printform_All()
Dim c As Range
Sheets("directory").Select
Sheets("accessory form").Visible = True
Sheets("accessory form").Select
ActiveSheet.Unprotect “password”
For Each c In Range("E2", Range("E" & Rows.Count).End(xlUp))
if c.value = "" then exit for
Range("L3").Value = c.Value
ActiveWindow.SelectedSheets.PrintOut copies:=2, collate:=True, IgnorePrintArea:=False
Next
ActiveSheet.Protect “password”,true,true
Sheets("accessory form").Visible = False
Sheets("directory").Select
End Sub

Seems to work. Would there be a better way?
 
Upvote 0
I got a new issue. The sheet is normally protected. How to unprotected it when it has a password to print the forms then re-protect with password?

Change "abc" by the password
Code:
Sub Printform_All()
    Dim c As Range
    Sheets("directory").Select
    Sheets("accessory form").Visible = True
    Sheets("accessory form").Select
activesheet.unprotect "[COLOR=#ff0000]abc[/COLOR]"
    For Each c In Range("E2", Range("E" & Rows.Count).End(xlUp))
        if c.value = "" then exit for
        Range("L3").Value = c.Value
        ActiveWindow.SelectedSheets.PrintOut copies:=2, collate:=True, IgnorePrintArea:=False
    Next
activesheet.protect "[COLOR=#ff0000]abc[/COLOR]"
    Sheets("accessory form").Visible = False
    Sheets("directory").Select
End Sub
 
Upvote 0
I found when protecting you should have:

ActiveSheet.Protect “password”, true, true

Do I need the true, true?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top