Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Change cell value and print via range list
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change cell value and print via range list

    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.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mxico
    Posts
    4,508
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Change cell value and print via range list

    Try this to print All

    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))
            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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Dec 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change cell value and print via range list

    Your code works. It cycles through the orders and prints, but I doesnt stop at a blank cell. It keeps going. How can I change the code to tell it to stop at a blank cell?

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mxico
    Posts
    4,508
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Change cell value and print via range list

    Quote Originally Posted by Wickedbane View Post
    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))
            if c.value = "" then exit for
            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
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Dec 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change cell value and print via range list

    That did it. Thanks!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mxico
    Posts
    4,508
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Change cell value and print via range list

    Quote Originally Posted by Wickedbane View Post
    That did it. Thanks!
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Dec 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change cell value and print via range list

    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?

  8. #8
    New Member
    Join Date
    Dec 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change cell value and print via range list

    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?

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mxico
    Posts
    4,508
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Change cell value and print via range list

    Quote Originally Posted by Wickedbane View Post
    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 "abc"
        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 "abc"
        Sheets("accessory form").Visible = False
        Sheets("directory").Select
    End Sub
    Regards Dante Amor

  10. #10
    New Member
    Join Date
    Dec 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change cell value and print via range list

    I found when protecting you should have:

    ActiveSheet.Protect password, true, true

    Do I need the true, true?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •