Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-05-2019, 01:32 AM
alecambo alecambo is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2019
Novice
Generating coversheets using inputboxes
 
Join Date: Apr 2019
Posts: 5
alecambo is on a distinguished road
Default 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.
Attached Images
File Type: png Snip.PNG (14.6 KB, 27 views)
Reply With Quote
  #2  
Old 04-05-2019, 04:18 AM
gmayor's Avatar
gmayor gmayor is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 04-05-2019, 06:14 AM
gmaxey gmaxey is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,427
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

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
... you get you 28, 29, 30 or 31 pages as required.


Edit: The attached template is a variation of the code above that employs a userform.
Attached Files
File Type: dotm Create Date Cover Sheets.dotm (46.8 KB, 13 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #4  
Old 05-26-2019, 01:46 AM
alecambo alecambo is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2019
Novice
Generating coversheets using inputboxes
 
Join Date: Apr 2019
Posts: 5
alecambo is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 05-26-2019, 02:50 AM
gmayor's Avatar
gmayor gmayor is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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"
The date format is set in the date content control properties, but once changed the autotext entry also has to be updated as that contains a copy of the template body with the three content controls.

See attached
Attached Files
File Type: dotm Create Date Cover Sheets.dotm (50.6 KB, 7 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 05-31-2019, 06:55 AM
alecambo alecambo is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2019
Novice
Generating coversheets using inputboxes
 
Join Date: Apr 2019
Posts: 5
alecambo is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
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"
The date format is set in the date content control properties, but once changed the autotext entry also has to be updated as that contains a copy of the template body with the three content controls.

See attached
Thanks gmayor.
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
Reply With Quote
  #7  
Old 05-31-2019, 08:19 PM
gmayor's Avatar
gmayor gmayor is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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.
Attached Files
File Type: dotm Create Date Cover Sheets.dotm (51.5 KB, 7 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #8  
Old 06-01-2019, 04:23 PM
alecambo alecambo is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2019
Novice
Generating coversheets using inputboxes
 
Join Date: Apr 2019
Posts: 5
alecambo is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 06-01-2019, 08:08 PM
gmayor's Avatar
gmayor gmayor is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

See attached
Attached Files
File Type: dotm Create Date Cover Sheets.dotm (54.5 KB, 9 views)
__________________
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.
Reply With Quote
  #10  
Old 06-02-2019, 12:48 AM
alecambo alecambo is offline Generating coversheets using inputboxes Windows 10 Generating coversheets using inputboxes Office 2019
Novice
Generating coversheets using inputboxes
 
Join Date: Apr 2019
Posts: 5
alecambo is on a distinguished road
Default

This is perfect. Thank you both for your help. Time to brush up further on my VBA!
Reply With Quote
Reply

Tags
inputbox

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Generating coversheets using inputboxes generating a calendar jimmy2016 Mail Merge 2 09-28-2016 11:09 PM
Generating coversheets using inputboxes Generating a Letter Shanna86 Word 9 09-29-2015 09:11 PM
Generating coversheets using inputboxes 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:02 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft