|
Printing Next Row
Good Morning
First time post so if I get it all wrong please allow a little leeway. We have a UserForm that reads a Result Sheet on filling the Text Boxes and clicking a CommandButton it the enters the info on a sheet named Card - we then print the resulting card at the moment we have to do each one individually by selecting a number from 1-100 from a drop down Combo Box is it possible after selecting 1 from the drop down it will then continue to find the next row down and print all the rows on to an individual card. The snippets of code below are I suspect very simple to all you clever people out there but this is we do it at the moment and its very time consuming. Private Sub Add1_Click() Dim varAnswer As String varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?", vbYesNo, "Print Warning") If varAnswer = vbNo Then Exit Sub End If Dim X As Integer X = InputBox("Number Of Copies Required ? Enter In Box Below", "Print") If X 0 Then Sheets("Card").Select ActiveWindow.SelectedSheets.PrintOut Copies:=X, Collate:=True End If End Sub Private Sub Cmb10_Click() Dim startrownum As Integer Dim endrownum As Integer startrownum = 2 endrownum = 102 UserForm1.Tb52.Value = (Sheets("Result").Range("A" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb53.Value = (Sheets("Result").Range("B" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb54.Value = (Sheets("Result").Range("C" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb55.Value = (Sheets("Result").Range("D" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb56.Value = (Sheets("Result").Range("E" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.TB57.Value = (Sheets("Result").Range("F" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb58.Value = (Sheets("Result").Range("G" & Trim(Str(Cmb10.ListIndex + startrownum)))) End Sub Private Sub Add11_Click() Worksheets("Card").Range("I4:P4").Value = UserForm1.Tb56.Value Worksheets("Card").Range("B6:W6").Value = UserForm1.Tb53.Value Worksheets("Card").Range("E10:I10").Value = UserForm1.Tb52.Value Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = UserForm1.Tb54.Value Worksheets("Card").Range("M15:O15").Value = UserForm1.Tb55.Value Worksheets("Card").Range("J17:N17").Value = UserForm1.TB57.Value Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = UserForm1.Tb58.Value End Sub |
Printing Next Row
This assumes that when the value is changed for cmb10, it updates the Card
sheet with the appropriate data. Add a button Add2: Private Sub Add2_Click() Dim varAnswer As String varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?", vbYesNo, "Print Warning") If varAnswer = vbNo Then Exit Sub End If Dim i As Long for i = 0 to cmb10.Listcount - 1 cmb10.ListIndex = i Sheets("Card").PrintOut Copies:=1, Collate:=True Next End Sub -- Regards, Tom Ogilvy "Many thanks Sue" wrote: Good Morning First time post so if I get it all wrong please allow a little leeway. We have a UserForm that reads a Result Sheet on filling the Text Boxes and clicking a CommandButton it the enters the info on a sheet named Card - we then print the resulting card at the moment we have to do each one individually by selecting a number from 1-100 from a drop down Combo Box is it possible after selecting 1 from the drop down it will then continue to find the next row down and print all the rows on to an individual card. The snippets of code below are I suspect very simple to all you clever people out there but this is we do it at the moment and its very time consuming. Private Sub Add1_Click() Dim varAnswer As String varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?", vbYesNo, "Print Warning") If varAnswer = vbNo Then Exit Sub End If Dim X As Integer X = InputBox("Number Of Copies Required ? Enter In Box Below", "Print") If X 0 Then Sheets("Card").Select ActiveWindow.SelectedSheets.PrintOut Copies:=X, Collate:=True End If End Sub Private Sub Cmb10_Click() Dim startrownum As Integer Dim endrownum As Integer startrownum = 2 endrownum = 102 UserForm1.Tb52.Value = (Sheets("Result").Range("A" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb53.Value = (Sheets("Result").Range("B" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb54.Value = (Sheets("Result").Range("C" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb55.Value = (Sheets("Result").Range("D" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb56.Value = (Sheets("Result").Range("E" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.TB57.Value = (Sheets("Result").Range("F" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb58.Value = (Sheets("Result").Range("G" & Trim(Str(Cmb10.ListIndex + startrownum)))) End Sub Private Sub Add11_Click() Worksheets("Card").Range("I4:P4").Value = UserForm1.Tb56.Value Worksheets("Card").Range("B6:W6").Value = UserForm1.Tb53.Value Worksheets("Card").Range("E10:I10").Value = UserForm1.Tb52.Value Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = UserForm1.Tb54.Value Worksheets("Card").Range("M15:O15").Value = UserForm1.Tb55.Value Worksheets("Card").Range("J17:N17").Value = UserForm1.TB57.Value Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = UserForm1.Tb58.Value End Sub |
Printing Next Row
Hi Gentlemen
You are brilliant as the hours passed I thought it wasn't going to be possible what we needed. Last year I printed nearly 1200 of these cards each one individually, just over 50 a week for 22 weeks during our season - two years ago I wrote them all by hand and then asked our Chairman and Secretary to sign them all - they are Prize Cards for our Gardening Society. Excel works wonders -- just one question Bernie is it possible to stop the routine running at say row 5 so that I can check that eveything is OK before running off the full compliment of Cards. -- Many Thanks Sue "Bernie Deitrick" wrote: Sue, The typical way to do this is to increment through your data base, updating the cells on the printout sheet each time, then printing. Based on your sample code, I think the macro below should work for you. It wasn't clear from your code what the values of UserForm1.Cmb11.Value and UserForm1.Tb51.Value should be, so you'll need to adjust those lines. I asumed that you want all the data from sheet "Result" printed out. HTH, Bernie MS Excel MVP Sub PrintOutForSue() Dim X As Integer Dim StartRowNum As Integer Dim EndRowNum As Integer StartRowNum = 2 EndRowNum = Sheets("Result").Range("E65536").End(xlUp).Row For X = StartRowNum To EndRowNum Worksheets("Card").Range("I4:P4").Value = Sheets("Result").Range("E" & X) Worksheets("Card").Range("B6:W6").Value = Sheets("Result").Range("B" & X) Worksheets("Card").Range("E10:I10").Value = Sheets("Result").Range("A" & X) Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = Sheets("Result").Range("C" & X) Worksheets("Card").Range("M15:O15").Value = Sheets("Result").Range("D" & X) Worksheets("Card").Range("J17:N17").Value = Sheets("Result").Range("F" & X) Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = Sheets("Result").Range("G" & X) Worksheets("Card").PrintOut Next X End Sub "Many thanks Sue" <Many thanks wrote in message ... Good Morning First time post so if I get it all wrong please allow a little leeway. We have a UserForm that reads a Result Sheet on filling the Text Boxes and clicking a CommandButton it the enters the info on a sheet named Card - we then print the resulting card at the moment we have to do each one individually by selecting a number from 1-100 from a drop down Combo Box is it possible after selecting 1 from the drop down it will then continue to find the next row down and print all the rows on to an individual card. The snippets of code below are I suspect very simple to all you clever people out there but this is we do it at the moment and its very time consuming. Private Sub Add1_Click() Dim varAnswer As String varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?", vbYesNo, "Print Warning") If varAnswer = vbNo Then Exit Sub End If Dim X As Integer X = InputBox("Number Of Copies Required ? Enter In Box Below", "Print") If X 0 Then Sheets("Card").Select ActiveWindow.SelectedSheets.PrintOut Copies:=X, Collate:=True End If End Sub Private Sub Cmb10_Click() Dim startrownum As Integer Dim endrownum As Integer startrownum = 2 endrownum = 102 UserForm1.Tb52.Value = (Sheets("Result").Range("A" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb53.Value = (Sheets("Result").Range("B" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb54.Value = (Sheets("Result").Range("C" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb55.Value = (Sheets("Result").Range("D" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb56.Value = (Sheets("Result").Range("E" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.TB57.Value = (Sheets("Result").Range("F" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb58.Value = (Sheets("Result").Range("G" & Trim(Str(Cmb10.ListIndex + startrownum)))) End Sub Private Sub Add11_Click() Worksheets("Card").Range("I4:P4").Value = UserForm1.Tb56.Value Worksheets("Card").Range("B6:W6").Value = UserForm1.Tb53.Value Worksheets("Card").Range("E10:I10").Value = UserForm1.Tb52.Value Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = UserForm1.Tb54.Value Worksheets("Card").Range("M15:O15").Value = UserForm1.Tb55.Value Worksheets("Card").Range("J17:N17").Value = UserForm1.TB57.Value Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = UserForm1.Tb58.Value End Sub |
Printing Next Row
Sue,
Right after this line: For X = StartRowNum To EndRowNum Add this line If X = 5 Then If MsgBox("Continue, Sue?", vbYesNo) = vbNo Then Exit Sub and if you click yes it will continue, no will stop. HTH, Bernie MS Excel MVP "Sue" wrote in message ... Hi Gentlemen You are brilliant as the hours passed I thought it wasn't going to be possible what we needed. Last year I printed nearly 1200 of these cards each one individually, just over 50 a week for 22 weeks during our season - two years ago I wrote them all by hand and then asked our Chairman and Secretary to sign them all - they are Prize Cards for our Gardening Society. Excel works wonders -- just one question Bernie is it possible to stop the routine running at say row 5 so that I can check that eveything is OK before running off the full compliment of Cards. -- Many Thanks Sue "Bernie Deitrick" wrote: Sue, The typical way to do this is to increment through your data base, updating the cells on the printout sheet each time, then printing. Based on your sample code, I think the macro below should work for you. It wasn't clear from your code what the values of UserForm1.Cmb11.Value and UserForm1.Tb51.Value should be, so you'll need to adjust those lines. I asumed that you want all the data from sheet "Result" printed out. HTH, Bernie MS Excel MVP Sub PrintOutForSue() Dim X As Integer Dim StartRowNum As Integer Dim EndRowNum As Integer StartRowNum = 2 EndRowNum = Sheets("Result").Range("E65536").End(xlUp).Row For X = StartRowNum To EndRowNum Worksheets("Card").Range("I4:P4").Value = Sheets("Result").Range("E" & X) Worksheets("Card").Range("B6:W6").Value = Sheets("Result").Range("B" & X) Worksheets("Card").Range("E10:I10").Value = Sheets("Result").Range("A" & X) Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = Sheets("Result").Range("C" & X) Worksheets("Card").Range("M15:O15").Value = Sheets("Result").Range("D" & X) Worksheets("Card").Range("J17:N17").Value = Sheets("Result").Range("F" & X) Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = Sheets("Result").Range("G" & X) Worksheets("Card").PrintOut Next X End Sub "Many thanks Sue" <Many thanks wrote in message ... Good Morning First time post so if I get it all wrong please allow a little leeway. We have a UserForm that reads a Result Sheet on filling the Text Boxes and clicking a CommandButton it the enters the info on a sheet named Card - we then print the resulting card at the moment we have to do each one individually by selecting a number from 1-100 from a drop down Combo Box is it possible after selecting 1 from the drop down it will then continue to find the next row down and print all the rows on to an individual card. The snippets of code below are I suspect very simple to all you clever people out there but this is we do it at the moment and its very time consuming. Private Sub Add1_Click() Dim varAnswer As String varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?", vbYesNo, "Print Warning") If varAnswer = vbNo Then Exit Sub End If Dim X As Integer X = InputBox("Number Of Copies Required ? Enter In Box Below", "Print") If X 0 Then Sheets("Card").Select ActiveWindow.SelectedSheets.PrintOut Copies:=X, Collate:=True End If End Sub Private Sub Cmb10_Click() Dim startrownum As Integer Dim endrownum As Integer startrownum = 2 endrownum = 102 UserForm1.Tb52.Value = (Sheets("Result").Range("A" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb53.Value = (Sheets("Result").Range("B" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb54.Value = (Sheets("Result").Range("C" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb55.Value = (Sheets("Result").Range("D" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb56.Value = (Sheets("Result").Range("E" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.TB57.Value = (Sheets("Result").Range("F" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb58.Value = (Sheets("Result").Range("G" & Trim(Str(Cmb10.ListIndex + startrownum)))) End Sub Private Sub Add11_Click() Worksheets("Card").Range("I4:P4").Value = UserForm1.Tb56.Value Worksheets("Card").Range("B6:W6").Value = UserForm1.Tb53.Value Worksheets("Card").Range("E10:I10").Value = UserForm1.Tb52.Value Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = UserForm1.Tb54.Value Worksheets("Card").Range("M15:O15").Value = UserForm1.Tb55.Value Worksheets("Card").Range("J17:N17").Value = UserForm1.TB57.Value Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = UserForm1.Tb58.Value End Sub |
Printing Next Row
Hi Bernie
Spot on -- even my Chairman of the Society is impressed and that takes some doing. -- Many Thanks Sue "Bernie Deitrick" wrote: Sue, Right after this line: For X = StartRowNum To EndRowNum Add this line If X = 5 Then If MsgBox("Continue, Sue?", vbYesNo) = vbNo Then Exit Sub and if you click yes it will continue, no will stop. HTH, Bernie MS Excel MVP "Sue" wrote in message ... Hi Gentlemen You are brilliant as the hours passed I thought it wasn't going to be possible what we needed. Last year I printed nearly 1200 of these cards each one individually, just over 50 a week for 22 weeks during our season - two years ago I wrote them all by hand and then asked our Chairman and Secretary to sign them all - they are Prize Cards for our Gardening Society. Excel works wonders -- just one question Bernie is it possible to stop the routine running at say row 5 so that I can check that eveything is OK before running off the full compliment of Cards. -- Many Thanks Sue "Bernie Deitrick" wrote: Sue, The typical way to do this is to increment through your data base, updating the cells on the printout sheet each time, then printing. Based on your sample code, I think the macro below should work for you. It wasn't clear from your code what the values of UserForm1.Cmb11.Value and UserForm1.Tb51.Value should be, so you'll need to adjust those lines. I asumed that you want all the data from sheet "Result" printed out. HTH, Bernie MS Excel MVP Sub PrintOutForSue() Dim X As Integer Dim StartRowNum As Integer Dim EndRowNum As Integer StartRowNum = 2 EndRowNum = Sheets("Result").Range("E65536").End(xlUp).Row For X = StartRowNum To EndRowNum Worksheets("Card").Range("I4:P4").Value = Sheets("Result").Range("E" & X) Worksheets("Card").Range("B6:W6").Value = Sheets("Result").Range("B" & X) Worksheets("Card").Range("E10:I10").Value = Sheets("Result").Range("A" & X) Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = Sheets("Result").Range("C" & X) Worksheets("Card").Range("M15:O15").Value = Sheets("Result").Range("D" & X) Worksheets("Card").Range("J17:N17").Value = Sheets("Result").Range("F" & X) Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = Sheets("Result").Range("G" & X) Worksheets("Card").PrintOut Next X End Sub "Many thanks Sue" <Many thanks wrote in message ... Good Morning First time post so if I get it all wrong please allow a little leeway. We have a UserForm that reads a Result Sheet on filling the Text Boxes and clicking a CommandButton it the enters the info on a sheet named Card - we then print the resulting card at the moment we have to do each one individually by selecting a number from 1-100 from a drop down Combo Box is it possible after selecting 1 from the drop down it will then continue to find the next row down and print all the rows on to an individual card. The snippets of code below are I suspect very simple to all you clever people out there but this is we do it at the moment and its very time consuming. Private Sub Add1_Click() Dim varAnswer As String varAnswer = MsgBox("Have you added the Dialog Box Entries to the Sheet?", vbYesNo, "Print Warning") If varAnswer = vbNo Then Exit Sub End If Dim X As Integer X = InputBox("Number Of Copies Required ? Enter In Box Below", "Print") If X 0 Then Sheets("Card").Select ActiveWindow.SelectedSheets.PrintOut Copies:=X, Collate:=True End If End Sub Private Sub Cmb10_Click() Dim startrownum As Integer Dim endrownum As Integer startrownum = 2 endrownum = 102 UserForm1.Tb52.Value = (Sheets("Result").Range("A" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb53.Value = (Sheets("Result").Range("B" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb54.Value = (Sheets("Result").Range("C" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb55.Value = (Sheets("Result").Range("D" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb56.Value = (Sheets("Result").Range("E" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.TB57.Value = (Sheets("Result").Range("F" & Trim(Str(Cmb10.ListIndex + startrownum)))) UserForm1.Tb58.Value = (Sheets("Result").Range("G" & Trim(Str(Cmb10.ListIndex + startrownum)))) End Sub Private Sub Add11_Click() Worksheets("Card").Range("I4:P4").Value = UserForm1.Tb56.Value Worksheets("Card").Range("B6:W6").Value = UserForm1.Tb53.Value Worksheets("Card").Range("E10:I10").Value = UserForm1.Tb52.Value Worksheets("Card").Range("H13:P13").Value = UserForm1.Cmb11.Value Worksheets("Card").Range("I15").Value = UserForm1.Tb54.Value Worksheets("Card").Range("M15:O15").Value = UserForm1.Tb55.Value Worksheets("Card").Range("J17:N17").Value = UserForm1.TB57.Value Worksheets("Card").Range("Q11:W11").Value = UserForm1.Tb51.Value Worksheets("Card").Range("J10:M10").Value = UserForm1.Tb58.Value End Sub |
| All times are GMT +1. The time now is 06:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com