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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,854
Office Version
2007
Platform
Windows
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
 

Wickedbane

New Member
Joined
Dec 18, 2014
Messages
9
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,854
Office Version
2007
Platform
Windows
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
 

Wickedbane

New Member
Joined
Dec 18, 2014
Messages
9
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

New Member
Joined
Dec 18, 2014
Messages
9
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,854
Office Version
2007
Platform
Windows
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
 

Wickedbane

New Member
Joined
Dec 18, 2014
Messages
9
I found when protecting you should have:

ActiveSheet.Protect “password”, true, true

Do I need the true, true?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,755
Messages
5,470,584
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top