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]

I found when protecting you should have:

ActiveSheet.Protect “password”, true, true

Do I need the true, true?

By deafult it is true, true, but you can put it.

 

[Wickedbane]

Thanks again.

This really helped me out.

 

[DanteAmor]

Thanks again.

This really helped me out.

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