Change cell value and print via range list

[Wickedbane]

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.

 

[DanteAmor]

Try this to print All

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

 

[Wickedbane]

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?

 

[DanteAmor]

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

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

 

[Wickedbane]

That did it. Thanks!

 

[DanteAmor]

That did it. Thanks!

I'm glad to help you. Thanks for the feedback.

 

[Wickedbane]

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?

 

[Wickedbane]

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?

 

[DanteAmor]

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

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

 

[Wickedbane]

I found when protecting you should have:

ActiveSheet.Protect “password”, true, true

Do I need the true, true?