#1
|
|||
|
|||
Generating coversheets using inputboxes
Hi All
I'm trying to write a VBA macro that will generate coversheets for each day of trading reciepts for my stores, for the month selected. I want each coversheet to display the store number, name, and then the date. Ideally, I want the user to just select a month and year, and the macro will go ahead and generate 28-31 pages with each days date. So far, I have two seperate inputboxes for the store number, and store name, and these are stored as varables. I'm having trouble getting to the next step, which is having the user specify a month and year, and having all the collected data inputted onto the document. It's been a few years since using VBA, and I'm having trouble moving forward. Any help would be greatly appreciated. Please see attached for what I'm aiming for. |
#2
|
||||
|
||||
In order to suggest a process we would need to know what you mean by
and having all the collected data inputted onto the document. What collected data would that be?
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Assuming all collected data means 1) Store number, 2) Store Name and 3) Month and year then I don't see why you can't just continue your process and get Month and Year from and inputbox as well.
I would create my template using three content controls. One for store number, one for store name and one for date. I would follows those three CCs with a section breack next page then select the three plus section break and save it as a BuildingBlock e.g., CoverSheet. Then using: Code:
Sub DatedCoverSheets() 'A basic Word macro coded by Greg Maxey Dim strName As String, strNum As String, strMY As String Dim arrParts() As String Dim oDate As Date Dim lngIndex As Long Dim oRng As Range strNum = InputBox("What is your store number?") strName = InputBox("What is your store name?") strMY = InputBox("Enter the month and year e.g., April 2019") arrParts = Split(strMY, " ") oDate = "1 " & arrParts(0) & " " & arrParts(1) For lngIndex = 1 To fcnDaysInMonth(oDate) With ActiveDocument.Sections(lngIndex).Range .ContentControls(1).Range.Text = "ABC" .ContentControls(2).Range.Text = "123" 'Date the page .ContentControls(3).Range.Text = Format(DateAdd("d", lngIndex - 1, oDate), "MMMM dd, yyyy") End With Set oRng = ActiveDocument.Range oRng.Collapse wdCollapseEnd ActiveDocument.AttachedTemplate.BuildingBlockEntries("CoverSheet").Insert oRng, True Next 'As a result of the section break in the template and in the building block, there will be two hanging sections at 'the end of the finished document. Delete them. Set oRng = ActiveDocument.Sections(lngIndex - 1).Range.Paragraphs.Last.Range oRng.End = ActiveDocument.Range.End oRng.Delete lbl_Exit: Exit Sub End Sub Function fcnDaysInMonth(oDateSample As Date) fcnDaysInMonth = Day(DateSerial(Year(oDateSample), Month(oDateSample) + 1, 1) - 1) End Function Edit: The attached template is a variation of the code above that employs a userform. |
#4
|
|||
|
|||
Thanks Gmaxey, this is brilliant.
One issue I'm having is that instead of writing the store number and name as inputted in the prompt, it is instead writing "123 ABC". Is there a way to fix this? How would I go about changing the date to a UK format also, if possible? Thank you very much for your help |
#5
|
||||
|
||||
It writes ABC and 123 because Greg hard coded that into the template code rather than the strName and strNum from the userform, no doubt while testing if his code worked to populate the controls in the loop (the incrementing date being the important bit of the code), and then forgot to replace the fixed texts with the string variables. It's easily done.
Code:
.ContentControls(1).Range.Text = "ABC" .ContentControls(2).Range.Text = "123" See attached
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#6
|
|||
|
|||
Quote:
I forgot to add that between the store name and date I need to have a day of the week for staff (i.e. "Tuesday") I would assume that this would be inserted as an aforementioned input - ".ContentControlsi.ContentControls(1).Range.Te xt = sDayName", but it doesn't seem to work. Is there something I'm missing here? Thanks again for your help |
#7
|
||||
|
||||
Apart from the fact that if you add another content control between 2 and 3 it will not be 1 but 3 and the former 3 will be 4, the method would be correct. Yo would however have to update the building block- see attached.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#8
|
|||
|
|||
Thanks mate. As opposed to a fixed day (at the moment it's just what ever day of the week I input), could I have it so instead of asking for a day of the week, it pulls it from the date instead? Automatically having the day of the week inputted.
Thanks |
#9
|
||||
|
||||
See attached
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com Last edited by gmayor; 06-01-2019 at 11:25 PM. |
#10
|
|||
|
|||
This is perfect. Thank you both for your help. Time to brush up further on my VBA!
|
Tags |
inputbox |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
generating a calendar | jimmy2016 | Mail Merge | 2 | 09-28-2016 11:09 PM |
Generating a Letter | Shanna86 | Word | 9 | 09-29-2015 09:11 PM |
generating a number in Word | John P | Word | 1 | 05-31-2012 10:29 PM |
Generating Buy/Sell signals accordingly... | babapusy | Excel | 0 | 04-14-2012 10:11 AM |
MS Word 2003 – Generating a ToC | krazykasper | Word | 1 | 04-29-2009 02:44 PM |